Skip to content

Latest commit

 

History

History
71 lines (47 loc) · 3.61 KB

back-up-a-database-master-key.md

File metadata and controls

71 lines (47 loc) · 3.61 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Back up a database master key
Learn how to back up a database master key in SQL Server by using Transact-SQL. This essential key encrypts other keys and certificates.
jaszymas
jaszymas
vanto
12/16/2021
sql
security
how-to
database master key [SQL Server], exporting

Back up a database master key

[!INCLUDE SQL Server] This topic describes how to back up a database master key in [!INCLUDEssnoversion] by using [!INCLUDEtsql]. The database master key is used to encrypt other keys and certificates inside a database. If it's deleted or corrupted, [!INCLUDEssNoVersion] may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key and store the backup in a secure off-site location.

Before you begin

Limitations and restrictions

  • The master key must be open and, therefore, decrypted before it's backed up. If it's encrypted with the service master key, the master key doesn't have to be explicitly opened. But if the master key is encrypted only with a password, it must be explicitly opened.

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

Security

Permissions

Requires CONTROL permission on the database.

Using SQL Server Management Studio with Transact-SQL

To back up the database master key

  1. In [!INCLUDEssManStudioFull], connect to the [!INCLUDEssNoVersion] instance containing the database master key you wish to back up.

  2. Choose a password that will be used to encrypt the database master key on the backup medium. This password is subject to complexity checks.

  3. Obtain a removable backup medium for storing a copy of the backed-up key.

  4. Identify an NTFS directory in which to create the backup of the key. This is where you'll create the file specified in the next step. The directory should be protected with highly restrictive access control lists (ACLs).

  5. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  6. On the Standard bar, click New Query.

  7. Copy and paste the following example into the query window and click Execute.

    -- Creates a backup of the "AdventureWorks2022" master key. Because this master key is not encrypted by the service master key, a password must be specified when it is opened.  
USE AdventureWorks2022;   
GO  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sfj5300osdVdgwdfkli7';   

BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'   
    ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg';   
GO

    [!NOTE]
    The file path to the key and the key's password (if it exists) will be different than what is indicated above. Please make sure that both are specific to your server and key set-up.

  8. Copy the file to the backup medium and verify the copy.

  9. Store the backup in a secure, off-site location.

See also

For more information, see OPEN MASTER KEY (Transact-SQL) and BACKUP MASTER KEY (Transact-SQL).