Redigera

Dela via

RESTORE MASTER KEY (Transact-SQL)

  • Artikel

Applies to: SQL Server

Imports a database master key from a backup file.

Important

SQL Server 2022 (16.x) introduces backup and restore support for the database master key to and from an Azure Blob storage. The URL syntax is only available for SQL Server 2022 (16.x) and later.

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: SQL Server 2022 (16.x) 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 SQL Server can't decrypt some of the private keys that are encrypted with it.

Remarks

When the master key is restored, SQL Server 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 SQL Server 2022 (16.x) 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 AdventureWorks2022 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 AdventureWorks2022 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