Can an admin see encrypted data with SQL Server 2016 always encrypted feature?

Question

This looks a very nice feature, but what I understood its that only the app can see the unencrypted text of the columns, right?

Is it possible that the admin with a SQL query can see the unencrypted data? or does it have to be always from the app?

If yes, can you post a small query to show this

Answer 1

The answer is NO you cannot see encrypted data even if you are the data admin. The admin should not have access to Column Master Key (certificate). You can access it from a client machine that has certificate installed using SSMS or .Net application. Please check the MSDN blog

The new security layer addresses that vulnerability by keeping the data encrypted even during transactions and computations, and by only giving the client keys to decrypt it. That means that if anyone else, including a database or system administrator, tries to access that client’s database, the credit card information or other sensitive data would just look like gibberish.

Answer 2

If you are an admin/DBA you can access plaintext data, if you have access to the column master key. To select and decrypt data, you can use any version of SSMS that supports Always Encrypted. Starting with SSMS 17.0, you can also insert, update, and filter by encrypted columns. For more details, please see:

• https://msdn.microsoft.com/en-us/library/mt757096.aspx#Anchor_1
• https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/