Monday, January 09, 2017

SQL Server Always Encrpted - 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 production as no one (including the production DBA's) will be able to access the actual data without having the appropriate key.

An 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 drivers (In our case, it would be ADO.NET) plays the key role. The driver encrypts the data which application sends in its queries to the database, and it then sends encrypted data to SQL Server.

2. Now when the application retrieves the encrypted data from the database the DRIVER transparently decryptes 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 cyphertext 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 cyphertext 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 ex, 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 we will see that actually to enable always encrypted add the below value in your 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 produce in the wizard. It's basically running on the same machine where the certificate was generated and stored in windows certificate store. And then it is able to decrypt a column encryption key and subsequently, decipher the sensitive information and show it in the clear text as expected.

No comments: