For securing a SQL Server database, you can take precautions like designing a secure system, encrypting confidential assets, and building a firewall. However, physical theft of media like drives or tapes can still compromise the data.
TDE provides a solution to this problem, with real-time I/O encryption/decryption of data at rest (data and log files) by using a symmetric database encryption key (DEK) secured by a certificate. For more information about migrating TDE certificates manually, see Move a TDE Protected Database to Another SQL Server.
When you migrate a TDE-protected database, the certificate (asymmetric key) used to open the database encryption key (DEK) must also be moved along with the source database. Therefore, you need to recreate the server certificate in the master database of the target SQL Server for that instance to access the database files.
You can use the Azure SQL Migration extension for Azure Data Studio to help you migrate TDE-enabled databases (preview) from an on-premises instance of SQL Server to Azure SQL.
The TDE-enabled database migration process automates manual tasks such as backing up the database certificate keys (DEK), copying the certificate files from the on-premises SQL Server to the Azure SQL target, and then reconfiguring TDE for the target database again.
Important
Currently, only Azure SQL Managed Instance targets are supported. Encrypted Backups aren't supported.
In this tutorial, you learn how to migrate the example AdventureWorksTDE encrypted database from an on-premises instance of SQL Server to an Azure SQL managed instance.
Before you begin the tutorial:
Install the Azure SQL Migration extension from Azure Data Studio Marketplace.
Run Azure Data Studio as Administrator.
Have an Azure account that is assigned to one of the following built-in roles:
Create a target instance of Azure SQL Managed Instance.
Ensure that the login that you use to connect to the SQL Server source is a member of the sysadmin server role.
The machine in which Azure Data Studio runs the TDE-enabled database migration should have connectivity to both sources and target SQL servers.
To open the Migrate to Azure SQL wizard:
In Azure Data Studio, go to Connections. Connect to your on-premises instance of SQL Server. You also can connect to SQL Server on an Azure virtual machine.
Right-click the server connection and select Manage.
In the server menu under General, select Azure SQL Migration.
In the Azure SQL Migration dashboard, select Migrate to Azure SQL to open the migration wizard.
On the first page of the wizard, start a new session or resume a previously saved session.
In Step 1: Databases for assessment in the Migrate to Azure SQL wizard, select the databases you want to assess. Then, select Next.
In Step 2: Assessment results, complete the following steps:
In Choose your Azure SQL target, select Azure SQL Managed Instance.
Select View/Select to view the assessment results.
In the assessment results, select the database, and then review the assessment findings. In this example, you can see the AdventureWorksTDE database is protected with transparent data encryption (TDE). The assessment is recommending to migrate the TDE certificate before migrating the source database to the managed instance target.
Choose Select to open the TDE migration configuration panel.
In the Encrypted database selected section, select Export my certificates and private key to the target.
The Info box section describes the required permissions to export the DEK certificates.
You must ensure the SQL Server service account has write access to network share path you use to backup the DEK certificates. Also, the current user should have administrator privileges on the computer where this network path exists.
Enter the network path.
Then check I give consent to use my credentials for accessing the certificates. With this action, you're allowing the database migration wizard to back up your DEK certificate into the network share.
If you don't want the migration wizard, help you migrate TDE-enabled databases. Select I don't want Azure Data Studio to export the certificates. to skip this step.
If you want to proceed with the TDE certification migration, select Apply.
The TDE migration configuration panel will close, but you can select Edit to modify your network share configuration at any time. Select Next to continue the migration process.
In Step 3: Azure SQL target in the Migrate to Azure SQL wizard, complete these steps for your target managed instance:
Select your Azure account, Azure subscription, the Azure region or location, and the resource group that contains the managed instance.
When you're ready, select Migrate certificates to start the TDE certificates migration.
In Step 3: Migration Status, the Certificates Migration panel will open. The TDE certificates migration progress details are shown on the screen.
Once the TDE migration is completed (or if it has failures), the page displays the relevant updates.
In case you need to retry the migration, select Retry migration.
When you're ready, select Done to continue the migration wizard.
You can monitor the process for each TDE certificate by selecting Migrate certificates.
Select Next to continue the migration wizard until you complete the database migration.
Check the following step-by-step tutorials for more information about migrating databases online or offline to Azure SQL Managed Instance targets:
Your target managed instance should now have the databases, and their respective certificates migrated. To verify the current status of the recently migrated database, copy and paste the following example into a new query window on Azure Data Studio while connected to your managed instance target. Then, select Run.
USE master;
GO
SELECT db_name(database_id),
key_algorithm,
encryption_state_desc,
encryption_scan_state_desc,
percent_complete
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID('Your database name');
GO
The query returns the information about the database, the encryption status and the pending percent complete. In this case, it's zero because the TDE certificate has been already completed.
For more information about encryption with SQL Server, see Transparent data encryption (TDE).
LimitationsThe following table describes the current status of the TDE-enabled database migrations support by Azure SQL target:
Target Support Status Azure SQL Database No Azure SQL Managed Instance Yes Preview SQL Server on Azure VM NoRetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4
