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