Blog Post

Azure Database Support Blog
1 MIN READ

Lesson Learned #87: Connecting from SQL Server 2008/R2 to Managed Instance using Linked Server.

Jose_Manuel_Jurado's avatar
Jose_Manuel_Jurado
Icon for Microsoft rankMicrosoft
Jun 01, 2019
    Hello Team,   As you know, the support of SQL Server 2008 and SQL Server 2008 R2 is approaching to the end and some customers are using Linked Server to connect to Azure SQL Managed Insta...
Published Jun 01, 2019
Version 1.0
TonyCampney's avatar
TonyCampney
Copper Contributor
Dec 06, 2021

I'm trying to do this as well but failing.  Any chance you'd be able to provide the details that worked in a format similar to below?

 

EXEC sp_addlinkedserver
server='xxxxx.yyyyyyy.DATABASE.WINDOWS.NET', --'FM-SQLSERVER01-PCC-SQL.C93B3B3DA95B.DATABASE.WINDOWS.NET'
@srvproduct='',
Provider='sqlncli',
@datasrc='xxxxx.DATABASE.WINDOWS.NET',
@location='',
@provstr='Server=tcp:xxxxx.yyyyy.database.windows.net,1433',
@catalog=NULL


EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'xxxxx.yyyyyyy.DATABASE.WINDOWS.NET',
@useself = 'false',
@rmtuser = 'user',
@rmtpassword = 'password'

EXEC sp_serveroption 'xxxxx.yyyyyyy.DATABASE.WINDOWS.NET', 'rpc out', true