Using Managed Identity with Azure SQL using a managed Identity Delegate

[superuhrich]

I love Quartz.Net. I am trying to make use of the managed identity in Azure SQL for my API where Quartz lives. I have made my own IDb Provider, however whenever I try to fire up my solution, I get the error of "Quartz.JobPersistenceException: COuldnt recover jobs: Must declare teh scalar variable "@". I am unsure what I am doing wrong here. Here is my process so far.

Starting with what works, Then I will show what I have built to try and get the Managed Identity to work and see where it fails.

Here is my configuration that works, this is just straight into a local DB.

This works fine. Then, the next logical step is to try and use my own driverDelegate, but connect to the same database (no managed identity in this case) SO I have changed the settings to this

Where this is the managedIdentityDelegate, again, this time just trying to connect to the local database.

    `using System.Configuration;

using System.Data;
using System.Data.Common;
using Azure.Core;
using Azure.Identity;
using System.Data.SqlClient;
using Microsoft.Data.SqlClient;
using Quartz.Impl.AdoJobStore.Common;
using TraxIntegrationHub.Utilities.IOC;

namespace TraxIntegrationHub.Utilities.Quartz;

[TransientRegistration]
[RegisterAsConcrete]
public class AzureManagedIdentityDelegate : IDbProvider
{

public string? _configurationConnectionString { get; set; }
public string? _environment { get; set; }

public AzureManagedIdentityDelegate() {
    _configurationConnectionString = GlobalConfiguration.Configuration.GetConnectionString("IntegrationHubDb");
    _environment = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");
}


public void Initialize()
{
    Console.WriteLine($"Environment: {this._environment}");
    Console.WriteLine($"Connection String: {this._configurationConnectionString}");
    // Do your initialization here, such as setting up a connection string, etc.
}

public DbCommand CreateCommand() {
    return new SqlCommand();
}

public DbConnection CreateConnection() {
    
    SqlConnection connection = new SqlConnection();

    if (this._environment != "Local") {
        // Initialize a token credential using managed identity
        var tokenCredential = new DefaultAzureCredential();

        // Get access token
        AccessToken accessToken = tokenCredential.GetToken(
            new TokenRequestContext(new[] { "https://database.windows.net/.default" }));
        
        // Set the access token obtained through managed identity
        connection.AccessToken = accessToken.Token;
        
    }
    
    // Set the connection string
    connection.ConnectionString = this._configurationConnectionString;
    


    // Return the connection
    return connection;
}

public void Shutdown()
{
    // Perform any cleanup needed when the provider is being shut down
}

string IDbProvider.ConnectionString { get; set; }

public DbMetadata Metadata
{
    get
    {
        // Return a DbMetadata object that describes the capabilities of your database
        return new DbMetadata()
        {
            AssemblyName = "System.Data.SqlClient",
            ParameterNamePrefix = "@",
            ConnectionType = typeof(SqlConnection),
            CommandType = typeof(SqlCommand),
            ParameterType = typeof(SqlParameter),
            ExceptionType = typeof(SqlException),
            BindByName = false,
            ParameterDbType = typeof(SqlDbType)
 
        };
    }
}

public string ConnectionString
{
    get
    {
        // Return the connection string that should be used to connect to the database
        return this._configurationConnectionString;
    }
}

public string DataSource
{
    get
    {
        // Return the name of the data source (such as the server name)
        return "";
    }
}

public bool SupportsBatching
{
    get
    {
        // Return whether or not your database supports batch SQL operations
        return true;  // or false, depending on your database
    }
}

}
`

So I dont know where my settings are wrong, or what is happening. But really looking for some advice here, been pounding my head through the wall on this one.

[kamalpreetk701]

@superuhrich were you able to make it working ?

[superuhrich]

No, I never did, ended up not using the driver delegate, and just a standard connection string. Though that was about a year ago and I don’t know if Quartz made it work.

I also have since used azure function apps with cron timers in place of Quartz, it is a little more robust and works natively in azure much better.

[lahma]

Anyone having access to managed identity based database is welcome to create a PR. Maybe for v4 (main) that would make IDbProvider more async (ValueTask) and allowing post-processing like setting such credentials.

[kamalpreetk701]

@lahma , does it means sql acess using managed identity is not supported in the latest version 3.9.0?

[lahma]

Well it doesn't seem to work out of the box at least what I've understood.