DMS - Support for Managed Identity for Azure SQL Managed Instance migration
Azure Database Migration Service (DMS) has introduced a new feature that supports the use of Managed Identity for migrating to Azure SQL Managed Instance. This enhancement simplifies the migration process and ensures secure and seamless integration with Azure Database Migration services. In this blog post, we will dive into the prerequisites, permissions or role(s) required, and how to use this associated Managed identity for migrating to Azure SQL Managed Instance. Currently, this feature is supported through Azure Portal, PowerShell, and Az cmdlets. Prerequisites Before you begin the migration to Azure SQL Managed Instance using Managed identity, ensure that following prerequisites are in place: 1. The Target Azure SQL Managed Instance's associated Managed Identity: Azure Database Migration Service only supports Managed Identity that is associated with the target Azure SQL Managed Instance. How to identify the associated Managed Identity? Once you start the migration to Azure SQL Managed Instance using Azure Database Migration Service and on second page, select the target Azure SQL Managed instance, its associated Managed Identity will be displayed if "Use Managed Identity" is selected (default), as highlighted below. Alternatively, you can follow these steps: a) Go to the target Azure SQL Managed Instance's home page. b) On the left menu, under Security > Identity: If User-assigned Managed Identity is present, the associated Managed Identity will be same as selected under the Primary Identity. If there is no User-assigned Managed Identity and only System-assigned Managed Identity is enabled, the associated Managed Identity will be System-assigned Managed Identity and have the same name as the Azure SQL Managed Instance's name. For example, for ABCSQLMI - Azure SQL Managed Instance the System-assigned Managed Identity will be "ABCSQLMI". 2) Permissions: Assign the "Storage Blob Data Reader" role on the storage account to the target instance's associated Managed Identity. Steps to Assign Permissions In the Azure portal, go to the storage account that will be used in migration for keeping the backup files. On the left menu under Access Control (IAM), click on "+Add" > Add role assignment Select or search for builtin role "Storage Blob Data Reader", click Next. Assing this role access to Managed Identity by selecting the associated Managed Identity identified in the previous step as the member. Note: Ensure that the storage account has the "Allow storage account key access" enabled. How to use associated Managed identity for migration? Upon initiating the migration to Azure SQL Managed Instance using Azure Database Migration Service, navigate to the second page and select the target Azure SQL Managed Instance. If the "Use Managed Identity" option is selected (default), the associated Managed Identity will be displayed and used for the migration (as shown in the first image above). Once Managed identity is used for the migration, DMS will utilize this Managed identity for reading the backup files on the Azure blob storage and thus removing the need for SAS keys. Limitations: Azure Database Migration Service supports Managed Identity that is associated with the target Azure SQL Managed Instance only. It can be either User assigned, or System assigned Managed identity. Currently, this feature is supported through Azure Portal only. Ensure that the storage account has the "Allow storage account key access" enabled. Benefits of using Managed Identity: Using Managed Identity for Azure SQL Managed Instance migrations offers several security benefits: Enhanced Security: Managed identities eliminate the need to use SAS key, reducing the risk of SAS key token exposure. Simplified Management: As associated Managed Identity of the target Azure SQL MI is used, it allows for seamless integration with Azure Database Migration services, making it easier to manage access permissions and roles. Improved Efficiency: The streamlined authentication process speeds up migrations and reduces the complexity of managing SAS keys. Improved Compliance: By using Managed Identity, user can ensure that they adhere to security best practices and compliance requirements, as it is managed securely by Azure. All the above benefits make Managed Identity better than SAS key token. Learn more. Conclusion The new feature supporting Managed Identity in Azure Database Migration Service for Azure SQL Managed Instance migrations offers a secure and efficient way to manage permissions during the migration process. By following the steps outlined above and leveraging the security benefits of Managed Identity, you can ensure a smooth and secure migration to Azure SQL Managed Instance.
Setting up SQL Managed Instance Link to an Availability Group
On a recent case, a customer was trying to set up SQL Managed Instance Link to partner with an on premise Always On Availability Group (AG). Using the public documentation will work, but this will only be active against the primary node and a failover will cause the database on the managed instance side to stop syncing. This post will be using steps from the following documents. Prepare your environment for a link - Azure SQL Managed Instance Configure link with scripts - Azure SQL Managed Instance The steps to set this up are below Create a database master key on your AG nodes Enabling trace flags on your AG nodes (Optional) Testing network connectivity between your SQL Managed Instance and the AG Create certificates on your AG nodes Import AG certificate public keys to your SQL Managed Instance Import the certificate public key of your SQL Managed Instance to your AG nodes Import Azure-trusted root certificate authority keys to your AG nodes Alter the mirroring endpoint on your AG nodes Create a distributed availability group on your AG Set up the managed Instance Link Create a database master key You first need to create a master encryption key on all nodes of your AG if it does not already exist. You can check if it exists by running this query. -- Run on SQL Server USE master; GO SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%'; If no results are returned, run this query, keep note of the passwords in a confidential and secure place. -- Run on SQL Server -- Create a master key USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong_password>'; Enabling trace flags on your AG nodes (Optional) To optimise the performance of your link, we recommend enabling the trace flags T1800 and T9567 on all of your nodes. Instructions on this are here and will require a restart of the service. Testing network connectivity between your SQL Managed Instance and the AG Instructions on how to run the connectivity tests can be found here, and can be done by SSMS or T-SQL. For these tests, your SQL Managed Instance will need to be able to connect to your Mirror endpoint using the IP of your availability group. All nodes of your AG will need to be able to connect to ports 5022 and 11000-11999 on the subnet of your SQL Managed Instance. Create certificates on your AG nodes On each node, run the query below to create a certificate. You can alter your @cert_expiry_date variable to a date that suits you. -- Create the SQL Server certificate for the instance link USE MASTER -- Customize SQL Server certificate expiration date by adjusting the date below DECLARE @cert_expiry_date AS varchar(max)='03/30/2025' -- Build the query to generate the certificate DECLARE @sqlserver_certificate_name NVARCHAR(MAX) = N'Cert_' + @@servername + N'_endpoint' DECLARE @sqlserver_certificate_subject NVARCHAR(MAX) = N'Certificate for ' + @sqlserver_certificate_name DECLARE @create_sqlserver_certificate_command NVARCHAR(MAX) = N'CREATE CERTIFICATE [' + @sqlserver_certificate_name + '] ' + char (13) + ' WITH SUBJECT = ''' + @sqlserver_certificate_subject + ''',' + char (13) + ' EXPIRY_DATE = '''+ @cert_expiry_date + ''''+ char (13) IF NOT EXISTS (SELECT name from sys.certificates WHERE name = @sqlserver_certificate_name) BEGIN PRINT (@create_sqlserver_certificate_command) -- Execute the query to create SQL Server certificate for the instance link EXEC sp_executesql @stmt = @create_sqlserver_certificate_command END ELSE PRINT 'Certificate ' + @sqlserver_certificate_name + ' already exists.' GO Import AG certificate public keys to your SQL Managed Instance The public keys of your AG certificates need to be imported into your SQL Managed Instance. Run this query on each node to get the data required -- Run on SQL Server -- Show the name and the public key of generated SQL Server certificate USE MASTER GO DECLARE @sqlserver_certificate_name NVARCHAR(MAX) = N'Cert_' + @@servername + N'_endpoint' DECLARE @PUBLICKEYENC VARBINARY(MAX) = CERTENCODED(CERT_ID(@sqlserver_certificate_name)); SELECT @sqlserver_certificate_name as 'SQLServerCertName' SELECT @PUBLICKEYENC AS SQLServerPublicKey; You will get a result similar to below You can now import these into your SQL Managed Instance using Azure Cloud Shell. Log in using this script, replacing . # Run in Azure Cloud Shell (select PowerShell console) # Enter your Azure subscription ID $SubscriptionID = "<SubscriptionID>" # Login to Azure and select subscription ID if ((Get-AzContext ) -eq $null) { echo "Logging to Azure subscription" Login-AzAccount } Select-AzSubscription -SubscriptionName $SubscriptionID Then run this script, replacing the values of $CertificateName, $PublicKeyEncoded and $ManagedInstanceName. # Run in Azure Cloud Shell (select PowerShell console) # =============================================================================== # POWERSHELL SCRIPT TO IMPORT SQL SERVER PUBLIC CERTIFICATE TO SQL MANAGED INSTANCE # ===== Enter user variables here ==== # Enter the name for the server SQLServerCertName certificate – for example, "Cert_sqlserver1_endpoint" $CertificateName = "<SQLServerCertName>" # Insert the certificate public key blob that you got from SQL Server – for example, "0x1234567..." $PublicKeyEncoded = "<SQLServerPublicKey>" # Enter your managed instance short name – for example, "sqlmi" $ManagedInstanceName = "<ManagedInstanceName>" # ==== Do not customize the below cmdlets==== # Find out the resource group name $ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName # Upload the public key of the authentication certificate from SQL Server to Azure. New-AzSqlInstanceServerTrustCertificate -ResourceGroupName $ResourceGroup -InstanceName $ManagedInstanceName -Name $CertificateName -PublicKey $PublicKeyEncoded Repeat this step for all AG nodes. Import the certificate public key of your SQL Managed Instance to your AG nodes Using the same Azure Cloud Shell, run this script to get the public key of your SQL Managed Instance certificate, replacing the value of the variable $ManagedInstanceName. # Run in Azure Cloud Shell (select PowerShell console) # =============================================================================== # POWERSHELL SCRIPT TO EXPORT MANAGED INSTANCE PUBLIC CERTIFICATE # ===== Enter user variables here ==== # Enter your managed instance short name – for example, "sqlmi" $ManagedInstanceName = "<ManagedInstanceName>" # ==== Do not customize the following cmdlet ==== # Find out the resource group name $ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName # Fetch the public key of the authentication certificate from Managed Instance. Outputs a binary key in the property PublicKey. Get-AzSqlInstanceEndpointCertificate -ResourceGroupName $ResourceGroup -InstanceName $ManagedInstanceName -EndpointType "DATABASE_MIRRORING" | out-string On all nodes of your AG you can then run this query, replacing with the value of the PublicKey output in the previous step. -- Run on SQL Server USE MASTER CREATE CERTIFICATE [<ManagedInstanceFQDN>] FROM BINARY = <PublicKey> Import Azure-trusted root certificate authority keys to your AG nodes Use the steps here to import the Microsoft PKI root-authority certificate and DigiCert PKI root-authority certificate onto all nodes of your AG. Alter the mirroring endpoint on your AG nodes The mirroring endpoint on your AG nodes will also need to be updated to allow your Managed Instance to authenticate with your AG using the newly created certificate. This can be done by navigating to Server Objects > Endpoints > Database Mirroring. Right click on the endpoint and select Script Endpoint as > CREATE To > New Query Editor Window. You will need to add this command under AUTHENTICATION, changing the cert_name value and change the CREATE ENDPOINT to ALTER ENDPOINT. CERTIFICATE [cert_name] Once updated, execute the query. Create a distributed availability group on your AG Use this script to set up the distributed availability group, updating the values - Your choice of name - AG Name already created : - The IP of your AG listener and the endpoint listener port - Your choice of name - FQDN of your instance - Just the instance name -- Run on SQL Server -- Create a distributed availability group for the availability group and database -- ManagedInstanceName example: 'sqlmi1' -- ManagedInstanceFQDN example: 'sqlmi1.73d19f36a420a.database.windows.net' USE MASTER CREATE AVAILABILITY GROUP [<DAGName>] WITH (DISTRIBUTED) AVAILABILITY GROUP ON N'<AGNameOnSQLServer>' WITH ( LISTENER_URL = 'TCP://<SQLServerIP>:<EndpointPort>', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC, SESSION_TIMEOUT = 20 ), N'<AGNameOnSQLMI>' WITH ( LISTENER_URL = 'tcp://<ManagedInstanceFQDN>:5022;Server=[<ManagedInstanceName>]', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC ); GO Set up the managed Instance Link The final step is to set up the link, you can do this by running the script here. Please ensure that the $SQLServerIP is the IP of your AG listener and not the IP of the primary node. The in the link includes updated parameters from Az.Sql 6.0.0. This version is not currently available in Azure Cloud Shell. If you are using Azure Cloud Shell, replace the final section of the command... ...with the below New-AzSqlInstanceLink -InstanceName $ManagedInstanceName -Name $DAGName -PrimaryAvailabilityGroupName $AGNameOnSQLServer -ResourceGroupName $ResourceGroup -SecondaryAvailabilityGroupName $AGNameOnSQLMI -SourceEndpoint $SourceIP -TargetDatabase $DatabaseName Once this is executed, your MI Link should be up and running and failing over to another node on your AG will not stop your MI Link from syncing. Disclaimer Please note that products and options presented in this article are subject to change. This article reflects MI Link and the documentation in January 2025. I hope this article was helpful for you, please feel free to share your feedback in the comments section.Release: Azure SQL Migration extension for Azure Data Studio v1.5.6
We're delighted to announce the release of the latest version of the Azure SQL Migration extension for Azure Data Studio, v1.5.6. This release provides you with Azure Database Migration Service’s new features like: 1) Support for Next-gen General Purpose service tier for Azure SQL Managed Instance. 2) Target Provisioning based upon SKU recommendation (using ARM templates) - Public Preview. 3) Enhanced login migration experience - Public Preview. What is new in Azure SQL Migration extension v1.5.6? 1) Support for Next-gen General Purpose service tier for Azure SQL Managed Instance: The Next-gen General Purpose service tier is an architectural upgrade to the existing General Purpose service tier that can be used for new and existing instances. Now, the Azure Data Studio extension for Azure Database migration service – Azure SQL Migration support Next-gen General Purpose as SKU recommendation for Azure SQL Managed Instance. For details, refer here. This service tier provides better performance, throughput, greater storage capacity and support more than 100 databases on a single instance. 2) Target Provisioning based upon SKU recommendation (using ARM templates) - Public Preview: With Azure SQL Migration v1.5.6, now users can generate ARM templates directly based upon the SKU recommendation generated using performance data collected from the source. User can use these ARM templates for all the Azure SQL offerings – Azure SQL VM, Azure SQL MI and Azure SQL DB and easily create the Azure SQL Target for the migrations. To create and deploy the Azure SQL Target, users have two options: a) Copy or save the ARM template in JSON and use Azure CLI, PowerShell and other deployment operations. b) Using Deploy-to-Azure button, then provide the Azure blob storage account details to store the template and deploying it though the Azure Portal. This feature is in Public Preview and will help you to streamline the Azure SQL target creation using ARM templates, can automate deployments and use the practice of infrastructure as code, deploy them quickly and CI/CD integration. 3) Enhanced login migration experience - Public Preview: After completing the data migration, the next critical step is to setup the authentication and authorization for the databases and thus login migration becomes the critical step in the migration journey. Azure SQL Migration extension supports Login migration (Public Preview) and now we have enhanced its experience by adding Pre-requisites validation checks to ensure all the requirements are in place for successful login migrations. Currently, only Azure SQL Managed Instance and SQL Server on Azure Virtual Machines targets are supported. Resources For more information about the extension and Azure Database Migration Service, see the following resources. Azure Database Migration Service documentation Migrate databases using the Azure SQL Migration extension One-click SQL Migration PoC environment Architecture of Azure Database Migration Service | Microsoft Community HubSQL MI Restore with valid SAS key failing with Operating System Error 86
We will provide details about a workaround for resolving the "Operating System Error 86" encountered during database restoration from Azure Storage on SQL Managed Instance. In this scenario, the Storage Account was not behind firewall and the credential already had a valid SAS token.
