OLE DB Driver for SQL Server not working with Arc Managed Identity

Question

I have a Windows 2019 SQL 2016 SP3 server (in AWS), within which I would like to create a Linked Server connecting to an Azure SQL Database. The Windows server is enrolled with Azure Arc, and so has a Managed Identity associated with it maintained by the Arc agent.

I would like to use the Arc managed identity for authentication of the Linked Server, but so far cannot successfully authenticate with the Linked Server test.

I can successfully authenticate using the "Microsoft.Data.SqlClient v5.0" .net package using a test tool, so I know the Arc Managed Identity is correctly permissioned against the Azure SQL database.

I have tried many different iterations of the linked server configuration, but I think this is the one thats got the closest (taken from scenario H - https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-ver16) :

EXEC master.dbo.sp_addlinkedserver @Testta = N'MyLinkedServer', @srvproduct = N'', @provider = N'MSOLEDBSQL', @provstr = N'Server=sqs-xxxxx.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

The error I get is:

I set up a wireshark trace and can see the requests for an access token being sent to:
GET
http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https:%2F%2Fdatabase.windows.net%2F

(The reason a the 404 response is seen is because AWS also use the same IP address for their metadata service, which of course cannot service this request).

But although this is the correct path for Azure VM with Managed Identities, when using Arc the IP should be localhost/127.0.0.1 & TCP port is dynamic - and this should be derived from the environment variable "IDENTITY_ENDPOINT".
https://learn.microsoft.com/en-us/azure/azure-arc/servers/managed-identity-authentication

E.g.:

PS C:\Users\xxxx> $env:IDENTITY_ENDPOINT http://localhost:40342/metadata/identity/oauth2/token

So I think the issue is that the OLE DB Driver for SQL Server (I've tried 18.5 and 19) is not checking for this environment variable before sending to 169.254.169.254:80.
Is it expected that Arc managed identities are supported? If not, could support be added?

Thanks,

Peter

Answer 1

I can't say whether this is expected to work or not. But I can't recall that I've read anything about Arc Managed Identity, so it would only if it falls under any of the other.

As for the environment variable, I think you would need to set for System an d not your user. I would also expect that you would need to restart SQL Server for the SQL Server process to be able to pick it up.