Azure SQL Managed Instance TDE

Question

Azure SQL Managed Instance TDE

Johannes Maly 101

I have a database with TDE enabled in a SQL Managed Instance. I need to backup and restore this database to an On-Prem SQL Server 2022. How can I restore the TDE encrypted backup without disabling TDE on that database in SQL-MI?

I need this to transfer the data to one or more On-Prem test systems.

So far I only found instructions that either deal with moving an On-Prem DB with TDE enabled to Azure SQL-MI or the way back by dropping TDE on the database that should be backed up and restored in SQL-MI.

Can anyone give me some hint how to manage this problem?

Thanks in advance

Johannes

Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-01T10:30:09.9966667+00:00
Hi Johannes Maly,

Greetings!

We would like to inform you that, restoring a TDE-encrypted database from a SQL Managed Instance (SQL MI) to an On-Prem SQL Server 2022 without disabling TDE involves several steps. Here's a high-level overview of the process:

Backup the TDE-encrypted Database: Take a COPY_ONLY backup of your TDE-protected database on SQL MI. This ensures that the backup does not interfere with the sequence of regular backups.

BACKUP DATABASE [YourDatabase] TO URL = 'https://yourstorageaccount.blob.core.windows.net/yourcontainer/YourDatabase.bak' WITH COPY_ONLY;

Export the TDE Certificate and Key: On your SQL MI, export the TDE certificate and the associated private key. This is necessary because the On-Prem SQL Server will need these to decrypt the database.

BACKUP CERTIFICATE YourTDECert TO FILE = 'C:\path\to\YourTDECert.cer' WITH PRIVATE KEY ( FILE = 'C:\path\to\YourTDECert.pvk', ENCRYPTION BY PASSWORD = 'YourStrongPassword' );

Transfer the Backup and Keys and Restore the TDE Certificate and Key on On-Prem SQL Server: Move the backup file and the exported TDE certificate and key to your On-Prem SQL Server and on your On-Prem SQL Server, create a new certificate from the exported TDE certificate and key. This will allow the server to decrypt the database during the restore process.

CREATE CERTIFICATE YourTDECert FROM FILE = 'C:\path\to\YourTDECert.cer' WITH PRIVATE KEY ( FILE = 'C:\path\to\YourTDECert.pvk', DECRYPTION BY PASSWORD = 'YourStrongPassword' );

Restore the Database: Restore the database from the backup file on your On-Prem SQL Server. Since the TDE certificate and key are already in place, the server will be able to decrypt the database during the restore process.

RESTORE DATABASE [YourDatabase] FROM DISK = 'C:\path\to\YourDatabase.bak' WITH MOVE 'YourDatabase_Data' TO 'C:\path\to\YourDatabase.mdf', MOVE 'YourDatabase_Log' TO 'C:\path\to\YourDatabase.ldf';

This process ensures that the TDE encryption remains intact throughout the backup and restore process.

Please refer to the below mentioned link for more information.

https://techcommunity.microsoft.com/blog/azuresqlblog/take-a-copy-only-backup-of-tde-protected-database-on-azure-sql-managed-instance/643407

https://www.sqlshack.com/restoring-transparent-data-encryption-tde-enabled-databases-on-a-different-server/

https://learn.microsoft.com/en-us/answers/questions/1164561/always-encryted-vs-transparent-data-encryption

I hope this information helps. Please do let us know if you have any further queries.
Johannes Maly 101 Reputation points

2025-04-01T10:52:42.1266667+00:00

Thx for the fast response.

I created a certificate on the SQL-MI

Next I tried to backup the certificate on the SQL-MI

BACKUP CERTIFICATE TDECert

TO FILE = 'C:\Temp\YourTDECert.cer'

WITH PRIVATE KEY ( FILE = 'C:\Temp\TDECert.pvk', ENCRYPTION BY PASSWORD = 'Your-Strong+Password!' );

And as expected I got an error

Msg 40538, Level 16, State 3, Line 12

A valid URL beginning with 'https://' is required as value for any filepath specified.

Consulting MS docu I found
But there is no option to specify an URL like in

And excatly this certificate backup step gave me headache for some days. So my question is how to backup a certificate from a SQL managed instance.
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff

2025-04-01T11:42:29.43+00:00
Hi Johannes Maly,

