Hi friends, today I will explain how to create and configure a linked server to retrieve data from an Azure SQL database.
Few points are describe in this article for configure link server with SSMS
- How to configure link server step by step in SSMS
- Solve some common Problem/Issue when create link server
- Using the Query/Script base to create link server
How to configure link server step by step in SSMS
To create link server via SSMS, go to Object Explorer under the Server Objects folder, right click on the Linked Servers folder and from the context menu choose the New Linked Server Command:
Fig 1.1
The New Lined Server window will be opened:
Fig 1.1
Put your Linked Server Name, then
Select data source: Microsoft OLE DB Provider for SQL Server
Then click on security tab on left side
Under the Security tab, select the Be made using this security context radio button and enter user credentials this exist on Azure server
Fig 1.2
Under the Security tab, use the same setting that we used in the previous example and press the OK button.
This will create a linked server to an Azure SQL Database(Azure Database). And when the plus(+) sign next to the table folder is pressed, the Tables folder will expand and show all tables for the selected Azure database:
Fig 1.3
To retrieve data from the SQL Azure table (e.g [dbo].[Actions]), type the following code:
SELECT Top 10 * FROM [LinkedServerName].[AzureDataBaseName].[dbo].[TableName]
Solve some common Problem/Issue when create link server
When we create linked server we facing some common issue like Linked server is showing created but we can’t able to access the linked database. It’s given error which is mention below image:
Fig 2.1
Above mention error, Fail to retrive data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft SQL Server, Error: 40515)
Using the Query/Script base to create link server
How to create linked server for SQL Database on Azure from local SQL Server
Step by step Azure SQL Database Linked Server
Step 1: Go to SQL Server Management Studio
Step 2: Run the below scripts. This script using a stored procedure "SP_addlinkedserver".
EXEC sp_addlinkedserver @server='Azure', -–Provide Linked Server Name @srvproduct='', @provider='sqlncli', @datasrc='AzureDataServerName', -–provide Azure SQl Server name @location='', @provstr='', @catalog='AzureDatabaseName' -–Provide azure database name
Step 3: Run the below script to provide the SQL Server login for the above created linked server.
Exec sp_addlinkedsrvlogin 'Azure', 'FALSE', NULL, '[azure sql server login]', '[sql server password]';
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 4 part query. [linked server name].[database name].[schema name].[table name]
SELECT Top 10 * FROM [linked server name].[database name].[schema name].[table name]
