Skip to main content

SQL Server Always Encrypted - At a high level how does it works?

One of the excellent feature introduced in SQL Server 2016 is "Always Encrypted". This gives an extra layer of protection as no one (including the production DBA's) will be able to access the actual data without having the appropriate key.

A high-level overview of how SQL Server 2016 Always Encrypted work:

1. Always Encrypted is a client-side encryption technology in which a SQL Server client driver (In our case, it would be ADO.NET) plays the key role. The driver encrypts the data which application sends as plaintext, and it then sends encrypted data to SQL Server. So, the data is encrypted on the fly as well as at rest.

2. Now when the application retrieves the encrypted data from the database the DRIVER transparently decrypts returning plaintext to the client app. Consequently, SQL Server never sees a sensitive information in plaintext. The keys, in fact, are managed entirely on the client side & the server doesn't have access to the keys either.

3. The key can be stored either in a Windows Certificate (or) Azure key vault,

4. Despite the fact that SQL Server never has access to plaintext and sensitive information, or the corresponding encryption key. SQL Server can query the data and can perform certain computations on encrypted data, namely equality comparison, equality joins, exact match searches or group by operations. - Conditions apply :)

5. There are 2 types of "Encryption Type": Randomized encryption and Deterministic encryption

a. Randomized encryption: This algorithm produces a different ciphertext value for a given plaintext value. Therefore, randomized encryption is more secure but it prevents any operations on encrypted data. Only we can select the column data and display that's it.

b. Deterministic encryption always produces the same ciphertext value for the given plaintext value. Therefore, it enables equality comparison on encrypted data in operations such as exact match searches, equality joins or group by operations.

c. Security Concern?

There is a slight security concern related to deterministic encryption because each plaintext value always maps to the same cyphertext value. An attacker can potentially examine the cyphertext patterns and guess the underlying plaintext values - especially if the dataset is small. For example, columns contain gender information about male and female. So that's something that we need to keep in mind and be careful while choosing the encryption type.

6. How exactly does the encryption happen?

a. It would download the data of the particular table, encrypts it and then uploads it back. In that process, the schema of the table would change.

b. Encrypted with clause has 3 parameters. The name of the column encryption key protecting data and the column, the type of encryption (deterministic / Randomized) and the name of the encryption algorithm.

c. Always encrypted currently supports just one encryption algorithm, which is AES256.

7. Sample table structure post enabling "Always Encrypted" feature:



8. Web.config changes:

This is the key part because actually to enable always encrypted from the .NET application we had to add the below value within our connection string.

column encryption setting=enabled;

9. Now application would be able to decrypt information retrieved from the database because it has access to that certificate, that we would have created earlier. So it would be able to decrypt a column encryption key and subsequently, decipher the sensitive information and show it in the clear text as expected.

10. SSMS Client:

From within SSMS client if one need to see the decrypted value then the following settings needs to be enabled.

Add Column Encryption Setting = Enabled in the Additional Connection Parameters in the SSMS Connect to Server window.


Comments

Popular posts from this blog

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul