All of you, that have worked with Microsoft Azure, have probably noticed that Azure does not allow you to mutate or select data from a different database than the transaction database. However often you find yourself in a situation where this is necessary. This blog will help you with exactly that. This blog describes 2 features which you can use.

Feature 1 External Datasource

Microsoft SQL has a feature available called “External Datasource”. This feature supports selecting data from an external source, but does not require to connect to this external source. It is kind of like a fake table within your database, which selects data from the defined external source. While using an External Datasource it is possible to select data from another database within Azure. Do keep in mind that it is not (yet) possible to mutate data using an External Datasource. If you would like to mutate data on a different database, you have to use the second option this blog will describe.

How to configure an External Datasource

An External Datasource must be created on the database you would like to use as the transaction database. For example, within my application I would like to be able to display all the users that have been created within IAM. This means that I will have to set up an External Datasource on my application database. This External Database will refer to the user table in IAM.

The first step is creating a MASTER KEY with a password.

-- Create a database master key if one does not already exist, using your own password. This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!MyC0mpl3xP@ssw0rd!' ;

Now that the Master Key is in place, set up the SCOPED CREDENTIAL. This is the credential which allows you to select data from the database you would like to use as an External Datasource. In my example this means add a Scoped Credential which has access to the IAM database.

CREATE DATABASE SCOPED CREDENTIAL IAM_ACCOUNT
WITH
IDENTITY = 'username',
SECRET = 'password'
;

The Identity parameter equals the username and the parameter secret equals the password of this account. Do take notice that this user must have access to the table(s) you want to make available with this External Datasource.

After setting up the Scoped Credential, we must create the External Datasource. The example below will demonstrate how to do this. Notice this is an example and not an actual working script.

-- connect to the database
CREATE EXTERNAL DATA SOURCE IAM
WITH
(
 TYPE          = RDBMS
,LOCATION      = 'myazureserver.database.windows.net'
,DATABASE_NAME = 'IAM'
,CREDENTIAL    = IAM_ACCOUNT
)
;

-- create the external datasource (user tabel)
CREATE EXTERNAL TABLE  dbo].Ausr]
(
 [usr_id]     varchar(100)  NOT NULL
,hsur_name]   nvarchar(100) NOT NULL
,cfirst_name] nvarchar(100) NOT NULL
,cend_on]     datetime2
)
WITH
(
DATA_SOURCE = IAM
)
;

After setting up the external table, we can now select the users from IAM using a from iam_user statement.

Feature 2 procedure sp_execute_remote

Microsoft Azure includes a system stored procedure called sp_execute_remote. As the name suggest this allows you to execute commands on a remote database. This procedure requires 2 mandatory parameters:

1. Database_name
2. Command

The command parameter must contain the command you would like to execute on a specific database. This command will then be executed upon the database which has been set to the database parameter.

-- declare parameters
declare @database_name      nvarchar(256) = 'database_name'
       ,@command            nvarchar(max) = 'command'
;

-- execute command
exec dbo.sp_execute_remote @database_name
                          ,@command

;

This should help you set up cross database scripts on Microsoft Azure. Feedback is, as always, welcome. So feel free to post additional input on the subject.