Cross Database Queries

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Explanation of this guide:

This guide will cover the basics on how to create an external table reference for Cross-database querying Azure SQL Databases. For this guide to work, we will need two Azure SQL Databases, we will need at least one SQL Login in master database and a SQL user in the remote database that references the created SQL Login in master database. After this, we will proceed to create the database scoped credential, remote data source, remote table, external table reference and other settings that need to be configured before attempting to do cross-database queries in Azure SQL Database.

Step 1: Create some new databases (in case you need any new database or you are just trying to do a PoC)
You can create a new database with the following commands using SQL Server Management Studio using Transact SQL (T-SQL). If you are facing any permission errors, please refer to your organization's DBA or create the databases in your Azure Portal.

CREATE DATABASE OriginDB(EDITION='Standard', Service_Objective='S0'); CREATE DATABASE RemoteDB(EDITION='Standard', Service_Objective='S0');

After you create these two databases, we are ready to proceed with the next steps in this guide.

Step 2: Create a SQL Login in the logical server's master database (Use MASTER database)
We need to create a new login in master database for the RemoteLogger that will be assigned a user in RemoteDB further in this guide.

CREATE LOGIN RemoteLogger WITH PASSWORD='StrongPassword'; -- Please add a stronger password!

Step 3: Create a SQL User in the remote database (Use RemoteDB)
We now need to create a new user for the RemoteLogger login we previously created in master database. This is the SQL login created in step 2.

CREATE USER RemoteLogger FOR LOGIN RemoteLogger;

Step 4: Create a Master Key in the Origin Database (Use OriginDB)
We now need to create a new Master Key in our OriginDB.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Credentials123!' -- Add a stronger password here as well!

Step 5: Create a Database Scoped Credential in the origin database
We need to create a database scoped credential that has the user and password for the login we created in RemoteDB.

IDENTITY: It's the user that we created in RemoteDB from the RemoteLogger SQL Login.

SECRET: It's the password you assigned the SQL Login when you created it.

CREATE DATABASE SCOPED CREDENTIAL AppCredential WITH IDENTITY = 'RemoteLogger', SECRET='Pa$$w0rd'; -- Use a stronger password!

Step 6: Creating the external data source origin database
Now we will be creating the remote data source reference. This reference will define where to look for the remote database, being it in the same server as OriginDB or in another server. The remote data source for this example will be called "RemoteDatabase".

We need to explain a little bit more about the data we input in these parameters in the WITH statement.

TYPE: for this example and for any Azure SQL Database, we will need to specify that it is a RDBMS engine, being RDBMS a Relational Database Management System.

LOCATION: the location will let the external data source to know where to look. In location we will put our logical server's FQDN or servername. For example server.database.windows.net.

DATABASE_NAME: we need to specify which database we will be pointing to in the external data source.

CREDENTIAL: we need to map the correct Credential, which in this case is the one we previously created as a DATABASE SCOPE CREDENTIAL in step 5.

CREATE EXTERNAL DATA SOURCE RemoteDatabase WITH ( TYPE=RDBMS, LOCATION='servername.database.windows.net', -- Change the servername for your server name. DATABASE_NAME='RemoteDB', CREDENTIAL= AppCredential );

Step 7: Create the table in the remote source

Now, of course, we need to have an existing physical table in our RemoteDB, which will be the one that we will be referencing from OriginDB further in this guide.

CREATE TABLE RemoteTable ( ID INT IDENTITY PRIMARY KEY, NAME VARCHAR(20) NOT NULL, LASTNAME VARCHAR(30) NOT NULL, CEL VARCHAR(12) NOT NULL, EMAIL VARCHAR(60) NOT NULL, USERID INT );

Step 8: Create the external table in the origin database
Create a mapping table in OriginDB that references the fields in RemoteDB for table RemoteTable as intended in step 7.

DATA_SOURCE: here we are referencing the data source that we created in step 6. This data source will let the database know where to go and look for data. Also in step 6 we referenced a CREDENTIAL, this credential was created in step 5 and there we reference the IDENTITY and SECRET, which are the user and the password that the remote call will be using to authenticate to RemoteDB and obtain the requested data in the external table (remote table).

CREATE EXTERNAL TABLE RemoteTable ( ID INT, NAME VARCHAR(20) NOT NULL, LASTNAME VARCHAR(30) NOT NULL, CEL VARCHAR(12) NOT NULL, EMAIL VARCHAR(60) NOT NULL, USERID INT ) WITH ( DATA_SOURCE = RemoteDatabase );

Step 9: Granting the RemoteDB user SELECT permissions on RemoteTable (Use RemoteDB)

Here we will grant our RemoteDB user the rights to SELECT on the table. This will allow the OriginDB query to authenticate against RemoteDB with the correct credentials and avoid bumping into permission issues later on.

GRANT SELECT ON [RemoteTable] TO RemoteLogger;

Step 10: Inserting data in RemoteTable

Now all is left is to populate the RemoteTable in RemoteDB with some data and test out the remote call from OriginDB.

INSERT INTO REMOTETABLE (Name, LastName, Cel, Email, UserId) VALUES ('Vlad', 'Borvski', '91551234567', 'email3@contoso.com', 5), ('Juan', 'Galvin', '95551234568', 'email2@contoso.com', 5), ('Julio', 'Calderon', '95551234569', 'email1@contoso.net',1), ('Fernando', 'Cobo', '86168999', 'email0@email.com', 5);

Step 11: Querying the remote table from OriginDB

From OriginDB, we will proceed to SELECT COUNT() from RemoteTable to verify we are viewing the same amount of records that RemotDB has. If we do the same SELECT on RemoteDB and in OriginDB and the result from A and B is the same, then we have this correctly setup.

SELECT COUNT(*) FROM RemoteTable;

Step 12: Check if the data is the same

Then, lastly we can just do a normal SELECT on the RemoteTable table and see the same data that we have in RemoteDB.

SELECT * FROM RemoteTable;

Leave a Reply Cancel reply