Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to:
SQL Server 2022 (16.x) and later versions
Note
SQL Server 2022 (16.x) introduces support for exporting and importing symmetric keys, either to or from Azure Blob storage or file.
Exports the symmetric key.
Transact-SQL syntax conventions
Syntax
BACKUP SYMMETRIC KEY key_name TO
{
FILE = 'path_to_file'
| URL = 'Azure Blob storage URL'
}
ENCRYPTION BY PASSWORD = 'password'
Arguments
FILE = 'path_to_file'
Specifies the complete path, including file name, to the file to which the symmetric key will be exported. The path might be a local path or a UNC path to a network location.
URL = 'Azure Blob storage URL'
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.
ENCRYPTION BY PASSWORD = 'password'
The password used to encrypt the symmetric key in the backup file. This password is subject to complexity checks. For more information, see Password Policy.
Prerequisite
In order to back up the symmetric key to an Azure Blob storage, you need to:
- Have an Azure storage account.
- Create stored access policy and shared access storage.
- Create a SQL Server credential using a shared access signature.
For more information, see Tutorial: Use Azure Blob Storage with SQL Server.
Remarks
The symmetric key must be open in the current session before you back it up. To open the key, use OPEN SYMMETRIC KEY.
Permissions
Requires ALTER permission on the symmetric key. If adding encryption by a certificate or asymmetric key, requires VIEW DEFINITION permission on the certificate or asymmetric key. If dropping encryption by a certificate or asymmetric key, requires CONTROL permission on the certificate or asymmetric key.
Examples
In the following example, the symmetric key, encrypted by the certificate symmetric_key_cert, is opened and backed up to a file.
OPEN SYMMETRIC KEY symmetric_key
DECRYPTION BY CERTIFICATE symmetric_key_cert;
BACKUP SYMMETRIC KEY symmetric_key
TO FILE = 'c:\temp_backups\keys\symmetric_key'
ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';
CLOSE SYMMETRIC KEY symmetric_key;
In the following example, the symmetric key is opened and backed up to Azure Blob Storage.
OPEN SYMMETRIC KEY symmetric_key
DECRYPTION BY CERTIFICATE symmetric_key_cert;
BACKUP SYMMETRIC KEY symmetric_key
TO URL = 'https://mydocsteststorage.blob.core.windows.net/mytestcontainer/symmetric_key.bak'
ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';
CLOSE SYMMETRIC KEY symmetric_key;