We apologize for the inconvenience caused.

The error you are encountering is due to the fact that SQL Managed Instance (SQL MI) requires the use of Azure Blob Storage for file operations, including backing up certificates. You cannot use local file paths like C:\Temp\ on SQL MI.

Here are the detailed steps how you can back up the TDE certificate to Azure Blob Storage:

Step 1: Create a Storage Account and Container

In the Azure portal, create a storage account if you don't already have one.

Create a container within the storage account.

Step 2: Generate a SAS Token

In the Azure portal, navigate to your storage account.

Under Settings, select Shared access signature.

Generate a SAS token with write permissions.

Step 3: Backup the Certificate: Use the following SQL commands to back up the certificate to Azure Blob Storage.

-- Define the URL for the certificate and private key files DECLARE @certUrl NVARCHAR(MAX) = 'https://yourstorageaccount.blob.core.windows.net/yourcontainer/YourTDECert.cer?sv=...'; -- Include your SAS token here DECLARE @keyUrl NVARCHAR(MAX) = 'https://yourstorageaccount.blob.core.windows.net/yourcontainer/TDECert.pvk?sv=...'; -- Include your SAS token here -- Backup the certificate BACKUP CERTIFICATE TDECert TO FILE = @certUrl WITH PRIVATE KEY ( FILE = @keyUrl, ENCRYPTION BY PASSWORD = 'Your-Strong+Password!' );

Replace yourstorageaccount, yourcontainer, and the SAS token placeholders with your actual storage account name, container name, and SAS token.

This should allow you to back up the TDE certificate and private key to Azure Blob Storage.

Please refer to the below mentioned links for more information.

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/doc-changes-updates-known-issues?view=azuresql

https://techcommunity.microsoft.com/blog/azuresqlblog/troubleshooting-potential-backuprestore-issues-on-azure-sql-managed-instance/633556

I hope this information helps. Please do let us know if you have any further queries.
Johannes Maly 101 Reputation points

2025-04-01T13:31:38.8166667+00:00

Thank you very much for the clarification. I will give it a try and come back as soon I have manged (or not) to backup the certificate.

BR Johannes

Johannes Maly 101

I tested the approach with the storage account as suggested. Sadly this didn't work.

I created a shared access signature on the storage account, tried to select all that was selectable:
User's image

Then I tried to backup the certificate from SSMS:

-- SAS token            : sv=2024...
-- Blob Service SAS URL : https://xxx.blob.core.windows.net/?sv=2024...
BACKUP CERTIFICATE [TDECert]
 TO FILE = 'https://xxx.blob.core.windows.net/dbbackups/TDECert.cer?sv=2024...'
WITH PRIVATE KEY (
    FILE = 'https://xxx.blob.core.windows.net/dbbackups/TDECert.key?sv=2024...'
  , ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
);

But an error popped up:

Cannot write into file 'https://xxx.blob.core.windows.net/dbbackups/TDECert.key?sv=2024-11-04...'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

Tried also with SAS created on blob container:
User's image

And retried to backup the certificate from SSMS:

-- Blob SAS token       : sp=ra...
-- Blob SAS URL         : https://xxx.blob.core.windows.net/dbbackups?sp=ra...
BACKUP CERTIFICATE [TDECert]
    TO FILE = 'https://xxx.blob.core.windows.net/dbbackups/TDECert.cer?sp=ra...'
WITH PRIVATE KEY (
       FILE = 'https://xxx.blob.core.windows.net/dbbackups/TDECert.key?sp=ra...'
     , ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
);

And got same error:

Cannot write into file 'https://xxx.blob.core.windows.net/dbbackups/TDECert.key?sp=rac...'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

Also tried to assign blob data roles in access control (IAM) to the managed instance:
User's image

Did not change anything. Anyhow backup of database and master key works without any problems:

BACKUP DATABASE [testdb] 
TO DISK = N'https://xxx.blob.core.windows.net/dbbackups/testdb.bak'
WITH  COPY_ONLY, CHECKSUM
;

BACKUP MASTER KEY 
	TO URL = 'https://xxx.blob.core.windows.net/dbbackups/MasterKey'
    ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
;

PratikLad 800 Reputation points Microsoft External Staff

2025-04-04T09:18:50.11+00:00

Hi Johannes Maly,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution

