principlesqlMinor
What are the advantages of Always Encrypted 2016 vs Encrypted Columns?
Viewed 0 times
thewhatcolumnsareadvantagesalwaysencrypted2016
Problem
I was reading about SQL Encryption methods, I have a good understanding of TDE, then I read about Encrypted Columns and then I read about Always Encrypted. The description of Always Encrypted is very lengthy and detailed, but the text with Encrypted Columns is less in depth but seems to me like it does the same thing. I want to understand the difference between the 2 and if there is any advantage with the new version?
Solution
The major difference I know is
Normal Column Encryption
The normal Column encryption which was introduced from SQL Server 2005 uses function Encryptbycert to encrypt the columns. It is not as secured as compared to Always Encrypted in terms that with this method data is submitted as clear text in SQL Server and this data can be seen from traces. A DBA, who has admin access to SQL Server, can see the data so this is actually not fully secured.
Always Encrypted
With always encrypted the encryption is done at clients app by API, like ADO.net,ODBC. Drivers are installed at clients end to do this encryption. This will not allow SQL Server to see text data hence not revealing it to DBA's and one who have admin access on SQL Server. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine.
Quoting from Docs.microsoft
Always Encrypted makes encryption transparent to applications. An
Always Encrypted-enabled driver installed on the client computer
achieves this by automatically encrypting and decrypting sensitive
data in the client application. The driver encrypts the data in
sensitive columns before passing the data to the Database Engine, and
automatically rewrites queries so that the semantics to the
application are preserved. Similarly, the driver transparently
decrypts data, stored in encrypted database columns, contained in
query results.
I hope I am able to give you some insight into both technologies.
Normal Column Encryption
The normal Column encryption which was introduced from SQL Server 2005 uses function Encryptbycert to encrypt the columns. It is not as secured as compared to Always Encrypted in terms that with this method data is submitted as clear text in SQL Server and this data can be seen from traces. A DBA, who has admin access to SQL Server, can see the data so this is actually not fully secured.
Always Encrypted
With always encrypted the encryption is done at clients app by API, like ADO.net,ODBC. Drivers are installed at clients end to do this encryption. This will not allow SQL Server to see text data hence not revealing it to DBA's and one who have admin access on SQL Server. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine.
Quoting from Docs.microsoft
Always Encrypted makes encryption transparent to applications. An
Always Encrypted-enabled driver installed on the client computer
achieves this by automatically encrypting and decrypting sensitive
data in the client application. The driver encrypts the data in
sensitive columns before passing the data to the Database Engine, and
automatically rewrites queries so that the semantics to the
application are preserved. Similarly, the driver transparently
decrypts data, stored in encrypted database columns, contained in
query results.
I hope I am able to give you some insight into both technologies.
Context
StackExchange Database Administrators Q#176277, answer score: 7
Revisions (0)
No revisions yet.