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

 

DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...
featured-2302
How to easily parse JSON in Power BI Desktop
Sometimes you want to use JSON data in Power BI. And sometimes Power BI doesn’t exactly do what you want...
header-2301
Power BI reports as monitoring tool
Who has not created monitoring reports or beautiful dashboards in Power BI? Would it not be great to show off...
your-modernization-journey-starts-now-header
Your Modernization Journey starts now
Recently Microsoft introduced SQL server 2022. We wrote this post to make sure you won’t be missing out on all...