3

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.

CurseStacker
  • 1,079
  • 8
  • 19
HARI VARMA
  • 41
  • 1
  • 6
  • I faced exactly the same problem recently. After googling quite a while I managed to perform Insterst / Updates: I created a procedure in the DB which performs the task at hand. Then I created a C# Application / SSIS Component which called the procedure with the required parameters (DML on always encrypted columns seems not to be possible in SSMS currently). Feel free to contact me for further details. Anyways, "which permissions" should be obsolete: always encrypted works with Key pairs / certificates. If a user is lacking the required KEy, he won't dee any decrypted data. – Tyron78 Sep 05 '18 at 08:31
  • Can you please provide me step by step process. – HARI VARMA Sep 05 '18 at 09:10
  • Sure, I will try my best. But before I start: what have you already tried / performed? Did you already generate (and store) a certificate (with keys)? As you mentioned that you already see encrypted data, I suppose this has already been done? Can YOU also see the decrypted data? – Tyron78 Sep 05 '18 at 10:48
  • I am just create one test database and create one table in Test database and insert some values. Table Structure contains PersonID,FirastName,LastName,Address,City. I did encryption on City column using GUI process Rights Click on table Encryption Column. column_encryption_key_name = 'CEK_Auto1' I have enbled Column Encryption Setting=Enabled in SSMS, after enable i am able to view the encrypted data in a normal text. When i try to insert with below query i am getting error – HARI VARMA Sep 05 '18 at 11:27
  • error like Msg 206, Level 16, State 2, Line 9 Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Encryption') collation_name = 'SQL_Latin1_General_CP1_CI_AS' INSERT INTO [dbo].[Persons](PersonID, FirstName, LastName, Address, City) VALUES ('4', 'ABC', 'DEF', 'AP','AP'); – HARI VARMA Sep 05 '18 at 11:27
  • Yes, this is the typical error. Then next step would be to create a procedure in the destination DB, like `CREATE PROCEDURE dbo.Insert_TestTable(@Idx_Det int, @Bez_Det nvarchar(10), @Val_Rnd decimal(19,4), @Dat_Rnd datetime)` (of course modified to your table names ans data types), which simply performs an `INSERT`. After this (at least for testing purposes) you should create a C# Application (for testing I found the C# Console Application most handy), which opens a `SqlConnection`. Note: The connection string will require `; Column Encryption Setting = Enabled`. – Tyron78 Sep 05 '18 at 12:11
  • So, did you succeed in creating a C# App with connection to the DB? – Tyron78 Sep 06 '18 at 06:28
  • Not yet, i have modified table names and datatypes but i am not able to insert data in encryption column. my table structure is [PersonID] [int],[FirstName] [varchar](255),[LastName] [varchar](255),[Address] [varchar](255),[City] [varchar](255) these 4 columns i have created in my test table. – HARI VARMA Sep 06 '18 at 06:50
  • Again: You will NOT be able to insert via SSMS. This is why I mentioned the creation of a C# app / SSIS component / ... ;-) For Testpurposes, a C# Console App should do fine! – Tyron78 Sep 06 '18 at 07:13
  • However, the idea would be that the C# opens a connection as mentioned, prepares a SQL Command of type StoredProcedure and calls the procedure you created in the DB with the values you want to insert. For further details Feel free to contact me. – Tyron78 Sep 06 '18 at 08:59
  • https://stackoverflow.com/questions/44657557/how-to-insert-values-into-a-table-in-sql-2016-whose-columns-are-always-encrypted?rq=1 – HARI VARMA Sep 08 '18 at 14:12
  • I have install Visual studio Code in my local computer and i have connect to the database and execute stored procedure from SQL server and i have fallowed the above link steps but still i am getting error from Visual Studio Code – HARI VARMA Sep 08 '18 at 14:14
  • Msg 33299, Level 16, State 6, Line 17 Encryption scheme mismatch for columns/variables '@SIN'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Encryption') (or weaker). – HARI VARMA Sep 08 '18 at 14:15
  • I have created same table and same stored procedure – HARI VARMA Sep 08 '18 at 14:16
  • Can you please send me any sample queries to execute from visual studio code to insert data using with stored procedure in encrypted table. – HARI VARMA Sep 11 '18 at 09:08
  • The same way as you would execute any nonquery command in C#. Define a connection (`con.ConnectionString = "Data Source=MyServer;Initial Catalog=MyDB;Integrated Security = True; Column Encryption Setting = Enabled";`), open it and define a new SQLCommand (`SqlCommand cmd = new SqlCommand("dbo.MyStoredProcedure", con)`, `cmd.CommandType = System.Data.CommandType.StoredProcedure;`). Define the parameters (`SqlParameter MyProcParam = new SqlParameter("@MyProcParam", System.Data.SqlDbType.NVarChar, 10);`), assign Values (`MyProcParam.Value = "MyValue";`) and execute (`cmd.ExecuteNonQuery();`). – Tyron78 Sep 13 '18 at 07:01
  • Oh, I forgot: before you execute the command, you have to attach the parameter to it: `cmd.Parameters.Add(MyProcParam);` – Tyron78 Sep 13 '18 at 07:03
  • ANd don't forget to encapsulate it in try-catch blocks in order to handle SQL Errors etc. – Tyron78 Sep 13 '18 at 07:04
  • @Hari_Varma did it work now? – Tyron78 Sep 19 '18 at 11:00

1 Answers1

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.

dybzon
  • 1,236
  • 2
  • 15
  • 21