Create a linked server to an Azure SQL database via SSMS

This article will explain how to create and configure linked server to retrieve data from an Azure SQL database. Also will explain how to solve common problems/issues during the process of creating a linked server to an Azure SQL database.

To create a linked server via SSMS, go to the 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.

The New Linked Server window will be opened

In this window, the first thing that needs to be done is to enter a name for the linked server in the Linked server box and to choose the server type by clicking the SQL Server or Other data source radio button under the General tab.

Under the Security tab, select the Be made using this security context radio button and enter user credentials that exist on Azure server.

After entering the user credentials press the OK button to create a linked server to an Azure SQL database. Now under the Linked Servers folder, Azure linked server that we created will appear and in the Catalogs folder, all available databases will be listed.

But when expanding a particular (e.g. TestDatabase) is needed in order to see tables of the database the following error message will appear.

This error occurs because Azure does not allow to alter the master details. To resolve this, you need to connect directly to Azure database you are going to use.

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

This time, under the Server type section of the General tab, choose the Other data source radio button. The name for the linked server in the Linked server text box can be, this time whatever you like (e.g. AZURE SQL DATABASE). Under the Provider drop down box choose the Microsoft OLEDB Provider SQL Server item. In the Data source text box, enter the name of the SQL Azure (e.g. server.database.windows.net). The most important setting in order to correctly create a linked server to an Azure SQL database is to enter the name in the Catalog text box (e.g. TestDatabase) of an Azure SQL database for which you want to create a linked server to an Azure SQL database. Otherwise, if this field is left empty, we will encounter the name 40515 error when trying to get a list of the tables under the Catalogs folder.

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 (TestDatabase database) and when the plus(+) sign next to the Tables folder is pressed, the Tables folder will expand and show all tables for the TestDatabase database:

To retrieve data from the SQL Azure table (e.g. CustomerAddress), type the following code:

If everything goes well, the data from the CustomerAddress table will appear in the Results grid.

Now if you try to execute a stored procedure from the AZURE SQL DATABASE linked server, the following message may appear:

This is because the RPC and RPC Out (Remote Procedure Call) options that allow remote procedures to be called from the linked server or to be called to the linked server by default set to false.

To fix that, right click on the AZURE SQL Database linked server, choose the Properties option:

Under the Server Options tab, set the RPC and RPC Out options to True:

Or in a query editor, paste and execute the following code:

OPENQUERY Function

The OPENQUERY function can be used to get data from the linked server by executing code like this:

The result will be the same as from the example above.

This is the ad hoc method for connection to a remote server using the linked server and querying data from a remote server. If the connection to remote server is frequently used, the using the linked server is better solution instead of using the OPENQUERY function.

This function can be used in the FROM clause of the INSERT, SELECT, DELETE or UPDATE statement.

Create a linked server to an Azure SQL database using Transact-SQL

In order to create a linked server to an Azure SQL database, type the following code in a query editor:

Connecting to Azure SQL database and querying data using the distributed queries

To connect to Azure SQL database and access data without creating  a linked server first, use the T-SQL OPENROWSET or OPENDATASOURCE functions.

To open a connection and querying data from the Azure SQL database using the OPENROWSET function type the following code in a query editor:

If, for some reason, the above code does not work, use the code below to connect and query data from Azure SQL database.

Another way of connecting and querying data from Azure SQL database is by using the OPENDATASOURCE function.

In a query editor, paste and execute one of the following codes:

Common error that may occur when using the T-SQL OPENROWSET and OPENDATASOURCE functions:

To resolve this the Ad Hoc Distributed Queries option should be enabled. To enable the Ad Hoc Distributed Queries option, use the sp_configure procedure and in a query editor, paste and execute the following code.