title: Setting up a linked server on a Managed SQL Instance
body: <p>On an Azure Managed SQL Instance that has been setup, I'm trying to setup some linked server connections.</p>
  <p>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.</p>
  <p>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.</p>
  <p>The port for the databases when accessing through MS, is 1433.</p>
  <p>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.</p>
  <p>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?</p>
  <p>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.</p>
  <p>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.</p>
  <p><a href="https://i.stack.imgur.com/Kz1sh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Kz1sh.png" alt="enter image description here" /></a></p>
  <p>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?</p>
  <p>The DB I am attempting to connect to is <strong>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)</strong></p>
  <p>Any help would be greatly appreciated.</p>
  <p>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?</p>
  <p>I am getting an error that states: &quot;A network-related or instance-specific error has occurred while establishing a connection to SQL Server.&quot; I can access this same instance through management studio on this managed SQL instance no problem. But I cannot create a linked server connection.</p>
  <p><strong>Here is what I've used to try and create the Linked Server connection:</strong></p>
  <pre><code>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
  </code></pre>