Blog Detail

11 Nov 2017
Tejas Shah

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]

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.