How to create a SQL MI Linked Server using TSQL Scripts

by

Linked servers enable Azure SQL Managed Instance (SQL MI) to seamlessly execute distributed queries and commands against remote database servers, such as other SQL MIs, Azure SQL Databases, or on-premises SQL Server instances.

I’m discussing the creation of a linked server from one SQL Server to another. This blog post doesn’t cover creating a linked server from SQL Server to other SQL providers like PostgreSQL, Oracle DB, and others.

Below are the two primary deployment scripts for SQL MI linked servers, categorized by their authentication methods and use cases.

SQL Authentication Linked Server

This method explicitly maps a local SQL login to a remote user and password on the destination server. It is typically used for hybrid architectures where you need to connect your Azure SQL MI back to a legacy on-premises SQL Server over a VPN or ExpressRoute. It is also the fallback method when cross-tenant Microsoft Entra ID (formerly Azure Active Directory) authentication is not possible.

-- ======================================================================================
-- PARAMETER DECLARATIONS (Configure these values before execution)
-- ======================================================================================
DECLARE @LinkedServerName   NVARCHAR(128)  = N'linkedservername';
DECLARE @TargetDataSrc      NVARCHAR(4000) = N'ServerName';
DECLARE @TargetCatalog      NVARCHAR(128)  = N'DatabaseName';
DECLARE @RemoteUser         NVARCHAR(128)  = N'YourSQLLoginUsername';
DECLARE @RemotePassword     NVARCHAR(128)  = N'YourSecureP@ssw0rd!'; 
DECLARE @ProviderString     NVARCHAR(4000);

-- Define the Provider String using MSOLEDBSQL
-- Encrypt=Mandatory requires the target to present a valid CA-signed certificate. 
SET @ProviderString = N'Encrypt=True;TrustServerCertificate=False;';

BEGIN TRY
    -- 1. Create Linked Server definition
    EXEC master.dbo.sp_addlinkedserver 
        @server     = @LinkedServerName, 
        @srvproduct = N'',                  
        @provider   = N'MSOLEDBSQL',        
        @datasrc    = @TargetDataSrc,
        @catalog    = @TargetCatalog,
        @provstr    = @ProviderString;

    -- 2. Configure Login Mapping
    EXEC master.dbo.sp_addlinkedsrvlogin 
        @rmtsrvname  = @LinkedServerName, 
        @useself     = N'False',            
        @locallogin  = NULL,                
        @rmtuser     = @RemoteUser, 
        @rmtpassword = @RemotePassword;

    -- 3. Enable RPC Out for executing remote stored procedures
    EXEC master.dbo.sp_serveroption @server = @LinkedServerName, @optname = N'rpc out', @optvalue = N'true';

    PRINT 'Linked Server Created Successfully.';
    EXEC master.dbo.sp_testlinkedserver @servername = @LinkedServerName;

END TRY
BEGIN CATCH
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;
GO

Once the linked server is created, there are plenty of linked server options that we can enable or disable. In the above script we enabled rpc out parameter to True to allow query execution on remote SQL servers.

Server Options (sp_serveroption):

Once a linked server is successfully established, its operational behavior is strictly governed by server options configured via the sp_serveroption system stored procedure. Check out the below table to understand when to use each option.

Server Option ParameterData TypeDefault StateWhen to Use and When NOT to Use
collation compatibleBoolean (true/false)falseUSE: Set to true only if both SQL instances share identical collations to allow the optimizer to push string comparisons to the remote server.

DO NOT USE: Leave false if collations differ, otherwise you risk silent data truncation or sorting errors.
data accessBoolean (true/false)falseUSE: Keep true for standard operations.
DO NOT USE: Set to false if you need to temporarily disable access during remote maintenance windows without destroying the server definition.
rpcBoolean (true/false)falseUSE: Rarely enabled.
DO NOT USE: Generally, keep disabled for stringent security reasons, as it controls inbound remote execution permissions from the remote server.
rpc outBoolean (true/false)falseUSE: Must be explicitly set to true to execute remote stored procedures or utilize the EXECUTE (‘SQL’) AT [RemoteServer] syntax.
DO NOT USE: Leave false if the linked server is strictly for simple data reads.
lazy schema validationBoolean (true/false)falseUSE: Enable (true) in highly volatile remote environments or over high-latency WAN links to save the overhead of pre-execution metadata calls.
DO NOT USE: Leave false on low-latency LANs where immediate schema validation prevents runtime query failures.
remote proc transaction promotionBoolean (true/false)trueUSE: Keep true if you require two-phase commits across servers.
DO NOT USE: Set to false to prevent MSDTC distributed transaction overhead for simple local queries. If MSDTC is not configured, this must be false or queries will fail

Managed Identity (Entra ID) Linked Server


This is the modern, highly secure approach for connecting your SQL MI to another Azure-hosted SQL instance (like another SQL MI or Azure SQL Database). It utilizes the source Managed Instance’s system-assigned managed identity to authenticate, completely eliminating the need to embed or manage passwords within your scripts.

The target server must have a login expressly mapped to the source MI’s identity from an external provider.

-- ======================================================================================
-- PARAMETER DECLARATIONS (Configure these values before execution)
-- ======================================================================================
DECLARE @LinkedServerName NVARCHAR(128)  = N'REMOTEMI_MSI_LINK';
DECLARE @TargetDataSrc    NVARCHAR(4000) = N'targetmi.database.windows.net';
DECLARE @TargetCatalog    NVARCHAR(128)  = N'FinanceDB';
DECLARE @ProviderString   NVARCHAR(4000);

-- Instruct the driver to request a managed identity token from Azure
SET @ProviderString = N'Authentication=ActiveDirectoryMSI;Encrypt=Mandatory;';

BEGIN TRY
    -- 1. Create Linked Server definition
    EXEC master.dbo.sp_addlinkedserver 
        @server     = @LinkedServerName, 
        @srvproduct = N'', 
        @provider   = N'MSOLEDBSQL', 
        @datasrc    = @TargetDataSrc,
        @catalog    = @TargetCatalog,
        @provstr    = @ProviderString;

    -- 2. Configure Login Mapping (Credentials are passed as NULL for Managed Identity)
    EXEC master.dbo.sp_addlinkedsrvlogin 
        @rmtsrvname  = @LinkedServerName, 
        @useself     = N'False', 
        @locallogin  = NULL, 
        @rmtuser     = NULL, 
        @rmtpassword = NULL;

    -- 3. Enable RPC Out for executing remote stored procedures
    EXEC master.dbo.sp_serveroption @server = @LinkedServerName, @optname = N'rpc out', @optvalue = N'true';
    
    PRINT 'Managed Identity Linked Server Created Successfully.';
    EXEC master.dbo.sp_testlinkedserver @servername = @LinkedServerName;

END TRY
BEGIN CATCH
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;
GO

Please comment below if you have any questions on Linked Server creation process.

For more technical blog posts, visit CloudNerchuko.in

Leave a Comment