How to create a linked server for Azure SQL Database (2023)

A linked server provides access to data from another SQL Server or data source (such asExcel) by usingSQL Server Management Studio (SSMS)ofTransactional SQL。

This article explains how to create and configure a linked server to pull data from AzureSQL-database. We will also explain how to solve some common problems/problems during the process of creating a linked server for Azure SQL Database.

Create a linked server to Azure SQL Database through SSMS

Create a linked server bySSMS, goobjectbrowserbelowserverobjectfolder, right clicklinked serverfolder and choose from the context menunew linked serverOrder:

itnew linked serverThe window opens:

In this window you must first enter the name of your linked server in Linked Server.linked serverand select the server type by clickingSQL-serverofother data sourcesradio button belowGeneralTicket.

We choose firstSQL-serverradio button belowserver typesection and see what happens. In addition, approxGeneralon the tablinked serverIn the box, enter the name of the Azure SQL server for which you want to create the linked server:

belowSafetytab, selectUse this security context for productionradio button and enter the user credentials present on the Azure server:

more aboutSafetyInserveroptiestabs can be found inHow to create and configure a linked server in SQL Server Management Studioarticle.

After entering the user information, press theOKbutton to create a linked server to Azure SQL Database. now onlinked serverfolder, the Azure Linked Server we created will appear and be located in theTable of contentsfolder, all available databases are listed:

However, when a specific database (such as TestDatabase) needs to be expanded to view the database's tables, the following error message appears:

Unable to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Extra information:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Connection Info)

This version of SQL Server does not support referencing database and/or server names in 'TestDatabase.sys.sp_tables_rowset2'. (Microsoft SQL Server, error: 40515)

This error occurs because Azure does not allow changes to the master database. To resolve this issue, you must connect directly to the Azure database you want to use.

Delete the linked SQL Azure server we created and create a new one:

this time, insideserver typeShareGeneraltab, selectother data sourcessingle button. The name of the linked serverlinked serverThis time, the text box can be anything you like (for example, AZURE SQL DATABASE). belowproviderdrop-down list, selectMicrosoft OLE DB provider for SQL Serverthing. insidedata sourceEnter a name for SQL Azure (for example, server.database.windows.net) in the text box. mostimportantTo correctly create the settings for the linked server to Azure SQL Database, in theTable of contentsText box for the Azure SQL Database for which you want to create a linked server to Azure SQL Database (for example, TestDatabase). Otherwise we get the same thing if the field is left blank40515An error occurred while retrieving the list of tables belowTable of contentsmap.

belowSafetytab, use the same settings we used in the previous example and press theOKknob. This will create a linked server with the Azure SQL Database (TestDatabase database), and when the plus sign (+) next to the Tables folder is pressed, the Tables folder will expand and show all the tables of the TestDatabase database:

To retrieve data (such as CustomerAddress) from a SQL Azure table, type the following code:

If all goes well, the data from the CustomerAddress table should appear in the fileresultrooster:

Now if you try to run a stored procedure from a linked Azure SQL DATABASE server, you may see the following message:

Message 7411, level 16, state 1, line 48
Server 'AZURE SQL DATABASE' is not configured for RPC.

This is because RPC and RCP Out (remote phone call) option to allow calls to external procedures from linked servers or calls to linked servers, set to false by default.

To resolve this issue, right-click the linked AZURE SQL DATABASE server and selectcharacteristicoptions:

belowserveroptiestab, settingsremote phone callInexecution of remote procedure callsoptionsReal:

Or, in Query Editor, paste and run the following code:

OPENQUERY function

itopen inquiryfunction can be used to retrieve data from a linked server by running code like this:

The result will be the same as the example above.

This is a temporary method of using a linked server to connect to and request data from a remote server. If you often use connections to remote servers, using a linked server is a better solution than using the OPENQUERY function.

This function can be usedvanConditionsinsert,to elect,to deleteofrefreshstatement.

Create a linked server to Azure SQL Database using Transact-SQL

To create a linked server to Azure SQL Database, type the following code into the query editor:

For more information on how to set up a linked server with T-SQL, seeCreate, configure, and delete SQL Server linked servers using Transact-SQLPage

Connect to Azure SQL Database and query data using distributed queries

Use T-SQL to connect to Azure SQL Database and access data without first creating a linked serveropen row setofopen data sourceFunction.

Open a connection and query data from Azure SQL Database using the following commandopen row setfunction type the following code in the query editor:

If the above code doesn't work for some reason, use the following code to connect to Azure SQL Database and query data:

Another way to connect to and query Azure SQL Database data is by usingopen data sourceFunction.

Paste and run one of the following codes in Query Editor:

of

Common errors that can occur when using T-SQLopen row setInopen data sourceFunction:

Message 15281, level 16, state 1, line 1

SQL Server blocked access to the "OpenRowset/OpenDatasource" STATEMENT of the "Ad Hoc Distributed Queries" component because the component was disabled as part of the server's security configuration. A system administrator can enable ad-hoc distributed search using sp_configure. For more information on enabling "Ad Hoc Distributed Queries", search for "Ad Hoc Distributed Queries" in SQL Server books at

To solve this problemAd hoc distributed queryThis option must be enabled. Use to enable the ad hoc distributed query optionsp_configureprocedure and paste and run the following code in the query editor:

Previous articles in this series:

• How to create and configure a linked server in SQL Server Management Studio
• Create, configure, and delete SQL Server linked servers using Transact-SQL
• Query Excel data using a server linked to SQL Server
• How to configure a linked server with an ODBC driver