Where is the service master key (SMK) and database master key stored?

Waseem Gondal 1 Reputation point
2022-08-25T12:12:55.567+00:00

Sql server encryption has got the service master key at the top of the hierarchy.

The database master key is encrypted with the service master key.

The database master key is stored in the respective database and also in the master db?

Where is the service master key (SMK) stored?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,046 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,611 Reputation points
    2022-08-25T12:45:19.227+00:00

    You need to backup your keys.

    We recommend that you back up the master key as soon as it is created, and store the backup in a secure, off-site location.

    Please see:
    https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/back-up-the-service-master-key?view=sql-server-ver16

  2. CathyJi-MSFT 20,776 Reputation points Microsoft Vendor
    2022-08-26T03:26:20.503+00:00

    Hi @Waseem Gondal ,

    >The database master key is stored in the respective database and also in the master db?

    Yes, you are right.

    Quote from MS document;

    >The database master key is stored in both the database where it is used and in the master system database.

    In addition, you should back up the database master key and store the backup in a secure off-site location. Refer to MS document Back up a database master key.

    >Where is the service master key (SMK) stored?

    I did not find this information from MS document.

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

    In addition, the service master key is the root of the encryption hierarchy. It should be backed up and stored in a secure, off-site location. Refer to MS document Back Up the Service Master Key.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".