kohera-logo-regular.svg

How To: Azure SQL DB Cross database queries using synonyms

When helping a fellow dba on dba.stackexchange.com, I came across an interesting subject. What about synonyms in Azure SQL DB? We know that synonyms that stay in the current database scope work, but how do we get cross db synonyms to work?

Cross database synonyms are not directly supported in Azure SQL DB (Workaround in next step)

I created two Azure SQL DB’s to prove this:

Error when trying to run this query at the stackoverflow database:

Use StackOverflow
CREATE SYNONYM Test  
FOR [OtherDB].dbo.test;  
GO  

Result:

Msg 40515, Level 15, State 1, Line 16 Reference to database and/or server name in ‘OtherDB.dbo.test’ is not supported in this version of SQL Server.

Show me the money: the workaround

What you can do however, is create an external table for that secondary database as long as it is in the same server, and create a synonym for the external table as to facillitate cross db queries!

Step 1: Create a master key in database1 (stackoverflow)

CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'STRONG123P@SSWORD'

Step 2: Create a database scoped credential (naming was a bit sloppy, apologies).

  Important here is that you use the exact! password for the account, vertora in this example.

CREATE DATABASE SCOPED CREDENTIAL nah WITH IDENTITY = 'vertora',
SECRET = 'MyAdminAccountStrongP@ssword';
;

Step 3: Create an external data source to the other database

CREATE EXTERNAL DATA SOURCE OtherDB
WITH
(
    TYPE=RDBMS,
    LOCATION='vertoraperformancetuning.database.windows.net',
    DATABASE_NAME='OtherDB',
    CREDENTIAL= nah
);

Step 3.1: Query executed on the other database (testing purposes)

Step 4: create an external table

CREATE EXTERNAL TABLE dbo.test(
id int ,
value nvarchar(255)
)

Step 5: Create a synonym for the table

Use StackOverflow CREATE SYNONYM Test123 FOR dbo.test;
GO

Step 6: Succes!

select * from dbo.Test123

 

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
2401-under-memory-pressure-featured-image
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
2402-fabric-lakehouse-featured-image
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...