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?

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?

• azure
• entity-framework

Share Improve this question Follow edited Jan 29 at 17:50 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 29 at 14:06 haagelhaagel 2,7281010 gold badges3838 silver badges5656 bronze badges 5

• 1 EntityFramework depends on System.Data.SqlClient. It shouldn't be using Microsoft.Data.SqlClient at all. How did you add Microsoft.Data.SqlClient in the first place and how did you force EF to use it - if it did? The error complains about a bad connection string key. The stack shows an attempt to read and parse that bad string, not actually use the library, If System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch` tries to find the target type by name, it's only chance (or rather, accident) that used MDS – Panagiotis Kanavos Commented Jan 29 at 14:30
• What kind of application are you using? If the runtime environment is provided by Azure, it won't include MDS. Even in a VM though, when you publish your application the old System.Data.SqlClient will always get included because it's a dependency of EntityFramework itself. – Panagiotis Kanavos Commented Jan 29 at 14:31
• I'm using the NuGet package Microsoft.EntityFramework.SqlServer 6.5.1 (nuget./packages/Microsoft.EntityFramework.SqlServer). As I understand it uses Microsoft.Data.SqlClient rather than System.Data.SqlClient. – haagel Commented Feb 3 at 2:56
• That's a completely new package released less than 1 year ago, to allow the use of Microsoft.Data.SqlClient, but doesn't remove the dependency on System.Data.SqlClient. The package you loaded depends on EntityFramework which in turn depends on System.Data.SqlClient. If your application (or the Azure framework you use) tries to load eg SqlConnection dynamically, it could end up loading the System.Data.SqlClient class – Panagiotis Kanavos Commented Feb 3 at 8:11
• What kind of Azure project are you building? What libraries or framework do you use? And what's your actual code? Whether you can prevent the use of System.Data.SqlClient or not may depend on tweaking that framework. For example, you may be able to explicitly block System.Data.SqlClient from publishing. That won't help if the Azure runtime includes it though. – Panagiotis Kanavos Commented Feb 3 at 8:16

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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.

When you deploy code to azure, were all existing files/ddl are cleared? it could be that old dlls still on the app service (not cleaned).

Also check the zip or folder of build/published package locally to see if both System.Data and Microsoft.Data dll are actually both there.

Regarding The exception occurs when connection string is parsed, probably you mean `not parsed' so gives exception above? If local uses Microsoft.Data, the app service should too. check the difference in debug and release folder to see if all dlls are same.