This article describes about,
How to create Linked Server on SQL Server for azure SQL database? or create linked servers on SQL Server for in azure SQL database
Microsoft Azure SQL Database is a managed cloud database provided as part of Microsoft Azure.
SQL servers allow to do cross SQL queries between 2 databases present on different server.
Linked Server is way of doing it. Using Linked Server on SQL Server you can access different SQL server databases in application just by providing Linked Server name in SQL queries.
Linked Server can be used in standalone SQL queries, Stored Procedures etc. to retrieve data.
All version of SQL server support linked Server. You can Create linked server in SQL server azure.
With Microsoft Azure, we have new concept call PASS Database i.e., Azure SQL Database where you will not have access to SQL Server instance like SQL Server VM.
You cannot create Linked Server on Azure SQL Database, but Azure SQL Database can be accessed on SQL Server with the help of linked Server.
If you want to access AZURE SQL Database from SQL Server VM this is possible with the help of SQL Linked Server.
Note: AZURE SQL Database does not support Linked Server. You can only create linked server of respective SQL DATABASE on SQL Server VM.
How to create Linked Server on SQL Server for azure SQL database? or create linked servers on SQL Server for in azure SQL database
Below script can create linked Server on your SQL Server VM. You have change parameter value in the script as per your requirement to Create linked server in SQL server azure.
In this create linked servers on SQL Server for in azure SQL database script we need to add Azure SQL Database Server name and Database name. You can get that details from Azure portal or by connecting Server in SSMS.
Also need remote login i.e., login present on AZURE SQL Database and having access on that database.
If you want to create user and grant access on Azure SQL Database, check this article
EXEC sp_addlinkedserver
@server='PROD',
@srvproduct='',
@provider='sqlncli',
@datasrc='test.database.windows.net', -- AZURE SQL DATABASE Server Name
@location='',
@provstr='',
@catalog='testdb' --This is database name from AZURE SQL for which you are creating Linked Server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'PROD',
@useself = 'false',
@rmtuser = 'test', -- This user needs to be present on AZURE SQL Database
@rmtpassword = 'abc@123' --Password of user on AZURE SQL Database
EXEC sp_serveroption 'PROD', 'rpc out', true
Once you create Linked Server on SQL Server VM, go to that Linked Server and Do connection testing. Once you get connection Successful, you are good to go for using it in you SQL queries.