Use SqlCacheDependency on an Azure SQL when connecting with Managed Identity

Question

I'm moving in Azure an existing application (C# & .NET 4.8.1) and I have this code:

SqlConnection con = new SqlConnection("connection_string_without_user_or_password")
con.AccessToken = GetToken(); // this provides a valid token, that works as expected
con.Open()

SqlCacheDependency sqlDependency = new SqlCacheDependency(con.Database, "my_table");

And I end up with this error:

Unable to connect to SQL database 'MyDatabase' for cache dependency polling.

and

[SqlException (0x80131904): Login failed for user ''.]

it somehow tries to use my connection string that has no user defined and it doesn't follow the Manage Identity connection.

Answer 1

The error you are getting is because the polling-based dependency mechanism used by SqlCacheDependency relies on SQL Server Agent or Service Broker and as per this Ms Documant the Service Broker is accessible in SQL server and SQL managed instance not in azure SQL.

When using Azure AD authentication (via Managed Identity or an access token), the SqlCacheDependency class does not natively support token-based authentication.

The workaround is either you can use SQL Authentication Instead of Azure AD Authentication If possible, switch to using SQL Authentication (username and password) for the SqlCacheDependency connection or you can use both approach at a sometime like you must use Azure AD authentication for your main application and SQL Authentication for the cache dependency, you can create a separate connection string specifically for SqlCacheDependency.