When Azure SQL Database was first released, each database was it's own entity, with no way to access another database, even when created on the same virtual server. While the databases on a server share a master database, with associated logins, there was no way to access one database from another. This was a limitation that many people were unable to accept, preventing some upgrades to the platform.

As the SQL Server codebase evolved, new features and functions appeared, such as external tables using the Polybase technology. In this article, we will look at how you can set up an external table in one database that can be used to query data in another. We will refer to the database from which we run a query as the source server and database. The database (and server) that we are attempting to remotely query will be the target.

Note: If you wish to follow along, you will need two Azure SQL Databases.

The short set of steps is listed here. We will follow them in more detail below:

• Create a login and on the target server/database
• Create a master key in the source database
• Create a database scoped credential in the source database
• Create an external data source in the source database
• Create an external table in the source database

The Setup

I've got two Azure SQL Databases setup already. One is called "SQLSat" with some data on SQL Saturday events. The other is "Speakers" where I do some demo work. I've decided that I want to be able to query the SQLSat database from the Speakers database. Specifically, I want to get a list of events and dates that I'll use to track some speaking activity.

In the SQL Sat database, here is the SQLSatEvent table:

CREATE TABLE [dbo].[SQLSatEvent](
[SQLSatEventKey] [int] IDENTITY(1,1) NOT NULL,
[SQLSatNumber] [smallint] NULL,
[SQLSatName] [nvarchar](500) NULL,
[SQLSatEventDate] [date] NULL,
[LocationKey] [int] NULL,
[CallForSpeakerDeadline] [date] NULL,
[Completed] [bit] NULL,
[Cancelled] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SQLSatEvent] ADD  DEFAULT ((0)) FOR [Completed]
GO
ALTER TABLE [dbo].[SQLSatEvent] ADD  DEFAULT ((0)) FOR [Cancelled]
GO

In the Speakers database, I have a user, SpeakerApp, that has rights to a few tables. I want this user to be able to query the list of events in the SQLSat database.

Configuring Security

We will use a credential to access the data in the other database. To do this, we need to actually create a login and user that we can use for the credential. Let's start by connecting to the master database on our server. Once we do that, we need to create a login that will be used by the credential.

In the Master database on the target server, run this (with a stronger password:

Master Database:

CREATE LOGIN QueryUser WITH PASSWORD = 'Demo12#45Six'

We need a user now to match with this login, so let's connect a user with this login.

SQLSat database

CREATE USER QueryUser FOR LOGIN QueryUser

We also need to grant rights for the user to our table, so let's do that here.

SQLSat database

GRANT SELECT, INSERT ON dbo.SQLSatEvent TO QueryUser

Next, we need a master key for security in the Speakers database. Let's go there and just create a master key with a password. Please use a strong password here.

Speakers database

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Demo12#498Seven';

Now the last part of the security is the Database Scoped Credential. This will use the values for the user we created above. The credential is the way we allow a user to proxy with the login/user from the target database.

Speakers database

CREATE DATABASE SCOPED CREDENTIAL QueryCredential 
  WITH IDENTITY = 'QueryUser', SECRET = 'Demo12#45Six'

Getting the Tables Set

Once we have set up the security, let's now set our tables. We already have the target table in the SQLSat database, so let's get things setup to link to this from the Speakers database. The first step in this is to create an EXTERNAL DATA SOURCE.

In this CREATE command, you give a type as a relational database. There are other types, but here we are accessing SQL Server. The location is your server. This does not need to be the same server. This is just the location of the database. You specify the database and then the Database Scoped Credential from above.

Speakers database

CREATE EXTERNAL DATA SOURCE SQLSatDB
 WITH 
 ( TYPE = RDBMS,
   LOCATION='dkranchapps.database.windows.net',
   DATABASE_NAME = 'SQLSat',
   CREDENTIAL = QueryCredential
 );

Now we create our table. Note that this table needs to match the schema of the target table. I'll just take the code from above for the SQL Sat table and then add a WITH clause. The WITH clause includes the data source, which we created above.

The other changes made are including the EXTERNAL keyword in the above. We also need to change the key to remove the IDENTITY keyword. This isn't needed with the external table as this is just a pointer to the actual table, which has the identity set. The same thing for the PK and defaults. We don't add those clauses to this external table.

Speakers database

CREATE EXTERNAL TABLE [dbo].[SQLSatEvent](
[SQLSatEventKey] [int] NOT NULL,
[SQLSatNumber] [smallint] NULL,
[SQLSatName] [nvarchar](500) NULL,
[SQLSatEventDate] [date] NULL,
[LocationKey] [int] NULL,
[CallForSpeakerDeadline] [date] NULL,
[Completed] [bit] NULL,
[Cancelled] [bit] NULL
) WITH (DATA_SOURCE = SQLSatDB)
GO

Testing the Configuration

Now that things are set up, let's check how this works. In the Speakers database, let's query our table. I'll do a simple SELECT from the table, and I see results.

Select works, but what about INSERT. I added INSERT rights, so let's check that. I'll do an insert and then select the value back in the image below.

This doesn't work. External tables are designed to be for reading, not writing (or updating). Not a bad thing, though certainly a limitation.

Summary

It's not hard to allow cross database access, but there are some limitations to be aware of. The external table must match the schema and name of the target table. The security is pretty easy to set up, but be sure you use strong passwords and secure them if you save them. You don't really need to save the password, so you might choose to pick a really long one.

One thing to keep in mind is that adding the credential and the login are adding attack surface area. If you don't use these, or you stop using them, remove these security objects.

References

A few items that helped write this article:

• Polybase
• Database Scoped Credential
• CREATE EXTERNAL TABLE