SQL Server uses encryption keys to help secure data, credentials, and connection information that is stored in a server database. SQL Server has two kinds of keys: symmetric and asymmetric.
Symmetric keys use the same password to encrypt and decrypt data.
Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).
In SQL Server, encryption keys include a combination of public, private, and symmetric keys that are used to protect sensitive data. The symmetric key is created during SQL Server initialization when you first start the SQL Server instance. The key is used by SQL Server to encrypt sensitive data that is stored in SQL Server. Public and private keys are created by the operating system and they are used to protect the symmetric key. A public and private key pair is created for each SQL Server instance that stores sensitive data in a database.
Applications for SQL Server and Database Keys
SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database.
The Service Master Key is the root of SQL Server’s Encryption Hierarchy. As such, there can only be one service master key per SQL Server instance. The service master key is used to protect (encrypt) other keys, mainly the database master keys. It cannot be used directly to encrypt data
Service master key
The Service Master Key is the root of the SQL Server encryption hierarchy. The SMK is automatically generated the first time the SQL Server instance is started and is used to encrypt a linked server password, credentials, and the database master key. The SMK is encrypted by using the local machine key using the Windows Data Protection API (DPAPI). The DPAPI uses a key that is derived from the Windows credentials of the SQL Server service account and the computer’s credentials. The service master key can only be decrypted by the service account under which it was created or by a principal that has access to the machine’s credentials.
The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.
Database master key
The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database. It can also be used to encrypt data, but it has length limitations that make it less practical for data than using a symmetric key. To enable the automatic decryption of the database master key, a copy of the key is encrypted by using the SMK. It is stored in both the database where it is used and in the master system database.
The copy of the DMK stored in the master system database is silently updated whenever the DMK is changed. However, this default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of the ALTER MASTER KEY statement. A DMK that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.
Managing SQL Server and Database Keys
Managing encryption keys consists of creating new database keys, creating a backup of the server and database keys, and knowing when and how to restore, delete, or change the keys.
To manage symmetric keys, you can use the tools included in SQL Server to do the following:
- Back up a copy of the server and database keys so that you can use them to recover a server installation, or as part of a planned migration.
- Restore a previously saved key to a database. This enables a new server instance to access existing data that it did not originally encrypt.
- Delete the encrypted data in a database in the unlikely event that you can no longer access encrypted data.
- Re-create keys and re-encrypt data in the unlikely event that the key is compromised. As a security best practice, you should re-create the keys periodically (for example, every few months) to protect the server from attacks that try to decipher the keys.
- Add or remove a server instance from a server scale-out deployment where multiple servers share both a single database and the key that provides reversible encryption for that database.
Key Check
##MS_ServiceMasterKey##
##MS_DatabaseMasterKey##
SELECT * FROM sys. symmetric_keys;
SELECT *
FROM sys. symmetric_keys AS SK
WHERE SK.name = '##MS_DatabaseMasterKey##';
Comments