3

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?

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.