So in my last post we looked at the basics of setting up “Always Encryption” within SQL 2016. In this post we will look at actually getting data into the SQL table using the .NET framework. If you remember from the last post the stored procedure would not work based on the columns now requiring encryption, this is due to .NET having to be the mechanism for encrypting the data as part of the save.

For me I will be using the same server for everything, but the principle is still the same. If you are coding from a different server or workstation you will need to export the certificate and import it onto the new workstation or server. The certificate can be exported using the MMC snap in for Certificates as normal.

The certificate resides in the container that you added it in to, for me it was the “Certificates – Current User“.

To export it you need to right click and choose “Export“.

You needs to make sure you export the “Private Key“.

Once this is completed you should then have the “PFX” file needed to copy to the other Server or Workstation.

This certificate can then be imported the same way by selecting import within the “MMC“. We also add the certificate to the “Trusted Certification Authorities” node which will get rid of the error we see about the trust opening the certificate from the certificate store.

Now we have the certificate exported and imported, we can now create a console application that is targeted at .NET Framework 4.6.

Now we can start to add code that will allow inserting of data into the encrypted row columns. Firstly we add the connection string.

Notice that in the connection string we need to add a new parameter for allowing the encryption settings.

Next we add the standard SQL code for adding the data. We will create a function for inserting the data and then call that from the main function of the console application.

The main function then simply connects to SQL and calls the insert function.

Now running the code as is, gave me the following error.

“An unhandled exception of type ‘System.Data.SqlClient.SqlException’ occurred in System.Data.dll

Additional information: Operand type clash: char(7) encrypted with (encryption_type = ‘RANDOMIZED’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘DEV_ALWAYS_ENCRYPTED_COLUMN’, column_encryption_key_database_name = ‘DEV_ENCRYPTION’) collation_name = ‘SQL_Latin1_General_CP1_CI_AS’ is incompatible with nvarchar(25) encrypted with (encryption_type = ‘RANDOMIZED’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘DEV_ALWAYS_ENCRYPTED_COLUMN’, column_encryption_key_database_name = ‘DEV_ENCRYPTION’)”

To resolve this issue check your stored procedure code, and make sure that all parameters match the right type that you have in the table. When I ran this I had changed the column data type for the “Password” field to a “CHAR(10)” instead of the original “NVARCHAR(16)” but not updated the stored procedure. Now we can add a “Console.ReadLine” command so we can see the outputted value to the code and then, add new function to all that will output the added values that are encrypted.

Running the code with the insert code outputs the following:

Checking in SQL using standard T-SQL code it shows the encrypted value:

Running the code again this time in retrieval mode display the following:

Notice that running the select statement in SQL only displays the encrypted value. If we modify the connection settings for the SQL Management Studio to include the “Column Encryption Setting = Enabled” we can then see the plain text values.

This allows those that have SQL Management Studio and DBA access to see the encrypted values.

As you can see this allows for an end to end encryption mechanism based on the certificates created, along with specific permissions for the user accounts being used. Now of course not every application supports this yet. More to come.