SQL Server Health Check with Managed Identity (Azure Access Token)

[johnayoub]

The current API doesn't allow connecting to Azure SQL Server using managed identity and an access token!

Any plans to add this feature or any suggested workaround in the meantime?

[unaizorrilla]

Hi,

Interesting!! Let me check this. Of course contributions are open!! 👍

[inkel]

I've similar issues with this and ended up creating a custom SQL Server health check: we use a custom connection manager, so this health check uses that, but while working on that I thought about how this feature could be added to AspNetCore.HealthChecks.SqlServer.

There's a tutorial named Secure Azure SQL Database connection from App Service using a managed identity that does the following once the connection is created:

var conn = (System.Data.SqlClient.SqlConnection)Database.GetDbConnection();
conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

In this case conn could be replaced with conn = SqlConnection(_connectionString) in src/HealthChecks.SqlServer/SqlServerHealthCheck.cs, however that way of getting the access token is only for Azure managed SQL Databases. At my company we get the access token in a different way:

var credential = new ClientCredential(clientId, clientSecret);
var authenticationContext = new AuthenticationContext(databaseAuthority);
var authenticationResult = await authenticationContext.AcquireTokenAsync(target, credential);
var accessToken = authenticationResult.AccessToken;

Given that there might be many different ways to specify the value of SqlConnection.AccessToken, I think that the easiest way to implement this is to change the SqlServerHealthCheck constructor to either accept an optional string accessToken = null value, or an overloaded that accepts an SqlConnection instead.

I think the simplest is definitely adding an additional parameter, however, adding a constructor that accepts SqlConnection can be useful when combined with dependency injection.

Either way, I'll gladly send a PR for this if you like and we agree on how to implement it.

[Nisden]

@inkel What about adding an Action<SqlConnection> beforeOpen? That would allow users to set the AccessToken, and would be more future proof.

[inkel]

@Nisden that sounds great! Would you like me to submit a PR for this?

[Nisden]

@inkel If you got the time for it, it would be awesome. Otherwise I could look into it.

[inkel]

@Nisden hey, sorry for the delay in replying.

Yeah, I think I can manage to make the time this or next week. Would that work?

[ghost]

I could really do with this functionality as well. I will raise a PR today if that is alright? I think I will go for the beforeOpen Action approach since that seems more flexible.

[inkel]

@JoeSainsburys thank you! I really hope they approve it soon and is available in the next release.