Add support for user-assigned managed identity connections

[ryan-adler]

Is there an existing issue for this?

• I have searched the existing open and closed issues

Description

While there seems to be support for System-Assigned MSI connection strings, when trying to use a user-assigned identity (ie. add "User Id=xxxxxxx-xxxx-xxx-xxxx" to the connection string), the deployment fails with the message:
Cannot set the AccessToken property if 'UserID', 'UID', 'Password', or 'PWD' has been specified in connection string.

What is the impact?

Only system-assigned MSI can be used in the SQL connection

[CSkjolden]

This would be very useful in our codebase.

It has a terrible workaround that could break at any time.

+1 from here

[droyad]

Can you share the code you are using? The connection string?

[DLPdev]

@CSkjolden what's your workaround?

[CSkjolden]

@DLPdev

I've lost the code during a branch cleanup since it was deemed too easy to break to be put into production.

But the idea was just creating a new IConnectionManager and give it a connection string and add a token. I had it fully up and working so I know it's possible.

Just follow this one here and inherit from DatabaseConnectionManager.

Then you can just create a new constructor where you input either credentials or a token from ManagedIdentityCredential to insert in the connection.

The method ".SqlDatabase" is then able to accept that as the argument.

[DLPdev]

@CSkjolden thank you, I'll give it a try.

[DLPdev]

For those of you that need it, a workaround I just tested goes like his:

public class AzureSqlConnectionManager : DatabaseConnectionManager
{
    public AzureSqlConnectionManager(string connectionString, string credential)
            : base(new DelegateConnectionFactory((log, dbManager) =>
            {
                var conn = new SqlConnection(connectionString);

                conn.AccessToken = credential;

                if (dbManager.IsScriptOutputLogged)
                    conn.InfoMessage += (sender, e) => log.LogInformation($"{{0}}", e.Message);

                return conn;
            }))
    { }
    public override IEnumerable<string> SplitScriptIntoCommands(string scriptContents)
    {
        var commandSplitter = new SqlCommandSplitter();
        var scriptStatements = commandSplitter.SplitScriptIntoCommands(scriptContents);
        return scriptStatements;
    }
}

That class can then be used to authenticate against Azure like this:

 var credential = new DefaultAzureCredential();
            var tokenRequestContext = new TokenRequestContext(new[] { "https://database.windows.net/.default" });
            var tokenRequestResult = credential.GetToken(tokenRequestContext);
            var token = tokenRequestResult.Token;

var connectionManager = new AzureSqlConnectionManager(connectionString, token);

Lastly that connectionManager can be passed to DeployChanges.To.SqlDatabase(connectionManager)