Tuesday, December 14, 2021

How to create Linked server from SQL Server to Azure SQL Database

Here in this blog, we are going to demo how to configure Linked server from on-prem SQL Server instance to Azure SQL database. I will have a reference to this blog in my subsequent blogs that will soon be available for Dynamic Data Masking & Cross database/server queries. 

We have taken example of two databases, Database1 (Copy of AdventureWorks 2019) as Azure SQL database & Database2 hosted in On-prem SQL Server Instance. In this demo we will be querying Database1 tables in the context of Database2.  

 

Database1: Azure SQL database 

Database2: SQL Server Instance on-prem 

 

Here is the sequence of steps that you need to follow to configure Linked Server using SSMS tool. 

Step-1: Connect to SQL Server Instance in SSMS tool and go to Object Explorer. Expand the Server Objects, right click on Linked Server and create a New Linked Server 

 

MadhumitaTripathyMSFT_7-1639472387104.png

 

 

 

Step-2: Go to General tab in the new Linked Server window. Under the Server type section, choose the Other data source option. Give a suitable name to the Linked Server as per your choice in the Linked Server section. Choose the “Microsoft OLE DB Provider SQL Server” in the Provider dropdown. In the Data source section, specify the Azure database logical server name for e.g., logicalservername.database.windows.net. You just need to change the logicalservername to the actual Azure server name which you can get from the Azure portal. Enter the Azure database name that you want to create the linked server to in the catalog field. 

 

MadhumitaTripathyMSFT_8-1639472387086.png

 

 

 

Step-3: Now go to Security tab and choose the option “Be made using this security context”. Enter the SQL login credentials which is already present on the Azure DB server and has access to Database1. 

MadhumitaTripathyMSFT_9-1639472387106.png

 

 

 

Step-4: Once the Linked Server is successfully created you can see it when you expand Linked Server section in Object Explorer and expand it further to view the list of tables. 

 

 

MadhumitaTripathyMSFT_11-1639472387108.png

 

  

 

 

MadhumitaTripathyMSFT_12-1639472387110.png

 

 

 

Step-5: Open a new query window in SSMS and switch to Database2 context on the on-prem SQL Server. Run the SELECT query to fetch data using the Linked Server that you just created in the previous steps. 

 

select * from [AZURE DATABASE DDMTEST].[Database1].[Person].[PersonPhone] 

 

MadhumitaTripathyMSFT_13-1639472387112.png

 

 

Hope you find the blog helpful. Please share your questions or feedback. 

 

 

Posted at https://sl.advdat.com/31TcEe4