Serenity with Always Encrypted Sql Server

[teafmartins]

Hi,

Anyone using Always Encrypted Sql server with Serenity?
Any trouble retrieving results in endpoints? I'm having timeout retrieving encrypted columns.

Thanks

[edwardch]

Hey @teafmartins,

I would be happy experiment for you if you could share a simplified explanation of your SQL Server set up?

I'd like to encrypt some columns too, so this may benefit both of us.

Cheers,
Edward

[teafmartins]

Hi @edwardch

I used this guideline:
https://www.toptal.com/sql-server/sql-server-2016-always-encrypted
I just created a key in SSMS and after that the master key column.

after that I just created the encrypted the column needed:

When running Serenity everything works find if I don't retrieve encrypted columns in endpoint. If I include this columns I get time out error.

Can you try your side if it works?

[edwardch]

Looks interesting, thanks. Let me give it a shot next week. Hopefully get something promising.

Out of interest, do you host production on self managed servers, or are you using managed like Azure SQL or something?

[teafmartins]

Always Encrypt works very well reading data from DB but not inserting or updating data.
Inserted and updated must be done using parameterized query.

How can I do it using serenity? any easy way modifying endpoint?

[Julian1993ARG]

Hi, did you find any way to do it?

[teafmartins]

Hi Julian,

Yes, as I said above, you have to change the "MySaveHandler" class in repository and manually perform the update with parameters.
For example, something like:

            string ConnectionString = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (var cmd = new SqlCommand("UPDATE [dbo].[Table] SET FieldA=@FieldA WHERE Id=" + Row.Id, connection))
                {
                    connection.Open();
                    cmd.CommandTimeout = 30;
                    var paramFieldA = new SqlParameter("@FieldA", SqlDbType.NVarChar, 50);
                    paramFieldA.Value = Row.FieldA;
                    cmd.Parameters.Add(paramFieldA);
                    cmd.ExecuteNonQuery();
                }
            }

I hope this helps.