1

I'm currently studying SQL Server Always Encrypted feature. How can I disable column decryption to "sa" if all they have to do is add Column Encryption Setting=enabled in the login's additional login parameters in SQL Server Management Studio?

I read articles in Google. They only said we can secure data from admins but no example how.

1

The column encryption key (CEK) is used to encrypt the data and is stored in the database. The CEK is secured using  a column master key (CMK).

The CMK is stored outside of SQL Server. Metadata about the key is stored within SQL Server.

If you restrict access to the CMK your database administrators / system administrators (sa) will not have the ability to decrypt.

-3

You can't simply because the key all stored in the catalog, and the catalog is unrestricted to sa.

I didn't write the query, I just googled for sys.column_encryption_key_values (which I knew stored the sym encryption keys), and found someone else who did the work.

SELECT cek.name, cmk.key_path, cekv.encrypted_value, cekv.encryption_algorithm_name
FROM sys.column_encryption_key_values cekv, sys.column_encryption_keys cek, sys.column_master_key_definitions cmk
WHERE cek.column_encryption_key_id = cekv.column_encryption_key_id
   AND cmk.column_master_key_definition_id = cekv.column_master_key_definition_id;

All bets are off with symmetric encryption if someone gets root.

  • so that means, all of my encrypted confidential data can be decrypted by any admin even if they supposedly have no access to view it? – user3204806 Dec 27 '17 at 7:49
  • 1
    "supposedly have no access to view it" "SA" is "System Admin" if you have that user enabled (Mixed Auth) then you're in for a world of pain if that user is compromised. That's how the DB works. – Evan Carroll Dec 27 '17 at 7:51
  • ok thanks...last question..is there a way to block Column Encryption Setting=enabled on non admin user...i tried to create one and adding this to parameter also decrypt the data – user3204806 Dec 27 '17 at 8:38
  • The CMK is stored outside of SQL Server, so the person would need to have the SA login, sql auth enabled, AND and copy of the certificate or access to a HSM with credentials for said HSM in order to get this to work with Always Encrypted. Definitely not trivial as you make it out to seem. @user3204806 – Sean Gallardy Dec 27 '17 at 13:07

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.