please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
PratikLad 800 Reputation points Microsoft External Staff

2025-04-07T03:47:05.31+00:00

Hi Johannes Maly,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution

please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Johannes Maly 101 Reputation points

2025-04-07T05:27:51.89+00:00

Hi PratikLad

Sorry for my late response but I was on holiday from friday until today.
So far I haven't found the time to follow all the steps in your great response.
I only watched the video from T. Hill and it seems he explains all the necessary steps and also demonstrated them in a real example. I assume this will solve my problem.

So thank you very much for your support.

BR Johannes

1 answer

Your answer

Johannes Maly 101 Reputation points

2025-04-01T10:52:42.1266667+00:00

Thx for the fast response.

I created a certificate on the SQL-MI

Next I tried to backup the certificate on the SQL-MI

BACKUP CERTIFICATE TDECert

TO FILE = 'C:\Temp\YourTDECert.cer'

WITH PRIVATE KEY ( FILE = 'C:\Temp\TDECert.pvk', ENCRYPTION BY PASSWORD = 'Your-Strong+Password!' );

And as expected I got an error

Msg 40538, Level 16, State 3, Line 12

A valid URL beginning with 'https://' is required as value for any filepath specified.

Consulting MS docu I found
But there is no option to specify an URL like in

And excatly this certificate backup step gave me headache for some days. So my question is how to backup a certificate from a SQL managed instance.
Johannes Maly 101 Reputation points

2025-04-01T13:31:38.8166667+00:00

Thank you very much for the clarification. I will give it a try and come back as soon I have manged (or not) to backup the certificate.

BR Johannes
Johannes Maly 101 Reputation points

2025-04-02T07:44:38.8566667+00:00

I tested the approach with the storage account as suggested. Sadly this didn't work.

I created a shared access signature on the storage account, tried to select all that was selectable:

Then I tried to backup the certificate from SSMS:

-- SAS token : sv=2024... -- Blob Service SAS URL : https://xxx.blob.core.windows.net/?sv=2024... BACKUP CERTIFICATE [TDECert] TO FILE = 'https://xxx.blob.core.windows.net/dbbackups/TDECert.cer?sv=2024...' WITH PRIVATE KEY ( FILE = 'https://xxx.blob.core.windows.net/dbbackups/TDECert.key?sv=2024...' , ENCRYPTION BY PASSWORD = 'Pa$$w0rd' );

But an error popped up:

Cannot write into file 'https://xxx.blob.core.windows.net/dbbackups/TDECert.key?sv=2024-11-04...'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

Tried also with SAS created on blob container:

And retried to backup the certificate from SSMS:

-- Blob SAS token : sp=ra... -- Blob SAS URL : https://xxx.blob.core.windows.net/dbbackups?sp=ra... BACKUP CERTIFICATE [TDECert] TO FILE = 'https://xxx.blob.core.windows.net/dbbackups/TDECert.cer?sp=ra...' WITH PRIVATE KEY ( FILE = 'https://xxx.blob.core.windows.net/dbbackups/TDECert.key?sp=ra...' , ENCRYPTION BY PASSWORD = 'Pa$$w0rd' );

And got same error:

Cannot write into file 'https://xxx.blob.core.windows.net/dbbackups/TDECert.key?sp=rac...'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

Also tried to assign blob data roles in access control (IAM) to the managed instance:

Did not change anything. Anyhow backup of database and master key works without any problems:

BACKUP DATABASE [testdb] TO DISK = N'https://xxx.blob.core.windows.net/dbbackups/testdb.bak' WITH COPY_ONLY, CHECKSUM ;

BACKUP MASTER KEY TO URL = 'https://xxx.blob.core.windows.net/dbbackups/MasterKey' ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ;
PratikLad 800 Reputation points Microsoft External Staff

2025-04-04T09:18:50.11+00:00

Hi Johannes Maly,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution

please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
PratikLad 800 Reputation points Microsoft External Staff

2025-04-07T03:47:05.31+00:00

Hi Johannes Maly,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution

please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Johannes Maly 101 Reputation points

2025-04-07T05:27:51.89+00:00

Hi PratikLad

Sorry for my late response but I was on holiday from friday until today.
So far I haven't found the time to follow all the steps in your great response.
I only watched the video from T. Hill and it seems he explains all the necessary steps and also demonstrated them in a real example. I assume this will solve my problem.

