Managed Identity with Azure Linked Server

I don't know if this is possible or not. But to set it up, you would specify the required connection-string elements in the @provstr attribute. That is, you would set up the linked server something like this:

EXEC sp_addlinkedserver MYSERVER, '', 'MSOLEDBSQL', @datasrc='YourServer.database.windows.net', @provstr="Initial Catalog=YourDB;Authentication..."

What I am uncertain of is whether the Authentication options in the connection string are honoured or if they are discarded.

Also, you must be using a version of SQL Server that supports the MSOLEDBSQL provider, that is SQL 2019 or later.

Hi Babawale Dawodu,

Thank you for posting your query on Microsoft Q&A!

Linked Server Creation: You can indeed set up a linked server from on-premises SQL Server to Azure SQL Database. However, it's crucial to note that while Azure SQL Managed Instance fully supports managed identity authentication, Azure SQL Database itself does not accept managed identity directly for linked server configurations. Typically, you would use SQL authentication for linked servers connecting from on-prem to Azure SQL Database.

1. Alternatives: If you can't use a SQL account, consider using Azure Elastic Query. This allows you to perform cross-database queries between your on-premises SQL Server and Azure SQL Database without needing a traditional linked server setup.
2. Microsoft Entra Authentication: If you're using Azure SQL Managed Instance (not just Azure SQL Database), you can use managed identities for authentication through linked servers. Here's are some details:
   • Ensure that the linked server uses the "Other data source" server type.
   • Use OLE DB driver version 18.2.1 or higher for Microsoft Entra authentication.
   • Keep in mind that you must add the same principal as a login on the remote server, and both instances should belong to the SQL trust group.
   1. Dependencies: Make sure that the proper permissions and roles are assigned. You'll need to configure necessary access from the Azure SQL Database to utilize any identities securely.

So, while creating an Azure SQL linked server with managed identity directly from an on-premises SQL Server is impractical due to the constraints of Azure SQL Database, using Azure SQL Managed Instance or exploring Elastic Query for cross-database queries could be viable alternatives.

Reference Microsoft documents:

• Linked Servers with Azure SQL Managed Instance
• Create a Linked Server
• Elastic Query Overview

I hope this helps, please let us know if you have further queries.

Kindly consider upvoting the comment if the given information helpful.

Thanks!

Kalyani