SQL Server and Database Encryption Keys (Database Engine)

Άρθρο
03/03/2023

Applies to: SQL Server

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.

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 in each database. 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.

SQL Server uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). AES is a newer encryption algorithm than 3DES used in earlier versions. After upgrading an instance of the Database Engine to SQL Server the SMK and DMK should be regenerated in order to upgrade the master keys to AES. For more information about regenerating the SMK, see ALTER SERVICE MASTER KEY (Transact-SQL) and ALTER MASTER KEY (Transact-SQL).

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 an asymmetric 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.

Important Security Information

Accessing objects secured by the service master key requires either the SQL Server Service account that was used to create the key or the computer (machine) account. That is, the computer account that is tied to the system where the key was created. You can change the SQL Server Service account or the computer account without losing access to the key. However, if you change both, you will lose access to the service master key. If you lose access to the service master key without one of these two elements, you be unable to decrypt data and objects encrypted by using the original key.

Connections secured with the service master key cannot be restored without the service master key.

Access to objects and data secured with the database master key require only the password that is used to help secure the key.

Caution

If you lose all access to the keys described earlier, you will lose access to the objects, connections, and data secured by those keys. You can restore the service master key, as described in the links that are shown here, or you can go back to the original encrypting system to recover the access. There is no "back-door" to recover the access.