Azure SQL Database: Cross-Database Queries on the Same Server

If you have two databases on the same Azure SQL logical server and try a three-part name query like you would on-prem SQL Server – it won’t work.

Here’s how to set it up using Elastic Query.

The setup

• Server: sql-server-dev-001
• Source database: sqldb-prod-001 (where you run queries)
• Target database: sqldb-dev-001 (where the data lives)

1. Check if cross-database queries work

From sqldb-prod-001, try querying a table in sqldb-dev-001:

SELECT * FROM [sqldb-dev-001].SalesLT.vGetAllCategories

If you get this error, you need to set up Elastic Query:

Reference to database and/or server name in 'sqldb-dev-001.SalesLT.vGetAllCategories'
is not supported in this version of SQL Server.

2. Generate a password

In Azure Portal, open Cloud Shell and generate a password:

openssl rand -base64 40

Copy the output.

3. Store the password in Azure Key Vault

Go to your Key Vault, Secrets > Generate/Import:

• Name: sqldb-dev-001-crossdb-password
• Value: paste the generated password

This gives you a single source of truth for rotation later.

4. Create a contained user in the target database

Connect to sqldb-dev-001 and create a user with the password from Key Vault:

CREATE USER [sqldb-dev-001-identity]
WITH PASSWORD = '';

ALTER ROLE db_datareader ADD MEMBER [sqldb-dev-001-identity];

5. Check if a Database Master Key exists

In the source database (sqldb-prod-001), a master key must exist for scoped credentials to work. Check if you already have one:

SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##';

If a row comes back – skip to the next step.

If no rows are returned, create one. This password is only used to encrypt the master key itself – it’s not referenced again in this setup. Generate it with:

openssl rand -base64 40

Store it in Key Vault as sqldb-dev-001-masterkey-password, then create the key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';

6. Create the scoped credential

Still in sqldb-prod-001:

CREATE DATABASE SCOPED CREDENTIAL [sqldb-dev-001-credential]
WITH IDENTITY = 'sqldb-dev-001-identity',
     SECRET = '';

Verify it was created:

SELECT name, credential_identity
FROM sys.database_scoped_credentials;

7. Create the external data source

CREATE EXTERNAL DATA SOURCE [sqldb-dev-001]
WITH (
    TYPE = RDBMS,
    LOCATION = 'sql-server-001.database.windows.net',
    DATABASE_NAME = 'sqldb-dev-001',
    CREDENTIAL = [sqldb-dev-001-credential]
);

What is an external table?

An external table is a live link to a table or view in another database. No data is copied or synced – every query executes against the target database in real-time and returns current results. It’s essentially a remote view with the network overhead of a cross-database call on each query.

You can’t query the external data source directly – there’s no ad-hoc SELECT * FROM [external-source].Schema.Table syntax. That’s the same three-part name pattern Azure SQL blocks. You need to create an external table for each table or view you want to access.

8. Create an external table

Map a table from the target database:

CREATE EXTERNAL TABLE [SalesLT].[vGetAllCategories] (
    [ParentProductCategoryName] NVARCHAR(50),
    [ProductCategoryName] NVARCHAR(50),
    [ProductCategoryID] INT
)
WITH (
    DATA_SOURCE = [sqldb-dev-001],
    SCHEMA_NAME = 'SalesLT',
    OBJECT_NAME = 'vGetAllCategories'
);

9. Test it

SELECT * FROM [SalesLT].[vGetAllCategories];

No three-part names needed – the external table handles the cross-database routing.

Cleanup reference

If you need to tear things down, drop in reverse order:

-- Source database
DROP EXTERNAL TABLE [SalesLT].[vGetAllCategories];
DROP EXTERNAL DATA SOURCE [sqldb-dev-001];
DROP DATABASE SCOPED CREDENTIAL [sqldb-dev-001-credential];

-- Target database
DROP USER [sqldb-dev-001-identity];

Notes

• Elastic Query has some performance overhead compared to local queries – it’s not ideal for high-frequency joins
• The contained user only needs db_datareader for SELECT access. Add db_datawriter if you need write access
• Store the password in Key Vault, not in code or config
• When rotating the password, update both the contained user and the scoped credential