Cross Database Query in Azure SQL Database

Azure, SQL Database, SQL Server

In modern cloud architectures, it’s common to distribute data across multiple Azure SQL Databases. While this promotes modularity and scalability, it can pose challenges when you need to access data across these databases. Fortunately, Azure SQL’s external tables feature allows for seamless cross-database querying without data duplication. This feature is also known as Elastic Query in Azure SQL database.This guide walks you through setting up cross database query in Azure SQL to reference a table from another Azure SQL Database, ensuring secure and efficient data access.

Use Case – Imagine this scenario:

  • You have a main transactional database named app-db hosted on Azure SQL Server app-db.database.windows.net.
  • You also have a reference or lookup database named source-db, hosted on a separate Azure SQL Server: source-server.database.windows.net.
Cross database query in Azure SQL database

You want your app-db to query the table dbo.external_table from the source-db — without importing or copying the data. The goal is to enable live, read-only access to shared reference data across databases in a secure and manageable way.

Prerequisites
Before diving into the setup, ensure the following:
1. Azure SQL Databases: Both the source and target databases should be Azure SQL Databases.
2. Same Region: Ensure both databases are in the same Azure region to minimize latency.
3. User Credentials: A SQL user say (ext_table_user) must exist in the source database with the necessary permissions to access the target table.

Why Create Separate user in the Source Database?

The ext_table_user is a SQL user in the source database that the target database uses to authenticate and access data. This user must have the necessary permissions (e.g., SELECT) on the target table.

Creating a dedicated user for external access enhances security by allowing you to:

  • Limit Permissions: Grant only the necessary permissions to this user.
  • Monitor Access: Track and audit activities performed by this user.
  • Revoke Access Easily: If needed, you can revoke this user’s access without affecting other users.

Fow these steps to how to grant access to Azure SQL database.

How to Setup Cross Database Access in Azure SQL

Follow the below quick steps to setup cross database access in Azure SQL database.

Step 1: Create a Master Key in the Target Database

The master key is essential for encrypting credentials used in external data sources.

-- Execute in the target database

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere';

Skip this if SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##' already returns a row.

Step 2: Create a Database Scoped Credential

This credential will be used to authenticate against the source database.

-- Execute in the target database

CREATE DATABASE SCOPED CREDENTIAL ext_table_user
WITH IDENTITY = 'ext_table_user',
     SECRET = 'ActualPasswordOfExtTableUser';

Step 3: Define the External Data Source

This step establishes a connection to the source database.

-- Execute in the target database

CREATE EXTERNAL DATA SOURCE LinkedAzureSQLDB
WITH (
    TYPE = RDBMS,
    LOCATION = 'source-server.database.windows.net',
    DATABASE_NAME = 'source-db',
    CREDENTIAL = ext_table_user
);

Ensure: Replace 'source-server.database.windows.net' and 'source-db' with your actual server and database names.

Step 4: Create the External Table

Now, define the external table in the target database to reference the table in the source database.

-- Execute in the target database

CREATE EXTERNAL TABLE dbo.external_table (
    [ID] NUMERIC(18, 0) NOT NULL,
    [list_id] INT NOT NULL,
    [type_id] INT NOT NULL,
    [description] VARCHAR(50) NULL
)
WITH (
    DATA_SOURCE = LinkedAzureSQLDB,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'external_table'
);

Note: The schema and object names should match those in the source database.

Step 5: Query the External Table

With the setup complete, you can now query the external table as if it were local:

-- Execute in the target database

SELECT TOP 10 * FROM dbo.external_table;

Performance Tip: Monitor query performance, as cross-database queries may have higher latency.

Pro tips:
1. Use Managed Identities: For enhanced security, consider using Azure Managed Identities instead of SQL authentication.
2. Monitor Performance: Regularly monitor the performance of cross-database queries to ensure they meet your application’s requirements.
3. Secure Credentials: Store sensitive information, like passwords, securely using Azure Key Vault.
4. Elastic query feature in Azure SQL is still in preview.

By following this guide, you can efficiently set up cross databse query in Azure SQL, enabling seamless cross-database querying while maintaining security and performance.

See more

Kunal Rathi

With over 13 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.

Shopping Cart
Scroll to Top