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:
Makecert.exe -n "CN=Always Encrypted cert" -pe -sr LocalMachine -r -eku 220.127.116.11.18.104.22.168.2,22.214.171.124.4.1.3126.96.36.199 -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.
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
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.
– 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:
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):
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:
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.