Azure SQL Database cross-database queries

In Azure SQL Database, you need to use Elastic Queries to run a query that access data from other databases, which is called cross-database queries. You probably have done it in SQL Server on-premises or IaaS, where you access tables from other databases by typing <database name>.<schema name>.<table name>.

In this post, I will show how to configure Elastic database query, please follow steps below:

• Create a MASTER KEY:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

• Create a DATABASE SCOPE CREDENTIAL (don’t include @servername):

CREATE DATABASE SCOPED CREDENTIAL <CREDENTIAL>
WITH IDENTITY = '<User Name>',
SECRET = '<Password>';

• Create an EXTERNAL DATA SOURCE:
  1. In Location enter your Azure Server Name.

E.g.: ‘db.database.windows.net’

CREATE EXTERNAL DATA SOURCE <DATA SOURCE NAME>
WITH (
TYPE = RDBMS,
LOCATION = N'<Azure SQL DB Server Name>',
DATABASE_NAME = N'<Database Name>',
CREDENTIAL= <Credential - from Previous Step>);

• Create an EXTERNAL DATA SOURCE:

CREATE EXTERNAL TABLE [dbo].[Supplier](
    [id] int NOT NULL,
    [name] nvarchar(200) NULL,
    [contact_name] nvarchar(100) NOT NULL,
    [contact_phone] nvarchar(20) NOT NULL,
    [street] nvarchar(200) NOT NULL,
    [city] nvarchar(50) NOT NULL,
    [state] nvarchar(20) NULL,
    [country] nvarchar(50) NOT NULL
)
WITH
(
     DATA_SOURCE = <EXTERNAL DATA SOURCE NAME>,
     SCHEMA_NAME = N'<SCHEMA NAME IN THE SOURCE DATABASE>',
     OBJECT_NAME = N'<TABLE NAME IN THE SOURCE DATABASE>'
);

SCHEMA_NAME and OBJECT_NAME clauses are optional, as it provides the ability to map the external table to a table in a different schema or table name.

Please see table below, for the first record, Schema_Name and Object_Name are not required as Schema and Table name are the same. For the second record Schema_Name and Object_Name are required as Schema and Table names are different.

You can use the query below to retrieve a list of all external data sources:

select * from sys.external_data_sources;

Thanks!