0

I have SQL Server Managed Instance. Now I want to get data from on-premises SQL Server to SQL Server Managed instance through linked server on a daily basis.

The on-premises SQL Server has only Windows logins and will provide access through Windows login. How can I create linked server to SQL Server Managed Instance with Windows authentication.

Does enabling Windows auth in SQL Server Managed Instance and connecting to on-premises SQL Server work?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Narendra
  • 11
  • 5

1 Answers1

0

Linked servers on Azure SQL Managed Instance support SQL authentication and Azure AD authentication. Please read here about more limitations related to linked servers. Azure AAD authentication can be used to create linked servers that link two Azure SQL Managed Instances only.

To create a linked server from Azure SQL Managed Instance to a SQL Server On-premises using SQL authentication you can use the following T-SQL script.

EXEC sp_addlinkedserver
@server = N'OnPremSQL', -- The name of the linked server
@srvproduct = N'', 
@provider = N'SQLNCLI', 
@datasrc = N'OnPremSQL.mydomain.com,1433', 
@catalog = N'OnPremDB'; 

-- Create a login mapping for the linked server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'OnPremSQL', -- The name of the linked server
@useself = N'False', 
@locallogin = NULL, 
@rmtuser = N'OnPremUser', -- The remote login name on the on-premises SQL Server instance
@rmtpassword = N'OnPremPassword'; 

-- Enable RPC and RPC out options for the linked server
EXEC sp_serveroption
@server = N'OnPremSQL', 
@optname = N'rpc out',
@optvalue = N'true'; 

EXEC sp_serveroption
@server = N'OnPremSQL',
@optname = N'rpc',
@optvalue = N'true';

-- Test the linked server
SELECT * FROM OnPremSQL.OnPremDB.dbo.OnPremTable;

Please note that you need to ensure that network paths are open to your on-prem server and make sure that outbound NSG rules and DNS resolution are set correctly on the MI subnet, also make sure that any firewalls rules are removed from on-premises server that would block traffic. In addition, to make this work your Managed Instance VNet may need to be routed to your on-prem network over S2S VPN or ExpressRoute.

Please test DNS resolution and ping from SQL Server Agent on Azure SQL Managed Instance to the SQL Server instance before you create the linked server.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • Hi Alberto, so, with this option who all can have read access to that remote db. Can we make only certain logins can read the remote db data? – Narendra Jul 14 '23 at 07:51
  • It will basically depend on whether the user (login) the linked server was configured to connect to the on-premises server only has read access or read-write access to the database tables/views. – Alberto Morillo Jul 14 '23 at 12:30