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

Microsoft

Dec 14, 2021

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.

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.

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. Please note this option is the least secure way to address the security configuration of Linked Server as any user who uses the Linked Server will be authenticated on the remote server using credentials provided here. Use of this option should be limited to testing environment.

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.

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]