How to create linked server between On-premise and Azure SQL database

In this post, let us see how to create linked server between On-premise and Azure SQL database and after establishing linked server, how we can query Azure SQL database & create objects in Azure SQL database from local On-premise SQL Server.

In previous posts, I wrote about 

• How to create Azure SQL database and server
• How to connect Azure SQL database through SSMS
• Create SQL objects in Azure SQL database

I am going to create a table and insert sample data in my Azure SQL database, so that after creating linked server I can query this sample object from my local On-premise SQL Server.

From SSMS, connect to On-premise SQL Server. Goto Object Explorer -> Server Objects -> Linked servers

Linked Server - Linked server name
Provider - SQL native client 11.0
Data source - Azure SQL Server detail
Catalog - Azure SQL database

Provide Remote login & password (Azure SQL server authentication):

Now we can query the Azure SQL database from local On-premise SQL Server using four part naming convention as shown below:

SELECT * FROM Linkedservername.databasename.schemaname.tablename

SELECT [Id]
      ,[Name]
  FROM [AZUREDB].[trnsqldb].[dbo].[Test]
GO

Now let us try to create table in Azure SQL database from local On-premise SQL Server, below is the syntax but it will throw error, we need to enable RPC out in Linked server property.

Right-click on Linked server -> Property ->  RPC Out -> True 

--Creating table in Azure sql database from local by mentioning at linked server name in below script
EXEC ('DROP TABLE IF EXISTS Test2; CREATE TABLE Test2(Id INT,Name VARCHAR(20))') at [AZUREDB]

--inserting into Azure sql database from local using four part naming convention
INSERT [AZUREDB].[trnsqldb].[dbo].[Test2]
SELECT 1,'Modi'

Reference: 

• We can also create linked server using t-sql script
• RPC Out

See Also: 

• My posts on Microsoft Azure