Azure SQL Database Cross Database Joins with Local Databases

Organizations that make use of the cloud sometimes have to integrate that information with local databases. If you have a need to do cross-database joins including an Azure SQL Database, you might be wondering how that can be accomplished. One solution that works is to go to a local database and add a linked server to your Azure database.

Defining a Linked Server with sp_addlinkedserver

To add a linked server, open your local database in SQL Server Management Studio and execute a sp_addlinkedserver statement. You'll need to specify a number of parameters:

• @server : the name you want to use to refer to the database in queries. Use a simple, memorable name. In our example, we'll use abc.
• @datasrc : the data source (Azure database server), such as clients.database.windows.net
• @catalog : the database to connect to. We'll use ABC_PROD in our example.
• @provider : set to SQLNCLI.
• @srvproduct : set to empty string.

sp_addlinkedserver @server='abc', @datasrc=N'clients.database.windows.net', @catalog=N'ABC_PROD', @provider=N'SQLNCLI', @srvproduct=N''

Defining a Linked Server Login

To go with your Azure linked server you'll need to define a login for it. Use a sp_addlinkedsrvlogin statement with these parameters:

• @rmtsrvname : name of the linked server (same value you used for @server in the previous section). Example: abc
• @useself : set to 'false'
• @rmtuser : username. Example: dbrpt
• @rmtpassword : password. Example: s4ssafr4ss!

sp_addlinkedsrvlogin @rmtsrvname='abc', @useself='false', @rmtuser='dbrpt', @rmtpassword='s4ssafr4ss!'

Viewing Linked Server in SSMS Object Explorer

After performing the above steps, refresh your SSMS Object Explorer and you should see your linked server. If you defined everything correctly, you'll also see the database objects available, such as tables and views.

For troubleshooting, there's also a handy Test Connection option if you right-click the linked server name.

Querying the Azure Linked Server

To query your linked database, use the prefix [server][database] before specifying schema/table, where server is the value your used for @server and database is the value you used for @catalog in the first step above. For example,

SELECT * FROM [abc].[ABC_PROD].[dbo].person

Including Azure Linked Server in Cross-Database Queries

Now that you have a working linked server, you can include it in database queries that join to the local database or other linked databases. In the query below, the Azure linked database is joined to a local database table.

There you have it: queries that combine cloud and local data through the use of a Linked Server.