SQL Server, T-SQL create Linked Server with Azure SQL Database

Today, we got a requirement to connect our Azure VM SQL (Report server) instance to access the Azure Database. Let me show you how create linked server for SQL Database on Azure from Azure VM SQL instance.

Please follow the mentioned steps to create a Linked Server to Azure SQL Databse:

Step 1: Open SQL Server Management Studio

Step 2: Execute script: "SP_addlinkedserver".

EXEC sp_addlinkedserver
@server='AzureDB', -–Provide Linked Server Name
@srvproduct=''    
@provider='sqlncli',
@datasrc='xxxxxx.database.windows.net', –provide Azure SQl Server name
@location='',
@provstr='',
@catalog='AzureDatabaseName' –Provide azure database name

Step 3: Execute the script to provide the SQL Server login for the above created linked server.

Exec sp_addlinkedsrvlogin ‘Azure’, ‘FALSE’, NULL, ‘’, ‘’;

Step 4: Verify the created linked server, under the Databases > Server Objects > Linked Servers > Your Linked Server Name

Step 5: Access the azure SQL database table. You need to use FOUR part query. [linked server name].[database name].[schema name].[table name]"

SELECT top 10 * 
FROM [linked server name].[database name].[schema name].[table name]

Reference : Tejas Shah http://www.SQLYoga.com