Lesson Learned #81: How to create a linked server from Azure SQL Managed Instance to SQL Server OnPremise or Azure VM


Hello Team,

Today, I worked on a service request where our customer requested information how to create a linked server from Azure SQL Managed Instance to SQL Server OnPremise.

In this post, I would like to share with you my lessons learned on it:

  • The first point, we need that our Azure SQL Managed Instance has access to our SQL Server OnPremise server. So, you may configure to  Site-To-Site or any ExpressRoute to connect these environments using a VPN or you could connect if your SQL Server OnPremise has the port 1433 (or other) opened as inbound to the IP of the Azure SQL Managed Instance.
  • If you have two different Virtual Networks in Azure you could use Virtual network peering.
  • Other thing that it is very important is about the Custom DNS in order to find the server name (you could connect using the IP if needed) and have the routing correctly configured.
  • Remember that the IP source of the Azure SQL Managed Instance will be the IP of the external load balancer and you need to open in your SQL Server OnPremise firewall - Review this URL.
  • Open the outbound port 1433 in the NSG of the Azure SQL Managed Instance subnet to connect to SQL Server OnPremise if needed.

In this example, I have an Azure Virtual Machine that is running SQL Server and I have the port 1433 opened in my firewall.

Basically, I executed the following script in my Azure SQL Managed Instance to create a new Linked Server with these parameters. I modified some parameters in the string provider due to several configurations that I have in this server.

USE [master]
GO

/****** Object:  LinkedServer [MYTESTJM]    Script Date: 3/22/2019 6:09:37 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MYTESTJM', @srvproduct=N'', @provider=N'SQLNCLI11', @provstr=N'Server=remotemachine.northeurope.cloudapp.azure.com;Initial Catalog=master;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYTESTJM',@useself=N'False',@locallogin=NULL,@rmtuser=N'remoteuser',@rmtpassword='remotepassword'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYTESTJM', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Finally, just running a simply query like the below one, you could obtain the details, in the similar way that you have in OnPremise.

select top 10 * from mytestjm.databasename.dbo.tablename 

Enjoy!

 


Comments (0)

Skip to main content