Marko Zivkovic

How to create a linked server to an Azure SQL database

September 27, 2017 by

Linked servers allow to access data from another SQL Server or another data source (e.g. Excel) by using SQL Server Management Studio (SSMS) or Transact-SQL.

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

Create a linked server to an Azure SQL database via SSMS

To create a linked 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:

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 a 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.

Let’s first choose the SQL Server radio button under the Server type section and see what will happen. Also, on the General tab in the Linked server box, enter the name of the Azure SQL server for which a linked server is created:

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

More about the Security and Server Options tab can be found on the How to create and configure a linked server in SQL Server Management Studio article.

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 database (e.g.TestDatabase) is needed in order to see tables of the database, the following error message will appear:

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

ADDITIONAL INFORMATION:

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

Reference to database and/or server name in ‘TestDatabase.sys.sp_tables_rowset2’ is not supported in this version of SQL Server. (Microsoft SQL Server, Error: 40515)

This error occurs because Azure does not allow to alter the master database. To resolve this, you need to connect directly to the 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 OLE DB 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 same 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:

Msg 7411, Level 16, State 1, Line 48
Server ‘AZURE SQL DATABASE’ is not configured for RPC.

This is because the RPC and RCP 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 options:

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 results 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, then 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:

More about how to set linked server by using T-SQL can be found on the How to create, configure and drop a SQL Server linked server using Transact-SQL page

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 reasons, 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 the Azure SQL database is by using the OPENDATASOURCE function.

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

Or

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

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books On

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:

Previous articles in this series:

See more

Consider these Free tools and community editions for Azure SQL Database

Useful links


Marko Zivkovic

Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic
Marko Zivkovic
SQL Azure

About Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques. Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins. View all posts by Marko Zivkovic

356 Views