Sysadmin Central

For most SQL Managed Instances, backups happen automatically. Microsoft handles encryption, storage, and retention behind the scenes. While this “set-and-forget” model works for many, organisations with regulatory requirements or strict data governance policies often need the ability to control where backups are stored and which keys encrypt them.

In such cases, performing a backup to an Azure Storage Account using a customer-managed key (CMK) allows you to keep a copy of your backup offsite, retain full control over encryption and potentially to align with compliance and regulatory requirements

This article walks through how to safely perform backups to an offsite location outside of Azure while ensuring that your encryption key is properly managed.

Understanding Backup Encryption with Customer-Managed Keys

Azure SQL Managed Instance uses Transparent Data Encryption (TDE) to secure data at rest. By default, TDE uses Service-managed keys, but you can switch to a CMK stored in Azure Key Vault.

When combined with a backup to Azure Storage, the process becomes:

• Your SQL Managed Instance encrypts the database using a Database Encryption Key (DEK) which is itself encrypted using the CMK from Key Vault
• Backups exported to a Storage Account remain encrypted with that key
• You maintain the ability to restore or reimport backups using the same CMK to the same or an alternative SQL Server instance

A critical point is that Azure Key Vault keys cannot be exported once created inside the vault unless it’s a HSM Vault, so you’ll need to generate the key outside of Azure and then import a copy of it into Key Vault.

Step 1 – Create an Azure Key Vault and Generate a Key

1. Create a Key Vault:

az keyvault create \
  --name mi-backup-keyvault \
  --resource-group rg-sql \
  --location eastus

Ensure that soft-delete and purge protection are enabled otherwise you cannot use stored keys to encrypt SQL data.

2. Generate the encryption key locally. For example, using PowerShell:

$certName = "exampleKey"
$password = "UcUS.\5#4hmD[1~9EV6£g"
$certPath = "C:\temp\$certName"
# Create self-signed cert with RSA private key (exportable)
$cert = New-SelfSignedCertificate `
    -Subject "CN=$certName" `
    -CertStoreLocation "Cert:\CurrentUser\My" `
    -KeyExportPolicy Exportable `
    -KeySpec Signature `
    -KeyAlgorithm RSA `
    -KeyLength 2048 `
    -HashAlgorithm SHA256 `
    -NotAfter (Get-Date).AddYears(1).AddMonths(2)
# Export the cert + private key as PFX
$pfxPassword = ConvertTo-SecureString -String $password -Force -AsPlainText
Export-PfxCertificate `
    -Cert "Cert:\CurrentUser\My\$($cert.Thumbprint)" `
    -FilePath "$certPath.pfx" `
    -Password $pfxPassword

3. Keep a secure offline copy. This copy is your only way to reimport the key later.
4. Import the key into Key Vault

Step 2 – Grant SQL Managed Instance Access to the Key

Your SQL Managed Instance must be able to use the key for encryption and backup operations.

1. Assign the Managed Identity of your instance the Key Vault Crypto Service Encryption User role to allow it to access the uploaded encryption key.
2. Navigate to the SQL Managed Instance and then to the Transparent Data Encryption pane.
3. Select Customer-managed key, then select the Key Vault and the uploaded Key.
4. Once confirmed, the database will start using the new key.

Although Microsoft state this process does not cause any disruption, we did see that the SQL instance failed to return metrics for several minutes intermittently following the change.

Without these permissions, backup operations to Storage Accounts will fail.

Step 3 – Prepare the Azure Storage Account

Create a Storage Account to store your backups:

az storage account create \
  --name mibackups \
  --resource-group rg-sql \
  --location eastus \
  --sku Standard_LRS

Create a container for the backups:

az storage container create \
  --name sql-backups \
  --account-name mibackups

Step 4 – Perform the Backup Using the Customer-Managed Key

To perform a backup, the simplest option is to access the database using SQL Server Management Studio and then follow the process shown below to configure a SAS Token for access to the Storage Account and then to perform a backup –

1. Expand the Databases tab then right-click the database you plan to back up. (I’ll show how to backup all or a subset of tables further down this post).
2. Select Tasks > Back Up in the menu.
3. Select the Add button and then New container in the window that appears.
4. Select the relevant Tenant, Subscription and Storage Account containing the blob container we created earlier.
5. Set an expiry and then click ‘Create Credential‘ to store a credential on the server.
6. Select OK then OK to return to the Back Up Database window.
7. From here, the credential is created and will be available whenever needed on the server.
8. Configure the available options if required, or optionally select Script in the command bar at the top to export an SQL Script which may be ran whenever needed.
9. The exported backup is encrypted with your customer-managed key. It can now be copied offsite, archived, or stored for long-term retention while remaining protected.

Here’s an example SQL script which allows you to loop through every non-system database performing backups –

DECLARE @storageContainerUrl NVARCHAR(MAX) = N'https://storageaccountname.blob.core.windows.net/containername'
DECLARE @dbName SYSNAME;
DECLARE @dateSuffix NVARCHAR(20) = FORMAT(GETDATE(), 'yyyy_MM_dd_HHmmss');
DECLARE @backupUrl NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
  AND name NOT LIKE 'Archive%'

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build backup URL with timestamp
    SET @backupUrl = @storageContainerUrl + '/' + @dbName + '_' + @dateSuffix + '.bak';

    -- Build dynamic BACKUP DATABASE command
    SET @sql = N'
    BACKUP DATABASE ' + QUOTENAME(@dbName) + N'
    TO URL = N''' + @backupUrl + N'''
    WITH
        BLOCKSIZE = 65536,
        MAXTRANSFERSIZE = 4194304,
        COMPRESSION,
        COPY_ONLY,
        NOFORMAT,
        NOINIT,
        NAME = N''' + @dbName + N' - Full Database Backup'',
        NOSKIP,
        NOREWIND,
        NOUNLOAD,
        STATS = 10;
    ';

    -- Execute the backup
    PRINT 'Backing up database: ' + @dbName;
    EXEC sp_executesql @sql;

    FETCH NEXT FROM db_cursor INTO @dbName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

Step 5 – Reimporting or Restoring Backups

If you need to restore the backup to another SQL Managed Instance or after a disaster, the same customer-managed key must be available in the target Key Vault. Since the Key Vault does not allow exporting the key, you must use your offline copy to import the key again if necessary.

The overall process assuming no access to the Azure tenant would be as follows –

1. Create a new SQL Managed Instance, Key Vault and Storage Account.
2. Upload the archived database to the Storage Account.
3. Upload the copy of the encryption key to the Key Vault.
4. Assign access to the SQL Managed Instance identity.
5. Configure the SQL Managed Instance to use the CMK key.
6. Restore the database to the SQL Managed Instance.

Key Operational Considerations

When managing backups with CMK:

• Always maintain a secure offline copy of your key
• Plan for key rotation and vault availability
• Monitor Key Vault access and audit logs
• Store backups in multiple regions for resilience
• Losing the key makes backups permanently unreadable

Final Thoughts

Using a customer-managed key to perform backups to Azure Storage adds complexity, but it gives you full control over encryption and compliance, and most importantly allows a completely offsite backup.

Handled correctly, this approach ensures your SQL Managed Instance backups are secure, portable, and fully under your control.