Setting up a linked server on a Managed SQL Instance

Question

On an Azure Managed SQL Instance that has been setup, I'm trying to setup some linked server connections.

When I access these same databases through Management Studio while logged into this MI SQL Instance, I can access the databases I am wanting to connect to without any issues.

However, when I create a linked server connection, it does not provide the databases on these other SQL instances that I can access through management studio.

The port for the databases when accessing through MS, is 1433.

I've been able to successfully create these Linked Server connections on a DEV SQL instance. However, the DEV SQL Instance is not a managed instance. It's an Azure VM Server with SQL Server installed.

My question is, do I need to do anything special to the SQL MI to allow these DBs that I'm wanting to set up?

I've attempted to use the default ports (1433), with no success. This is the port that is used to connect to the DBs while using management studio.

It does create the Linked Server connection, but does not provide the DBs and tables on that DB I'm attempting to reach out to.

Since it is sitting in Azure, for a MI, do I need to do anything special in the Azure Portal or on the server for the Managed Instance?

The DB I am attempting to connect to is SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

Any help would be greatly appreciated.

Is there anything special that needs to be done since the server I'm attempting to set this up on is a managed SQL Instance?

I am getting an error that states: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server." I can access this same instance through management studio on this managed SQL instance no problem. But I cannot create a linked server connection.

Here is what I've used to try and create the Linked Server connection:

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServer_Name', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'IPAddress,1433', @catalog=N'DB_Name'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServer_Name',@useself=N'False',@locallogin=NULL,@rmtuser=N'user_name',@rmtpassword='password'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'collation compatible', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'rpc', @optvalue=N'true'
GO

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

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'LinkedServer_Name', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Answer 1

Managed Instances supports linked servers unless they utilize MSDTC for some distributed writes.

The Microsoft Distributed Transaction Coordinator (MSDTC) service monitors all aspects of the transaction process, even across different resource managers on several machines. This helps guarantee that the transaction is committed if all parts of it succeed, or that it is rolled back if any element of the transaction process fails.

When a user registers on to the local server and runs a distributed query that references a table on the connected server, the local server must log on to the linked server on the user's behalf to access that table. To specify the login credentials that the local server will use to connect on to the linked server, call sp_addlinkedsrvlogin.

Make sure you have remote Logins:

EXEC master.dbo.sp_addlinkedsrvlogin   
    @rmtsrvname = N'SRVR002\ACCTG',   
    @locallogin = NULL ,   
    @useself = N'True' ;  
GO

//

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PEER',@useself=N'False',@locallogin=NULL,
@rmtuser=N'$(linkedServerUsername)', @rmtpassword='$(linkedServerPassword)';

Additionally, please check if the name of the target user added in the sp_addlinkedserver

    EXEC master.dbo.sp_addlinkedserver @server = N'<LINKED SERVER NAME>', 

@srvproduct=N'', @provider=N'SQLNCLI11', @provstr=N'Server=<SERVER_DNS_NAME>;Initial Catalog=master;Persist Security 

Info=False;MultipleActiveResultSets=FalseEncrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;User ID=<TARGET USER>'

Answer 2

If you have a connection problem maybe you need to check your Managed Instance network configuration (NSG).

check if you have outbound rule to connect SQL server. if not you need to create it.