Introducing Login and TDE-enabled database migrations for the Azure SQL Migration extension
Published Mar 01 2023 09:00 AM 5,772 Views
Microsoft

Today, we're announcing the public preview of logins and TDE-enabled database migrations in the Azure SQL Migration extension for Azure Data Studio. These new features provide a secure and improved user experience for migrating TDE databases and SQL/Windows logins to Azure SQL.

 

Prerequisites

Before migrating to Azure SQL Database using the Azure Migration extension for Azure Data Studio, be sure that the following prerequisites are in place. 

 

Optionally, you can migrate your on-premises databases to your selected Azure SQL target by using one of the following tutorials:

 

Migration scenario Migration mode
SQL Server to Azure SQL Managed Instance Online / Offline 
SQL Server to SQL Server on Azure Virtual Machines  Online / Offline 

 

Login migrations

Migrating SQL logins and Windows accounts are one of the most common post-migration tasks, usually performed manually through T-SQL or PowerShell scripting.  

 

For this reason, we are introducing a new user experience with an independent workflow to migrate logins and server roles from your on-premises source SQL Server to Azure SQL Managed Instances or SQL Server on Azure Virtual Machines. 

carlosrobles_0-1677525238211.png

 

This login migration experience automates manual tasks such as synchronizing logins with their corresponding user mappings and replicating server/securable permissions and server roles. 

 

The login migration wizard will guide you through the following steps: 

Specify your Azure SQL target 

In this step, you need to select your Azure SQL target. Specifying the Azure account, subscription, resource group, and the Azure SQL Managed Instance or SQL Server on Azure VM target.

 

Select logins to migrate 

In this step, you must select the logins you wish to migrate from the source SQL server to the Azure SQL target. You'll be prompted to enter the Azure Active Directory domain name for Windows accounts.

 

Monitor migration process 

After the Login migration starts, you can monitor the complete list of logins to be migrated or check each login individually.  

 

The following table describes the current support status of login migrations by Azure SQL target and login type: 

Target Login type Support Status
Azure SQL Database SQL Login No  
Azure SQL Database Windows account No  
Azure SQL Managed Instance SQL Login Yes Preview
Azure SQL Managed Instance Windows account Yes Preview
SQL Server on Azure SQL VM SQL Login Yes Preview
SQL Server on Azure SQL VM Windows account No

 

 

We recommend verifying the login migration by logging into the target Azure SQL using one of the logins migrated by entering the same password as it had on the source SQL Server instance. 

 

TDE-enabled database migrations

To enhance the security of your SQL Server database, it is crucial to take necessary precautions such as designing a secure system, encrypting confidential assets, and building a firewall. However, even with these measures, physical media theft like drives or tapes can still compromise your data. 

 

Transparent Data Encryption (TDE) is a SQL Server feature that provides real-time I/O encryption and decryption of data at rest (including data and log files) using a symmetric database encryption key (DEK) secured by a certificate.  

 

When migrating a TDE-protected database, it is essential to remember that the certificate (asymmetric key) used to open the database encryption key (DEK) must also be moved along with the source database. For the instance to access the database files, you must recreate the server certificate in the master database of the target SQL Server. 

 

The TDE-enabled database migration process in the Azure SQL Migration extension 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 reconfiguring TDE for the target database again. 

 

This new capability is now included as an additional step in the Migration wizard: 

carlosrobles_1-1677526175156.png

All you need to do is consent to the Azure SQL Migration extension to use your existing credentials to allow the Migration wizard to back up your DEK certificate temporarily into a network share. 

 

You must ensure the SQL Server service account has write access to the network share path you will use to back up the DEK certificates temporarily. Also, the current user should have administrator privileges on the computer where this network path exists. The certificates will be deleted by the Migration Wizard after  

carlosrobles_2-1677526208491.png

 

Continue the Migration wizard as usual. In Step 3: Azure SQL target of the Migration wizard, select your Azure account, Azure subscription, the Azure region or location, and the managed instance's resource group.  

 

You also have a new option to migrate the TDE certificate to your target: 

carlosrobles_3-1677526208492.png

 

If you select the Migrate certificate option, the Certificates Migration panel will open. The TDE certificates migration progress details are shown on the screen. 

carlosrobles_4-1677526208492.png

 

We recommend verifying the status of the recently migrated database using the following T-SQL query: 

 

 

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

 

 

 

Conclusion

The latest public preview features introduced in the Azure SQL Migration extension for Azure Data Studio make the post-database migration experience more seamless. Assisting with migrating instance-level objects such as SQL and Windows logins, permissions, and server roles and further updating user mapping for recently migrated databases.  

 

You can perform migrations of TDE-enabled databases with an easy-to-use wizard that automates backing up, copying, and reconfiguring database encryption keys for Azure SQL Managed Instance targets.  

 

To learn more about these new features, see the detailed tutorials: 

We look forward to hearing about your experience using the Azure Database Migration extension for Azure Data Studio. Please don't hesitate to contact us with questions or suggestions at the Azure Community forum — Azure Database Migration Service forum. 

 

Resources

Co-Authors
Version history
Last update:
‎Feb 28 2023 01:38 PM
Updated by: