SQL 2016: Testing Always Encrypted – Part 2

Now let’s see what certificate has been created by the Encrypt Columns wizard:
67 68
6970

This certificate was generated by the SQL Server and thus it’s not trusted by default – you can create the same certificate with makecert utility by youself:

https://msdn.microsoft.com/en-us/library/mt146393.aspx

Makecert.exe -n "CN=Always Encrypted cert" 
    -pe -sr LocalMachine -r 
    -eku 1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 
    -ss my -sky exchange 
    -sp "Microsoft Enhanced Cryptographic Provider v1.0" 
    -sy 1 -len 2048 -a sha256

…or get a certificate from some agency, for example VerySign.

I will need this certificate for importing to the other user’s certificate store later on so I will export it now:
72  747577

The next step is to test access to the encrypted column. As you saw above using Select statement for the Frequency column results in the rows being outputted as the   encrypted values, even for the user who created the keys and who has the certificate in his Current User certificate store. This is because I did not “say” my client application (MS SQL Management Studio) that it must be able to encrypt/decrypt data in my database. The following parameter will let the Management Studio read and update encrypted data:

Column Encryption Setting=enabled

79-NewConnectionString

The Frequency column is now displayed in the plaintext:
80

What would happen if some other user will try to quiery a table with the encrypted column? To answer this question I log on to my SQL Server computer as another user – Tester2 (a member of the Data_Reader built-in role). This user does not have the certificate which was used for CMK creation in his own Current User certificate store.
83 85– SQL Server tells us exactly the same: it failed to find a certificate (=CMK) and thus could not decrypt the CEK for the Frequency column.

Please pay attention to the following fact: once SQL Server failed to find the CMK it would not returne any rows – neither rows without Frequency column nor data from the Frequency column in encrypted form.

For the user Tester2 to succeed in reading encrypting data, he should import the certificate which was exported from the ‘firsov‘s Current User certificate store and issue the Select * from CPUDetails statement once again:
87 88 89 90 91 92 93 94

The result:
101

The next step is inserting a new row into the table with the encrypted column (I will incert a new row as firsov and then read the updated table as Tester2 as user Tester has only Data-Reader right on the DB):
107108-1

The new row has been successfully added:109

The last step is to try to update the data in the encrypted column.122

After changing ‘3100’ to ‘3600’ I got the error above: it states that SQL Server would allow data modifications for columns encrypted with DETERMINISTIC encryption type but not for RANDOMIZED. This behaviour is by design for SSMS when using Edit Top 200 rows command because when the MS SQL Management Studio opens a table for updating it uses “where column-value = some parameter” command for the EVERY column in a table, including the encrypted ones, but any equality searches are not allowed for Randomized encryption as stated in the documentation:

114-Q So I can’t test updating the rows in the encrypted table – for this I would  have to write my own application.

Summary:

Always Encrypted is working as expected. The only exception to this is updating the data in the columns encrypted with the Randomized encryption type, or, strictly speaking, it may be not the “exception” but just a peculiarity of MS SQL Management Studio.

 

%d bloggers like this: