How to perform insert, update and delete operations, on encrypted column in a table in SQL server 2017

Question

My name is Hari varma, I am working as a SQL DBA.

Currently I am facing issues with Always Encryption in SQL Server 2017 Development Edition in our testing server.

I don't have any experience in Always Encryption and TDE. One of my client asked me to do encryption on database.

I have done some testing on Always Encryption and I was able to encrypt and decrypt the column data by using doing the following:

On the SQL Server instance
-->Options-->Additional connection Parameter-->Column Encryption Setting = Enabled

After I enabled the column encryption I am able to view the encrypted data in the table.

However I am not able to insert, update, and delete data in this encrypted column. Also I need to be able to set permissions on users who are allowed / not allowed to view the data on this encrypted column.

Which permissions I need to give on a particular user and provide any prerequisites for Always Encryption and TDE.

Answer 1

First of all it's important to understand that your SQL Server instance does not know the keys used for encrypting and decrypting data when using Always Encrypted. The keys are kept externally, usually in a key store such as Windows Certificate Store or Azure Key Vault. This means that SQL Server itself cannot encrypt or decrypt the data - this instead has to be done by a client application that has access to the keys.

However I am not able to insert, update, and delete data in this encrypted column.

I assume you are attempting to insert, update, and delete data directly through SSMS or something similar. This is only possible to a limited extent. This is because SSMS (which is your client application in this case) needs to be able to encrypt the data before sending it to your SQL Server.

Read more about inserting data into columns that are encrypted via Always Encrypted in SQL Server here (using SSMS).

A brief summary of how to insert encrypted data via SSMS:

• You need to enable the column encryption setting in your connection string. This is done under Options>>Additional Connection Parameters when you connect to your SQL Server instance in SSMS. Add this text in there: Column Encryption Setting=Enabled
• Once you've connected to your database and opened a query window, you need to enable parameterization for always encrypted. This is done in SSMS under Query>>Query Options>>Execution>>Advanced>>Enable Parameterization for Always Encrypted.

When you've completed the two steps above you'll be able to insert data into an encrypted column like this:

DECLARE @ParameterToBeEncrypted NVARCHAR(100) = 'Decrypt me';
INSERT INTO dbo.MyTable(MyEncryptedColumn) VALUES (@ParameterToBeEncrypted);

This works because your client application (SSMS) is able to encrypt the value that you're initializing @ParameterToBeEncrypted with before sending it to SQL Server. This only works if your current user has access to the column encryption key. SQL Server will never see the plain/non-encrypted value ('Decrypt me') - it will only see the encrypted value that should be inserted into the encrypted column.

Which permissions I need to give on a particular user and provide any prerequisites for Always Encryption

It's a combination of permissions in SQL Server and being able to access the keys used for encrypting and decrypting the data. The necessary database permissions are VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION. You can read more about the necessary permissions here.

If you want to encrypt a set of existing data in your database, then your best bet is to write your own client application (e.g. in C# or similar) or create a SSIS package (which would serve as a client application). The client application or SSIS package should read the data from the database, encrypt the data outside of the database, and then send it back to the database as encrypted data.