Contents
How is the service master key used in SQL Server?
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.
What happens when the service master key is regenerated?
When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised.
How is the Microsoft service master key encrypted?
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.
Is the service master key AES or AES?
The service master key itself is a symmetric encryption key. For SQL Server version 2005 to 2008 R2 it was used with the triple DES encryption algorithm. Since SQL Server 2012 new service master keys are AES keys. However, upgrading an existing SQL Server installation does not change the key.
Where is the encryption key stored in SQL Server?
When it is created, the master key is encrypted by using the Triple DES algorithm and a user-supplied password. To enable the automatic decryption of the 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.
How to access objects secured by the service master key?
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.