Set up a linked server from SQL Managed Instance to Azure Synapse SQL

Azure SQL Managed Instance supports setting up a linked server to Azure Synapse SQL using SQL Authentication or Managed Identity. The linked server will grant you access to the Synapse SQL instance, but you may encounter errors when trying to query the data due to missing permissions on the storage account side. In this blog post, we will go through the steps required, providing examples for both SQL Authentication and Managed Identity.

Query Data from Synapse

Before setting up the linked server from SQL Managed Instance, we will test that we can query the data from Synapse first.

For this example, I have a simple csv file with 2 rows that I have uploaded to the storage account.

We will query the data.

 

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://storacc.dfs.core.windows.net/dlsg/UserData.csv',
        DATA_SOURCE = 'storage_data_source',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0'
    ) AS [result]

I have logged in to the instance using Entra ID and SQL Authentication and get 2 different errors.

Entra Authentication

SQL Authentication

In both cases, the login cannot authenticate to the storage account. For it to work with SQL Authentication, we will need to set up a database scoped credential, and an external data source on a user database (Solution 1). For Entra ID, you can follow the same steps or simply apply RBAC permissions to the storage account for the credential used (Solution 2).

Solution 1

As per the documentation, you cannot create the objects required on a Lake database, so we will need a SQL Database if there is not one already.

CREATE DATABASE [BlogDatabase]
GO

The database will also need master key if it does not already exist.

USE [BlogDatabase]
GO

IF EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PASSWORD'

We can now create the database scoped credential. This credential can be set up to use Managed Identity or a SAS token.

Managed Identity

USE [BlogDatabase]
GO

CREATE DATABASE SCOPED CREDENTIAL [storage_access] 
WITH IDENTITY = 'Managed Identity' 
GO

SAS Token

For the SAS token, we only need the following ticked:

• Allowed services - Blob
• Allowed resource types - Object
• Allowed permissions - Read and List

The generated SAS token can then be copied into the next script.

USE [BlogDatabase]
GO

CREATE DATABASE SCOPED CREDENTIAL [storage_access] 
WITH IDENTITY = 'Shared Access Signature',
SECRET = 'SECRET'
GO

We can then create the external data source.

USE [BlogDatabase]
GO

CREATE EXTERNAL DATA SOURCE [storage_data_source] 
WITH (LOCATION = 'abfss://dlsg@storacc.dfs.core.windows.net', CREDENTIAL = [storage_access])
GO

We can then test connecting again, but with an adjusted query. This adds the data source and changes the file location. This must be run against the user database configured.

USE [BlogDatabase]
GO

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'UserData.csv',
        DATA_SOURCE = 'storage_data_source',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0'
    ) AS [result]

Please Note

With this solution, external tables will also need to be in the same format.

Solution 2

Solution 2 is less complex but will only work for Entra ID authentication.

We need to add the RBAC permission Storage Blob Data Reader for the credential authenticated. In my example, I am logged in using my Entra ID user but for the linked server we will set up below, the same permission will be required for the SQL Managed Instance managed identity.

In the next window, you can add multiple members, then Review + assign

In my tests, the permissions took around 5 minutes to take effect. After allowing some time, we can test the connectivity again.

Now that we can query in Synapse, we can set up the linked server in SQL Managed Instance, however, we need to make sure the login being used exists in Synapse SQL. 

To adhere to the least privilege we should use a login with only the permissions required.

SQL Authentication

USE [master]
GO

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'LOGIN') 
CREATE LOGIN [LOGIN] WITH PASSWORD = 'PASSWORD' 

Managed Identity

USE [master]
GO

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'LOGIN') 
CREATE LOGIN [LOGIN] FROM EXTERNAL PROVIDER

For Solution 1, the login also needs permissions on the database.

USE [BlogDatabase]
GO

CREATE USER [LOGIN]
FOR LOGIN [LOGIN]
GO

--Optional to query external tables
ALTER ROLE db_datareader
ADD MEMBER [LOGIN]

We can now set up the linked server.

Create Linked Server

SQL Authentication

The template script needs to be updated with the correct SQL FQDN and the user database name.

EXEC master.dbo.sp_addlinkedserver
     = N'SynapseSQL',
    @srvproduct=N'', 
    =N'MSOLEDBSQL',
    @datasrc=N'SYNAPSE.sql.azuresynapse.net', 
    @catalog=N'BlogDatabase',
    @provstr = N'Authentication=ActiveDirectoryMSI';
GO

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'SynapseSQL',
    @useself = 'FALSE';
GO


EXEC master.dbo.sp_serveroption 
    =N'SynapseSQL', 
    @optname=N'remote proc transaction promotion', 
    @optvalue=N'false';
GO

EXEC master.dbo.sp_serveroption 
    =N'SynapseSQL', 
    @optname=N'rpc', 
    @optvalue=N'true';
GO

EXEC master.dbo.sp_serveroption 
    =N'SynapseSQL',
    @optname=N'rpc out', 
    @optvalue=N'true';
GO

Managed Identity

The SQL FQDN will need to be changed as well as the database if using Solution 1.

EXEC master.dbo.sp_addlinkedserver
     = N'SynapseSQL',
    @srvproduct=N'', 
    =N'MSOLEDBSQL',
    @datasrc=N'SYNAPSE.sql.azuresynapse.net', 
    @catalog=N'master';
GO

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname=N'SynapseSQL', 
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'LOGIN',
    @rmtpassword='PASSWORD';
GO


EXEC master.dbo.sp_serveroption 
    =N'SynapseSQL', 
    @optname=N'remote proc transaction promotion', 
    @optvalue=N'false';
GO

EXEC master.dbo.sp_serveroption 
    =N'SynapseSQL', 
    @optname=N'rpc', 
    @optvalue=N'true';
GO

EXEC master.dbo.sp_serveroption 
    =N'SynapseSQL',
    @optname=N'rpc out', 
    @optvalue=N'true';
GO

 

We can test the connection to ensure this is working correctly.

Query from SQL Managed Instance

We can now test to see if the same query works from the SQL Managed Instance side.

Using Solution 1

SET QUOTED_IDENTIFIER OFF 

EXEC("
	SELECT
		TOP 100 *
	FROM
		OPENROWSET(
			BULK 'UserData.csv',
			DATA_SOURCE = 'storage_data_source',
			FORMAT = 'CSV',
			PARSER_VERSION = '2.0'
		) AS [result]
") AT SynapseSQL;

Using Solution 2

SET QUOTED_IDENTIFIER OFF 

EXEC("
	SELECT
		TOP 100 *
	FROM
		OPENROWSET(
			BULK 'https://storacc.dfs.core.windows.net/dlsg/UserData.csv',
			FORMAT = 'CSV',
			PARSER_VERSION = '2.0'
		) AS [result]
") AT SynapseSQL;

 

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.