Skip to content

Latest commit

 

History

History
119 lines (90 loc) · 5.73 KB

restore-master-key-transact-sql.md

File metadata and controls

119 lines (90 loc) · 5.73 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
RESTORE MASTER KEY (Transact-SQL)
RESTORE MASTER KEY (Transact-SQL)
VanMSFT
vanto
05/24/2022
sql
t-sql
reference
RESTORE_MASTER_KEY_TSQL
RESTORE MASTER KEY
LOAD_MASTER_KEY_TSQL
LOAD MASTER KEY
database master key [SQL Server], importing
encryption [SQL Server], Database Master Key
copying Database Master Keys
importing Database Master Keys
cryptography [SQL Server], Database Master Key
transferring Database Master Keys
RESTORE MASTER KEY statement
TSQL

RESTORE MASTER KEY (Transact-SQL)

[!INCLUDE SQL Server]

Imports a database master key from a backup file.

Important

[!INCLUDE sssql22-md] introduces backup and restore support for the database master key to and from an Azure Blob storage. The URL syntax is only available for [!INCLUDE sssql22-md] and later.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

RESTORE MASTER KEY FROM 
  {
    FILE = 'path_to_file'
  | URL = 'Azure Blob storage URL'
  }  
    DECRYPTION BY PASSWORD = 'password'  
    ENCRYPTION BY PASSWORD = 'password'  
    [ FORCE ]

Arguments

FILE ='path_to_file'
Specifies the complete path, including file name, to the stored database master key. path_to_file can be a local path or a UNC path to a network location.

URL ='Azure Blob storage URL'
Applies to: [!INCLUDE sssql22-md] and later
Is the URL for your Azure Blob storage, in the format similar to https://<storage_account_name>.blob.core.windows.net/<storage_container_name>/<backup_file_name>.bak.

DECRYPTION BY PASSWORD ='password'
Specifies the password that is required to decrypt the database master key that is being imported from a file.

ENCRYPTION BY PASSWORD ='password'
Specifies the password that is used to encrypt the database master key after it has been loaded into the database.

FORCE
Specifies that the RESTORE process should continue, even if the current database master key isn't open, or if [!INCLUDEssNoVersion] can't decrypt some of the private keys that are encrypted with it.

Remarks

When the master key is restored, [!INCLUDEssNoVersion] decrypts all the keys that are encrypted with the currently active master key, and then encrypts these keys with the restored master key. This resource-intensive operation should be scheduled during a period of low demand. If the current database master key isn't open or can't be opened, or if any of the keys that are encrypted by it can't be decrypted, the restore operation fails.

Use the FORCE option only if the master key is irretrievable or if decryption fails. Information that is encrypted only by an irretrievable key will be lost.

If the master key was encrypted by the service master key, the restored master key will also be encrypted by the service master key.

If there's no master key in the current database, RESTORE MASTER KEY creates a master key. The new master key won't be automatically encrypted with the service master key.

If you're using [!INCLUDE sssql22-md] and later, and want to restore the database master key from an Azure Blob storage, the following prerequisites apply:

  1. Have an Azure storage account.

  2. Create stored access policy and shared access storage.

  3. Create a SQL Server credential using a shared access signature.

    For more information, see Tutorial: Use Azure Blob Storage with SQL Server.

Permissions

Requires CONTROL permission on the database.

Examples

The following example restores the database master key of the [!INCLUDE sssampledbobject-md] database from a file.

USE AdventureWorks2022;  
RESTORE MASTER KEY   
    FROM FILE = 'c:\backups\keys\AdventureWorks2022_master_key'   
    DECRYPTION BY PASSWORD = '3dH85Hhk003#GHkf02597gheij04'   
    ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';  
GO

The following example restores the database master key of the [!INCLUDE sssampledbobject-md] database from an Azure Blob storage.

USE AdventureWorks2022;  
RESTORE MASTER KEY   
    FROM URL = 'https://mydocsteststorage.blob.core.windows.net/mytestcontainer/AdventureWorks2022_master_key.bak'   
    DECRYPTION BY PASSWORD = '3dH85Hhk003#GHkf02597gheij04'   
    ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';  
GO

See also

CREATE MASTER KEY (Transact-SQL)
ALTER MASTER KEY (Transact-SQL)
Encryption Hierarchy
RESTORE SYMMETRIC KEY