How to create a Linked Server to a SQL Azure Database.

Date created: 7/29/2017.

On this article we are showing how to create a linked server to a SQL Azure Database.

To start we will open SQL Server Management Studio and we will get connected to a SQL Azure Database named AdventureWorksLT.

As you can see on below image we need to provide the SQL Azure Database server name.

Once you click on the "Connect" button on the previous dialog, you may get the "New Firewall Rule" dialog if you have not previously created a firewall rule to allow access to the SQL Azure Database from your current IP address. Please click on the "Sign In" button to login to SQL Azure services.

Proceed to provide credentials of the Microsoft account you used to access Azure services.

On below dialog, click the "OK" button to create the firewall rule on Azure that will allow you to access your SQL Azure database using SQL Server Management Studio (SSMS).

Now on below image you can appreciate on the Object Explorer that we are already connected to the SQL Azure database, we need now to connect to a on-premises instance of SQL Server and that is the reason you can also appreciate the "Connect to Server" dialog on below image.

Since we are now connected to the SQL Azure database and to the local SQL Server instance, let's open a new query window to start creating the linked server using Transact-SQL.

Below image is showing how we use special stored procedure sp_addlinkedserver to create the linked server. The @server parameter refers to the name the linked server will have once created. The @datasrc parameter refers to the name of the SQL Azure Database server. The @catalog parameter should have the name of the database on the SQL Azure server that we want to access via the linked server.

Next step is adding the remote user credentials to be able to access the SQL Azure database as shown on below image.

Now let's enable RPC OUT to be able to use a four-part qualifier in queries involving the linked server.

Let's refresh the Linked Servers section under Server Objects

Now let's test the newly created linked server with a SELECT statement.

As you can see the above query took 7 seconds to complete. Mainly because of the OLEDB wait type common when querying a remote server using linked servers. The same happened when below INSERT statement was executed. It took 2 seconds to insert a record on a table.

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.