How to use Hangfire in ASP.NET Core with Azure database and Active Directory Password authentication

Question

We're trying our first use of Hangfire (v1.7.19) in an ASP.NET Core WebApi application (.NET 5). Previously they've all been old school ASP.NET, and have worked without issue.

Hangfire packages used (per the Hangfire documentation) are Hangfire.Core, Hangfire.SqlServer and Hangfire.AspNetCore. We've also tried to just use the combined Hangfire package, but are having the same results.

Lifting directly from the code on that documentation page, in ConfigureServices we add

    services.AddHangfire(configuration => configuration
        .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
        .UseSimpleAssemblyNameTypeSerializer()
        .UseRecommendedSerializerSettings()
        .UseSqlServerStorage(connectionString), new SqlServerStorageOptions
        {
            CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
            SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
            QueuePollInterval = TimeSpan.Zero,
            UseRecommendedIsolationLevel = true,
            DisableGlobalLocks = true
        }));

which, at runtime, gives

System.ArgumentException
  HResult=0x80070057
  Message=Keyword not supported: 'authentication'.
  Source=System.Data.SqlClient
  StackTrace:
   at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms)
   at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
   at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
   at Hangfire.SqlServer.SqlServerStorage.<.ctor>b__6_0()
   at Hangfire.SqlServer.SqlServerStorage.CreateAndOpenConnection()
   at Hangfire.SqlServer.SqlServerStorage.UseConnection[T](DbConnection dedicatedConnection, Func`2 func)
   at Hangfire.SqlServer.SqlServerStorage.UseConnection(DbConnection dedicatedConnection, Action`1 action)
   at Hangfire.SqlServer.SqlServerStorage.Initialize()
   at Hangfire.SqlServer.SqlServerStorage..ctor(String nameOrConnectionString, SqlServerStorageOptions options)
   at Hangfire.SqlServer.SqlServerStorage..ctor(String nameOrConnectionString)
   at Hangfire.SqlServerStorageExtensions.UseSqlServerStorage(IGlobalConfiguration configuration, String nameOrConnectionString)

The value of connectionString is

Initial Catalog=XXX;Data Source=YYY;Authentication=Active Directory Password;UID=ZZZ;PWD=PPP"

This works fine with local SqlServer and LocalDb, but not with AzureDB. The connection string is exactly the one we're also using for EntityFrameworkCore 5 (in fact, the value is assigned from context.Database.GetConnectionString()).

I've read where there were issues with AzureDB and .NET Core, but those were resolved quite some time ago. Looking through the Hangfire.SqlServer package dependencies, I see where it uses System.Data.SqlClient, and current documentation for AzureDB use all refer to Microsoft.Data.SqlClient, which makes me think that the enhancements to support Active Directory Password authentication weren't made in System.Data.SqlClient but only in the newer Microsoft.Data.SqlClient package. If that's the case, I can put in a request that Hangfire replace its SqlClient package, but want to get confirmation before I do that.

Any ideas if this is indeed the case? Is there something we can do in the meantime to fix this instead?

Thanks in advance.

Answer 1

Hangfire IO GitHub Issue 1827

Pointers from Sergey Odinokov

As early as 1.7.8 there has been support to use an overload of UseSqlServerStorage that accepts a Func<DbConnection> instead of a connection string where you can use a connection factory to provide the newer Microsoft.Data.SqlClient.SqlConnection which supports the Authentication keyword.

Here is the related source code

You can use this like

 services.AddHangfire(config => config
       // other options you listed above removed for brevity
.UseSqlServerStorage(
          () => new Microsoft.Data.SqlClient.SqlConnection(<connection_string>)
          , new SqlServerStorageOptions() {...}
     )
);