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.
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|
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.
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:
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.
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.
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:
|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.
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:
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
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:
If you select the Migrate certificate option, the Certificates Migration panel will open. The TDE certificates migration progress details are shown on the screen.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.