DEV Community

Andrew Elans
Andrew Elans

Posted on

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

#database #sql #azure

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.

Prerequisites

Elastic Query only supports SQL authentication - Entra ID (AAD) and Managed Identity are not supported for cross-database credentials.

If your server has Microsoft Entra ID only authentication enabled, you'll need to disable it:

Azure Portal → SQL Server → Settings → Microsoft Entra ID → uncheck Support only Microsoft Entra authentication for this server.

This is a server-level setting that applies to all databases. If AAD-only is a company policy, there is a workaround to cross-query dbs with Power Automate - I'll cover that in a separate post.

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 = '<password-from-keyvault>';

ALTER ROLE db_datareader ADD MEMBER [sqldb-dev-001-identity];
Enter fullscreen mode Exit fullscreen mode

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##';
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password-from-keyvault>';
Enter fullscreen mode Exit fullscreen mode

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 = '<password-from-keyvault>';
Enter fullscreen mode Exit fullscreen mode

Verify it was created:

SELECT name, credential_identity
FROM sys.database_scoped_credentials;
Enter fullscreen mode Exit fullscreen mode

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]
);
Enter fullscreen mode Exit fullscreen mode

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'
);
Enter fullscreen mode Exit fullscreen mode

9. Test it 

SELECT * FROM [SalesLT].[vGetAllCategories];
Enter fullscreen mode Exit fullscreen mode

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];
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)