SQL Server Always Encrypted – Securing from ‘sa’ Account

always-encryptedsql serverssms

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.

Best Answer

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.

Related Solutions

Sql-server – How to add linked server SQL Server 2008 R2

Change the server type to SQL server, put the servername,port number in the Linked Server name; on the Security section, change to "Be made using this security context" and enter the user name/password, assuming they are SQL Authentication credentials.

SQL Server – Restore Database with Encrypted Column Without Overwriting Service Master Key

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available.

I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key.

What you need to do is to restore the database, open the database master key using the DBMK password and then add the Server 2 SMK encryption to the DBMK:

RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Best Answer

Related Solutions

Sql-server – How to add linked server SQL Server 2008 R2

SQL Server – Restore Database with Encrypted Column Without Overwriting Service Master Key

Related Question