Database-to-Database queries using Managed Identity between Azure SQL Databases

Question

I'm currently trying to answer a problem that can only be answered by combining the datasets of two different Azure SQL databases (different servers, if that matters).

When using user+password authentication, there was a way to do cross-database queries like this (Azure SQL Database Elastic Queries):

CREATE DATABASE SCOPED CREDENTIAL RemoteCredential WITH
    IDENTITY = '<remote database user name>',
    SECRET = '<remote database user password>'

CREATE EXTERNAL DATA SOURCE RemoteDatabase WITH (
    Location = '<database server URL>',
    DATABASE_NAME = '<database name>',
    CREDENTIAL = RemoteCredential,
    TYPE = RDBMS
)

CREATE EXTERNAL TABLE [dbo].[RemoteTable] (
    <Remote table definition>
)

SELECT TOP(1) * FROM [RemoteTable]

That worked very well before but we have since migrated to using only managed-identity logins, and user + password authentication is no longer an option.

I've found below snipped to change the credential for managed identity in the context of accessing Azure Storage Accounts here:

CREATE DATABASE SCOPED CREDENTIAL RemoteCredential
    WITH IDENTITY = 'Managed Identity'

But this results in the following error message:

Msg 33047, Level 16, State 5, Line 47
Fail to obtain or decrypt secret for credential 'RemoteCredential'.

I've also tried to provide my personal username or the source database server's name, but with the same result.

Some more details:

• Both database servers are part of the same tenant and subscription
• I've enabled system-assigned identity on the source database server that I am querying.
• I've also created an external source user in the target database for the use with managed identity and granted it the required roles.
• My user has the required permissions on both databases.
• Access with managed identity from my Management Studio works fine for both databases.

The final solution would have to work with Azure SQL databases in Azure China, but I would be grateful for a solution in Azure Global as well.

My current assumption is that managed identity authentication towards other Azure SQL databases from within a SQL query is not yet supported. But maybe someone else has found a way to make this work.

Answer 1

While not stated anywhere officially, it does seem that Elastic Queries with Managed Identity are not supported as of now.

If it will be when you read this, please let me know. Thanks!