How to create linked servers from SQL managed instance using Entra Authentication (part 1)

Lately, we have seen some customers interested on the options available to use linked servers from Managed Instance and using Entra Authentication (formerly Azure Active Directory). It is certainly possible to create Linked Servers on SQL Managed instance (SQL MI) to connect to other PaaS databases such as other SQL MIs, Azure SQL Databases or Synapse databases using Entra Authentication.

 

At the time of writing, we support 2 specific use cases. Here you will find the official article. These scenarios are the following.

 

• SQL Managed Instance linked server with managed identity Microsoft Entra authentication.
• SQL Managed Instance linked server with pass-through Microsoft Entra authentication.

 

Let’s explore the first option in this article. We will explore the other scenario in a future one.

 

I will use a SQL managed instance named “azuresqlmi2” and an Azure SQL Server named “lacsql”

 

1. Using Managed Identity. Here are the steps on the official documentation.

One of the prerequisites that we will need is to create a managed identity on our source SQL Managed Instance. So, we can go to the SQL MI blade on the Azure portal and activate the system managed identity. You can also perform this step with Azure PowerShell or Azure CLI.

 

 

In my scenario, I will create a linked server to an Azure SQL Database.

 

Another prerequisite is that I will need to allow outbound traffic on my SQL MI’s network security group (NSG). I will use the Azure SQL service tag to simplify this task. You can read more about service tags here.

 

 

Another prerequisite is to ensure that the SQL MI has configured an Entra Admin.

 

First, I created a contained user on the Azure SQL database that points to the managed identity of the SQL MI managed identity. I chose a contained user that makes my DB more portable in case I need to move it to a different server. In my case the user database is named “epdb2”.

 

 

 

 

CREATE USER [azuresqlmi2] FROM EXTERNAL PROVIDER;

 

 

 

 

The next step is to execute the sp_addlinkedserver store procedure to create the linked server. One of the critical arguments for this SP is the “provstr” parameter. This is basically the connection string to our SQL Database. In this case I will add the Database keyword to the connection string to make a direct connection to the Azure SQL database and not to the Azure SQL (logical) server. As we are mostly interested in querying data for a particular database. In my case the DB is named “epdb2”.

 

 

 

 

EXEC master.dbo.sp_addlinkedserver
    server = N'MSILinkedServer',
    @srvproduct = N'',
    Provider = N'MSOLEDBSQL',
    @provstr = N'Server=lacsql.database.windows.net,1433;Database=epdb2;Authentication=ActiveDirectoryMSI;';

 

 

 

 

The name of my linked server is “MSILinkedServer” and it is using the OLEDB provider. Notice how I also specified the Authentication keyword and set its value to "ActiveDirectoryMSI".

 

The next step is to create a mapping of a login on the SQL Managed Instance (source) and the user account I created on my SQL Database (target). We do this by executing the store procedure sp_addlinkedsrvlogin. The documentation states that “Consider optionally using @locallogin = NULL to allow all local logins”.

 

I was interested to explore more on how that option works. I decided to first test this option setting a specific login “lu***@microsoft.com” that existed on my SQL MI which has a membership on the sysadmin server role. “Useself” is a parameter that has to be set to false when using managed identity authentication.

 

 

 

 

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'MSILinkedServer',
    @useself = N'False',
    @locallogin = N'lu***@microsoft.com';       

 

 

 

 

The next thing was to test the linked server. So, I connected to my SQL MI login as the account lu***@microsoft.com and tested the linked server.

 

 

Then I decided to log into the SQL MI using a different account and I tried to use the linked server. I received the following error when using a SQL authentication account named “miadmin”.

 

 

Why did we receive an error?

 

Remember that when we ran the sp_addlinkedsrvlogin stored procedure we did a hard coded mapping of lu***@microsoft.com as the local login. Then I decided to recreate the linked server but this time as the documentation mentions I will set the locallogin parameter to NULL to allow all local logins to use the linked server.

 

 

 

 

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'MSILinkedServer',
    @useself = N'False',
    @locallogin = NULL;

 

 

 

 

I logged into my SQL MI using the SQL authentication account that I used previously and this time the query execution was successful.

 

 

I executed the query below on my Azure SQL Database to identify the session id that was created to run the remote the remote query.

 

 

 

 

SELECT
  s.session_id,
  auth_scheme,
  client_net_address,
  login_time,
  [host_name],
  client_interface_name,
  nt_domain AS [TenantID],
  nt_user_name AS [ApplicationId],
  login_time,
  connection_id
FROM
  sys.dm_exec_connections AS c
  INNER JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE
  program_name = 'Microsoft SQL Server'
AND
  auth_scheme != 'SQL'

 

 

 

 

 

On the next installment of this short series, we will explore more about how to use the pass-through Microsoft Entra authentication.