Azure Sql database check with EnsureDatabase fails when using managed identity

[ArnaudB88]

Is there an existing issue for this?

• I have searched the existing issues

Library Version

6.0.16

What happened?

Hi,

I just managed to get dbup working with a connection string to an Azure SQL database using ActiveDirectoryManagedIdentity authentication.
There are 2 things I want to point out.

1. Checking if the database exists, still does not work:

var sqlBuilder = new SqlConnectionStringBuilder(options.ConnectionString);
EnsureDatabase.For.SqlDatabase(sqlBuilder.ConnectionString);

I get the same exception as if I would use the DeployChanges.To.SqlDatabase() to get a builder:
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource.

Looking at the internal code, it sets up a SqlConnection which is different from the connection set up by the AzureSqlConnectionManager.

For reference, my full code to check database existance and return a builder is

static UpgradeEngineBuilder GetBuilder(Options options)
{
    var sqlBuilder = new SqlConnectionStringBuilder(options.ConnectionString);

    if (sqlBuilder.DataSource.Contains("database.windows.net") && sqlBuilder.Authentication == SqlAuthenticationMethod.ActiveDirectoryManagedIdentity)//Azure SQL with Managed Identity authentication
    {
        //Checking if database exists with EnsureDatabase does not work Azure Managed Identity connection
        EnsureDatabase.For.SqlDatabase(sqlBuilder.ConnectionString, DbUp.SqlServer.AzureDatabaseEdition.Basic);

        sqlBuilder.Authentication = SqlAuthenticationMethod.NotSpecified;//Remove authentication method since AzureSqlDatabaseWithIntegratedSecurity sets an access token

        return DeployChanges.To.AzureSqlDatabaseWithIntegratedSecurity(sqlBuilder.ConnectionString);
    }
    else
    {
        EnsureDatabase.For.SqlDatabase(sqlBuilder.ConnectionString);

        return DeployChanges.To.SqlDatabase(sqlBuilder.ConnectionString);
    }
}

2. The authentication part
A second thing I want to point out is that I manually need to remove the Authentication part of the connection string by setting it to NotSpecified. Is it possible to let the AzureSqlConnectionManager perform this action? If I don't clear the Authentication part, I get the exception:
System.InvalidOperationException: Cannot set the AccessToken property if 'Authentication' has been specified in the connection string.

Thanks!

[yooakim]

Just out of curiosity, have you tried using ActiveDirectoryDefault instead? @ArnaudB88

When you use ActiveDirectoryManagedIdentity you need to specify the userid as the client id of the user identity you want to use, as far as I know.

More details here.

[ArnaudB88]

Hi @yooakim ,

I tried the ActiveDirectoryDefault and indeed it works, both for checking if the db exists and executing updates.
I changed my code to:

static UpgradeEngineBuilder GetBuilder(Options options)
{
    var sqlBuilder = new SqlConnectionStringBuilder(options.ConnectionString);

    if (sqlBuilder.DataSource.Contains("database.windows.net") && sqlBuilder.Authentication == SqlAuthenticationMethod.ActiveDirectoryManagedIdentity)//Azure SQL with Managed Identity authentication
    {
        sqlBuilder.Authentication = SqlAuthenticationMethod.ActiveDirectoryDefault;//Use this type instead
    }

    EnsureDatabase.For.SqlDatabase(sqlBuilder.ConnectionString);
    return DeployChanges.To.SqlDatabase(sqlBuilder.ConnectionString);
}

(I keep using the same connectionstring with ActiveDirectoryManagedIdentity since the connection string is used by all applications running in Azure)

fyi ActiveDirectoryManagedIdentity with specification of UserId did not work. I am executing dbup in a DevOps pipeline, running inside an Azure cli (so running under a DevOps service connection to Azure).