System.Data.SqlClient used in Azure even though Microsoft.Data.SqlClient is used locally

I'm managing an old ASP.NET MVC application built with .NET 4.6.2 and Entity Framework (yes I know it should be updated). Due to changes in the way it and its database is hosted in Azure, I've been tasked to change the SQL provider from System.Data.SqlClient to Microsoft.Data.SqlClient. I have updated and installed a bunch of NuGet packages as well as made configuration changes.

This all seem to work fine when I run it locally. However, when deployed to Azure, it does not work. And what seems strange to me is that I can see in the stack trace of the exception that System.Data.SqlClient is still used. When I provoke the application to get the same exception locally, I see that Microsoft.Data.SqlClient is used as expected.

How can that be? That my local environment and Azure is using different packages when the code is the same?

The only thing that I know I different between the two is the connection string.

The exception occurs when connection string is parsed. These are the last lines in the stack trace:

Locally:

[ArgumentException: Invalid value for key 'Authentication'.]
Microsoft.Data.Common.DbConnectionStringBuilderUtil.ConvertToAuthenticationType(String keyword, Object value) +393
Microsoft.Data.SqlClient.SqlConnectionString.ConvertValueToAuthenticationType() +109
Microsoft.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +2303
Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +38
Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +217
Microsoft.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) +53
Microsoft.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +374
System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +98
System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.SetConnectionString(DbConnection connection, DbConnectionPropertyInterceptionContext`1 interceptionContext) +514

On Azure:

[ArgumentException: Invalid value for key 'Authentication'.]
System.Data.Common.DbConnectionStringBuilderUtil.ConvertToAuthenticationType(String keyword, Object value) +412
System.Data.SqlClient.SqlConnectionString.ConvertValueToAuthenticationType() +118
System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +3042
System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +38
System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +217
System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) +68
System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +167
System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +98
System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.SetConnectionString(DbConnection connection, DbConnectionPropertyInterceptionContext`1 interceptionContext) +494

I believe the reason for the exception is that the new provider must be used for the connection string to be working in Azure. But my question is why is the old provider used in Azure?

Solution

I encountered the same issue while using the default connection strings. It turns out that the provider name is overridden by default.

The solution that worked for me was to create a custom DbConfiguration and register it in Application_Start():

DbConfiguration.SetConfiguration(new AppServiceConfiguration());

Then, define the custom configuration class:

public class AppServiceConfiguration : MicrosoftSqlDbConfiguration
{
    public AppServiceConfiguration()
    {
        SetProviderFactory("System.Data.SqlClient", Microsoft.Data.SqlClient.SqlClientFactory.Instance);
        SetProviderServices("System.Data.SqlClient", MicrosoftSqlProviderServices.Instance);
        SetExecutionStrategy("System.Data.SqlClient", () => new MicrosoftSqlAzureExecutionStrategy());
    }
}

For more details, refer to the known issues section:
Microsoft.EntityFramework.SqlServer - Known Issues

This should ensure that the correct provider is used consistently.