Service principal authentication with Azure SQL

[sethdorris]

Is there a way to set up the config to authenticate with a service principal to Azure SQL? Right now looks like the only method is a raw connection string passed into config ... quick sample code

            var sqlOptions = new SqlServerStorageOptions
            {
                CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                QueuePollInterval = TimeSpan.Zero,
                UseRecommendedIsolationLevel = true,
                DisableGlobalLocks = true
            };

            GlobalConfiguration.Configuration
                .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
                .UseSimpleAssemblyNameTypeSerializer()
                .UseRecommendedSerializerSettings()
                .UseUnityActivator(UnityConfig.Container)
                .UseSqlServerStorage(ConfigurationManager.ConnectionStrings["DbContext"].ToString(), sqlOptions);

[fedahl]

I also had issues with using a Service Principal in the connection string, I kept getting an error System.ArgumentException: Keyword not supported: 'authentication'.

Looking at other issues opened here I found this thread that talks about wanting to use Managed Identity to connect to the Azure SQL database. I tried out the same suggestion as the first response to this thread using my Service Principal and it worked.

Call to add Hangfire:

var connectionString = configuration.GetDbConnectionString();
services.AddHangfire(conf =>
    conf.UseSqlServerStorage(() => new Microsoft.Data.SqlClient.SqlConnection(connectionString),
        new SqlServerStorageOptions
        {
            // Add the options you like here
        }));

The connection string would then look something like this:

Server={SQLServer};Initial Catalog={SQLDatabase};Persist Security Info=False;
Authentication=Active Directory Service Principal;User ID={clientId};Password={clientSecret};
MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

I haven't tried running this in production yet but it runs fine for me locally. Hope this help and thanks to https://github.com/orjan for the original answer!

[RbVs]

My App is hosted on Azure App Service connected to AzureSQL DB using the Authentication=Active Directory Device Code Flow; to authenticate the app against the database. I had the same issue to run hangfire. After a few hours of searching and trying, i came up with the following solution using the code @fedahl posted.

The default connection string looks like: Server={ServerName};Authentication=Active Directory Device Code Flow; Database={DatabaseName};

My connection string for hangfire looks like: Server={ServerName};Authentication=Active Directory Managed Identity;Database={DatabaseName};

So far I haven't found any problems running on prod.

[sven5]

As of today, is there any other solution to this?

Our connection string looks like this "Server=tcp:xxxxx.database.windows.net,1433;Database=database;Authentication=Active Directory Default;TrustServerCertificate=True;"

This is working correctly outside Hangfire. I assume the underlying problem is that Hangfire uses System.Data.SqlClient instead of Microsoft.Data.SqlClient

[odinserj]

In Hangfire 1.7.X it is possible to use Microsoft.Data.SqlClient package by using an explicit connection factory as in the following example:

GlobalConfiguration.Configuration
    .UseSqlServerStorage(
        () => new Microsoft.Data.SqlClient.SqlConnection(@"Server=.\;Database=Hangfire.Sample;Trusted_Connection=True;"));

In Hangfire 1.8.X, Microsoft.Data.SqlClient will be used by default if installed into the target project, please see https://docs.hangfire.io/en/latest/configuration/using-sql-server.html#installation for detalis.