"Invalid Managed Identity" error in vulnerability assessments for SQL Managed Instance
On a recent case, a customer received the error "Invalid Managed Identity" when trying to scan a database. The error suggests that there is an issue with the managed identity or the RBAC permissions required, but the same error occurs when there is a connectivity issue between SQL Managed Instance and the storage account selected. Checking RBAC permissions The RBAC permissions can be manually checked in Azure Portal, or you can use the below script in Azure CLI, providing the resource details. #SQL Managed Instance Details $serverResourceGroup = '' $serverName = '' #Storage Account Details $storageAccountResourceGroup = '' $storageAccount = '' ############################################################################################################################## $sqlIdentity = ((az sql mi show -g $serverResourceGroup -n $serverName | ConvertFrom-Json).Identity).principalId $storageId = (az storage account show -g $storageAccountResourceGroup -n $storageAccount | ConvertFrom-Json).id $permissions = $NULL $permissions = az role assignment list --all --assignee $sqlIdentity | ConvertFrom-Json | Where-Object {$_.scope -eq $storageId -and $_.roleDefinitionName -eq 'Storage Blob Data Contributor'} if ($permissions -eq $NULL) {Write-Host "RBAC permissions do not exist"} else {Write-Host "RBAC Permissions exist"} It will return a simple message to confirm if the permissions exist. Connectivity issues If the permissions do exist, then it may be due to connectivity issues between SQL Managed Instance and the storage account. Listed below are ways to check this. Storage account networking configuration The storage account can be configured to allow the following access: Public - All Public - Selected networks Private If the access is set to Selected Networks, make sure the SQL Managed Instance subnet is in the list. If the access is private only, then the SQL Managed Instance would need to be able to resolve the Private IP in DNS. NSG/Firewall rules and routing Check that there are no rules blocking connections between each resource and that the routing is configured correctly. DNS settings If the DNS settings are custom or a private endpoint is being used, the DNS configuration for the SQL Managed Instance virtual network may need to be configured, for example, adding a private DNS zone. Network Peering If the connectivity is through a private endpoint in a different virtual network, check that there is communication between them, such as network peering. Disclaimer Please note that products and options presented in this article are subject to change. This article reflects the documentation in January 2026. I hope this article was helpful for you, please feel free to share your feedback in the comments section.Setting up SQL Managed Instance Link to an Availability Group (Updated September 2025)
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 there are some differences required to ensure a failover does not break the connection. Please note that Managed Instance Link can only be created with an availability group with a single database. 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 the AG nodes Enabling trace flags on the AG nodes (Optional) Testing network connectivity between SQL Managed Instance and the AG Create certificates on the AG nodes Import AG certificate public keys to SQL Managed Instance Import the certificate public key of the SQL Managed Instance to AG nodes Import Azure-trusted root certificate authority keys to AG nodes Alter the mirroring endpoint on AG nodes Create a distributed availability group on AG Set up the managed Instance Link Create a database master key Firstly, a master encryption key is required on all nodes of the AG if it does not already exist. The script below will not return any rows if it does not exist. -- Run on SQL Server USE master; GO SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%'; If no rows are returned, the following query can be used. Keep a 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 AG nodes (Optional) To optimise the performance of the link, we recommend enabling the trace flags T1800 and T9567 on all nodes. Instructions on this are here and will require a restart of the service. Testing network connectivity between SQL Managed Instance and the AG Instructions on how to run the connectivity tests can be found here, and can be done by SSMS GUI or T-SQL. For these tests, the SQL Managed Instance will need to be able to connect to the Mirror endpoint using the IP of the availability group. The SQL Managed Instance IPs can can change (for example during maintenance). For that reason NSG and firewall rules should allow access to ports 5022 and 11000-11999 for the whole subnet range of the SQL Managed Instance. Create certificates on AG nodes The below query will need to be run on all AG nodes. Pay particular attention to the @cert_expiry_date variable to ensure it is a date in the future. It will need to be replaced after expiry. -- 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 SQL Managed Instance The public keys of the AG certificates need to be imported into the SQL Managed Instance. The below query will return the data required for the next step. -- 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; Example result The next step will need to be run in PowerShell and can be run through Azure Cloud Shell. Below is an example script to log in to Azure. # 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 Replace the values of $CertificateName, $PublicKeyEncoded and $ManagedInstanceName and then run this. # 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 node values. Import the certificate public key of SQL Managed Instance to AG nodes Using the same PowerShell session, run this script to get the public key of the 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</managedinstancename> On all nodes of the AG run the following query, replacing the certificate name and the value of the PublicKey with the output from the previous step. -- Run on SQL Server USE MASTER CREATE CERTIFICATE [<managedinstancefqdn>] FROM BINARY = <publickey></publickey></managedinstancefqdn> Import Azure-trusted root certificate authority keys to AG nodes Use the steps here to import the Microsoft PKI root-authority certificate and DigiCert PKI root-authority certificate onto all nodes of the AG. Alter the mirroring endpoint on AG nodes The mirroring endpoint on the AG nodes will also need to be updated to allow the Managed Instance to authenticate with the 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. Change CREATE ENDPOINT to ALTER ENDPOINT and add this command after AUTHENTICATION = WINDOWS NEGOTIATE CERTIFICATE, changing the cert_name value. CERTIFICATE [cert_name] Once updated, execute the query. For multiple Availability groups on the same instance, the next section would need to be repeated for each one. Create a distributed availability group on the AG (running against the primary server) Use this script to set up the distributed availability group, updating the sections in angle brackets (<>). The SQLServerIP will need to be the IP of the listener of the availability group. -- 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</managedinstancename></managedinstancefqdn></agnameonsqlmi></endpointport></sqlserverip></agnameonsqlserver></dagname> Set up the managed Instance Link The final step is to set up the link, the script for this is here. Please ensure that the $SQLServerIP is the IP of the AG listener and not the IP of the primary node. Once this is executed, the MI Link should be up and running and failing over to another node on the AG will not stop 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 September 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.
How to Avoid Transaction Isolation Level Issues on Azure SQL Managed Instance
Learn how to set and maintain the transaction isolation level for your distributed transactions on Azure SQL Managed Instance, and how connection reset can affect the isolation level and cause unexpected behavior in your application.Restore database across servers(Azure SQL Database and Azure SQL managed instance)- Azure Automation
In this article, we consider the scenario where customers would like to restore their Azure SQL database and managed database from one Azure SQL server to another Azure SQL server, for example for development reasons. The article will provide you with the steps to achieve this by automating the job using Azure AutomationLearning from Expertise#10: Why no restore point available?!
Introduction In today's blog article, we will try to address and clarify some points on how Azure SQL DB and Managed Instance Point in Time Restore (PiTR) works, especially when it comes to failover group and Geo-replication (Azure SQL DB), and we are going to provide answers to common queries. Use case: On some occasions, after the failover is initiated, the current Primary DR will start a new backup chain from that point and old backups are available on the current secondary DR. If we want to restore the backups which exists in Secondary it will not allow us to perform, apparently restore cannot be initiated on the Primary as the backup is not available. Also, sometimes we observe in secondary DR for few databases, PitR restore point is available and for few databases it shows “no restore point available” Common Questions. Do we expect to lose the PITR ability after failover? Why are the backups still listed and available on the new secondary if they are not usable? Why do some databases show the restore point and why for few databases it shows “no restore point available”? Here is the scenario: Day 1 - > Geo-Replication/ Failover group setup, Primary in North Europe and Secondary in West Europe, PITR backup retention is set to 7 days. Day 2 -> Customer initiated failover, now Primary is in West Europe and secondary is in North Europe Day 4 -> Customer would like to restore the database to the state of Day1 which is possible as 7 days retention was set to PITR backups, and Day1 is 3 days back from current point in time As we are running backups only for the Primary DR, the new Primary in West Europe does not have the requested PITR backup (Day 1) The needed backup is “visible” on the backups listed for current secondary in North Europe The customer cannot restore to requested PITR as The restore cannot be initiated on the secondary as this is not possible for Secondary DR. The restore cannot be initiated on the primary as the backup is not there. Also, sometimes you will be seeing available PITR backups on Failover secondary server databases and for some databases it shows as “No restore point available” For the databases where the restore point is available, they can’t be able to restore it. The restore cannot be initiated on the secondary as this is not possible for Secondary DR. The restore cannot be initiated on the primary as the backup is not there. Clarifications In case the database was recently created, the first full backup might be still in the queue, so it might be a promising idea that you wait for some time before checking again. Failover groups is a disaster recovery feature that allows our customers to recover multiple related databases in a secondary region, after a catastrophic failure or other unplanned event that results in full or partial loss of service’s availability in the primary region. Database backups are an essential part of any business continuity and disaster recovery strategy because they protect your data from accidental corruption or deletion. It is important for us to understand that we are not losing any backups, in case the current region was not the primary at the desired point in time where your restoring point is holding, we will need to failover to the other region and make it as Primary, then you should be able to restore it. Backups are only taken on the Primary server, there can be several justifications why we can see PitR on some DBs and not for others on the Secondary Server. For the Databases that have PitR it is most probably because there was a failover that caused the Primary to become Secondary, as we have already had backups taken on the old primary which is secondary now. The DBs that do not have PitR They could have been provisioned after the last failover happened, so the backups are only available on the primary server Or they were added to the failover group after the last failover that will also cause the backups to be taken on the primary It should be fair to say that we will likely “fail back” once availability is restored in the original primary region because we might have picked that region for a reason. Note: - In case no geo-failover happened nor the database was recently created, and you still see “no restore point available,” please contact Microsoft support. Alternatives: you can perform Export operation on the secondary and import it on Primary DR. You can leverage geo redundant backup and Geo-restore, taking into consideration that Geo-restore is the most basic disaster-recovery solution available in SQL Database and SQL MI. It relies on automatically created geo-replicated backups with a recovery point objective (RPO) up to 1 hour and an estimated recovery time of up to 12 hours. Note: Azure SQL Managed Instance does not currently support exporting a database to a BACPAC file using the Azure portal or Azure PowerShell. To export a managed instance into a BACPAC file, use SQL Server Management Studio (SSMS) or SQLPackage. References: Restore a database from a backup - Azure SQL Database & SQL Managed Instance | Microsoft Docs Automatic, geo-redundant backups - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs Azure SQL Database Point in Time Restore | Azure Blog and Updates | Microsoft Azure Azure SQL Database Geo-Restore | Azure Blog and Updates | Microsoft Azure Disclaimer Please note that the products and options presented in this article are subject to change. This article reflects the database backup options available for Azure SQL database and Azure SQL managed instance in June 2022. Closing remarks We hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below. Raviteja Devarakonda (Author) Ahmed Mahmoud (Co-Author)Primary Key Violation in Bidirectional Replication between SQL Managed Instance and SQL server on VM
The steps to create a Bi-directional replication is simple, and similar to the steps for configuring transnational replication with extra step to enable the @loopback_detection parameter of sp_addsubscription to ensure that changes are only sent to the Subscriber and do not result in the change being sent back to the Publisher.
