This trick is about running xDM Dashboard on Azure SQL Database.  Customer is creating workflows for product data authoring.  They want to see a dashboard that shows the workflow status and can drill down into what products are being created within the workflows.  Workflow status are stored in the "Semarchy Repository" database while Product details are stored in "Data Location" database.  They need to build a cross-database query within the xDM Dashboard.


If it's an on-premise SQL Server or Azure SQL Managed Instance deployment, simply you can access another database in SQL using the Three-part Naming like [Database Name].[Schema Name].[Object Name].  But Azure SQL Database doesn't support the Three-part Naming Query.  It's listed in the comparison between Features of SQL Database and SQL Managed Instance.


One workaround of this is to build a cross-database-query between the "Semarchy Repository" and "Data Location" databases.  Here are the SQLs that we ran against the "Semarchy Repository" database.


-- protects private keys
CREATE MASTER KEY; -- create master key
GO
 
-- credential maps to a login or contained user used to connect to remote database 
CREATE DATABASE SCOPED CREDENTIAL CrossDbCred -- credential name
WITH IDENTITY = 'semarchy_dloc',                    -- login or contained user name
SECRET = 'YourSecret';                    -- login or contained user password
GO
 
-- data source to remote Azure SQL Database server and database
CREATE EXTERNAL DATA SOURCE SemarchyDloc
WITH
(
    TYPE=RDBMS,                           -- data source type
    LOCATION='yourservername.database.windows.net', -- Azure SQL Database server name
    DATABASE_NAME='semarchy_dloc',         -- database name
    CREDENTIAL=CrossDbCred                -- credential used to connect to server / database  
);
GO
 
-- external table points to table in an external database with the identical structure
CREATE EXTERNAL TABLE [dbo].[SA_PRODUCT]
(
  B_LOADID decimal(38,0),
  ID decimal(38,0),
  B_AUTHORINGTYPE nvarchar(30),
  B_CLASSNAME nvarchar(128),
  B_ERROR_STATUS nvarchar(30) ,
  B_ORIGINALBATCHID decimal(38,0) ,
  B_OPERATIONID nvarchar(128) ,
  B_DUPSOPERATIONID decimal(38,0) ,
  B_DUPSMERGETARGET decimal(38,0) ,
  B_DELETETYPE nvarchar(30) ,
  B_DELETEDATE datetime2(6) ,
  B_DELETEAUTHOR nvarchar(128) ,
  B_DELETEOPERATION nvarchar(128) ,
  B_DELETEPATH nvarchar(4000) ,
  B_COPIEDFROM decimal(38,0) ,
  B_CREDATE datetime2(6) ,
  B_UPDDATE datetime2(6) ,
  B_CREATOR nvarchar(128) ,
  B_UPDATOR nvarchar(128) ,
  PRODUCT_ID nvarchar(18) ,
  PRODUCT_DESCRIPTION nvarchar(128) ,
)
WITH (DATA_SOURCE = [SemarchyDloc],  -- data source 
      SCHEMA_NAME = 'dbo',           -- external table schema
      OBJECT_NAME = 'SA_PRODUCT'       -- name of table in external database
    );
GO

If you run "SELECT * FROM SA_PRODUCT" on "Semarchy Repository" now, you'll hit a firewall issue


SQL Error [46832] [S0003]: An error occurred while establishing connection to remote data source: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open server 'yourservername' requested by the login. Client with IP address 'XX.XXX.XXX.X' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect.

Finally, configure the Firewall Rules enabling the client IP address "XX.XXX.XXX.X" to access the Azure SQL Server, then everything will work fine!