So thank you very much for your support.

BR Johannes

Answer 1

Hi Johannes Maly,

You can restore an encrypted database to SQL Server only if it was encrypted with a customer-managed key and the destination server has access to the same key that's used to encrypt the database. If you are using Service-managed key you must disable TDE encryption on the source database before taking the backup as Service-managed key is managed by Microsoft, you cannot restore the database using this key.

To restore databases that are encrypted at rest by using Transparent Data Encryption (TDE), the destination instance of SQL Server must have access to the same key that's used to protect the source database through the SQL Server Connector for Azure Key Vault

Steps are as below:

Set Up a Microsoft Entra Service Principal, generate a client secret for this application. Note the Application (client) ID and the client secret's value; these will be needed later.
Create an Azure Key Vault and Ensure that the above created Microsoft Entra service principal has at least the following permissions for the Key Vault: get, list, wrapKey, and unwrapKey.
Download and install the SQL Server Connector, which allows SQL Server to communicate with Azure Key Vault.
Configure SQL Server to use EKM by running the following Transact-SQL script in master database:


EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

-- Enable EKM provider

EXEC sp_configure 'EKM provider enabled', 1;

GO

RECONFIGURE;

Create a cryptographic provider by using the SQL Server Connector, which is an EKM provider for the Azure Key Vault. In this example, the provider name is AzureKeyVault_EKM.


CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM

FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';

GO

Set up a SQL Server credential for a SQL Server login to use the key vault.


CREATE CREDENTIAL sysadmin_ekm_cred

    WITH IDENTITY = 'DocsSampleEKMKeyVault',                            -- for public Azure

    -- WITH IDENTITY = 'DocsSampleEKMKeyVault.vault.usgovcloudapi.net', -- for Azure Government

    -- WITH IDENTITY = 'DocsSampleEKMKeyVault.vault.azure.cn',          -- for Microsoft Azure operated by 21Vianet

    -- WITH IDENTITY = 'DocsSampleEKMKeyVault.vault.microsoftazure.de', -- for Azure Germany

    -- WITH IDENTITY = '<name of Managed HSM>.managedhsm.azure.net',    -- for Managed HSM (HSM URI in the Azure portal resource)

           --<----Application (Client) ID ---><--Microsoft Entra app (Client) ID secret-->

    SECRET = 'd956f6b9xxxxxxxyrA8X~PldtMCvUZPxxxxxxxx'

FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM;

-- Add the credential to the SQL Server administrator's domain login

ALTER LOGIN [<domain>\<login>]

ADD CREDENTIAL sysadmin_ekm_cred;

Open your Azure Key Vault key in your SQL Server instance.


CREATE ASYMMETRIC KEY EKMSampleASYKey

FROM PROVIDER [AzureKeyVault_EKM]

WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0',

CREATION_DISPOSITION = OPEN_EXISTING;

Create a new login by using the asymmetric key in SQL Server that you created in the preceding step.


 --Create a Login that will associate the asymmetric key to this login

CREATE LOGIN TDE_Login

FROM ASYMMETRIC KEY EKMSampleASYKey;

Create a new login from the asymmetric key in SQL Server. Drop the credential mapping so that the credentials can be mapped to the new login.


--Now drop the credential mapping from the original association

ALTER LOGIN [<domain>\<login>]

DROP CREDENTIAL sysadmin_ekm_cred;

Alter the new login, and map the EKM credentials to the new login.


--Now add the credential mapping to the new Login

ALTER LOGIN TDE_Login

ADD CREDENTIAL sysadmin_ekm_cred;

To restore your database to SQL Server, run the following sample T-SQL command with file paths appropriate to your environment


RESTORE DATABASE [SampleDB]

FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<containername>/SampleDB.bak'

WITH

MOVE 'data_0' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\SampleDB_data_0.mdf',

MOVE 'log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\SampleDBlog.ldf',

MOVE 'XTP' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\SampleDB_xtp.xtp'

Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-ver16&tabs=portal - till step 5 configure master database.

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-database-to-sql-server?view=azuresql&tabs=managed-identity - from restore to SQL server

You can also refer this video for more understanding - https://www.youtube.com/watch?v=B8U_UZvQrfs

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Azure SQL Managed Instance TDE

1 answer

Your answer