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.
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