Unable to use Microsoft.Data.SqlClient as part of AddIn project inside SSMS

[adrijan1976]

Code

Have simple code used to connect to SQL Server

var a = "Data Source=XXXX\\SQL2016;Integrated Security=True;Encrypt=False;";
Microsoft.Data.SqlClient.SqlConnection conn = new Microsoft.Data.SqlClient.SqlConnection(a);
conn.Open();

To reproduce

When running this code in context of SSMS Addin receiving the following error

The type initializer for 'Microsoft.Data.SqlClient.SqlAuthenticationProviderManager' threw an exception.
Type: System.TypeInitializationException
	Stack trace
	   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 477
	   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnectionFactory.cs:line 143
	   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 163
	   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 943
	   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 2000
	   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 1412
	   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 1296
	   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 354
	   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionInternal.cs:line 766
	   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionClosed.cs:line 71
	   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1946
	   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1934
	   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1495
	   at Microsoft.Data.SqlClient.SqlConnection.Open() in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1466
	   at ApexSQL.Common.Db.BaseConnection.OpenConnection()
	   at ApexSQL.Common.MsSQL.Db.MsSQLConnection.Connect()
	   at ApexSQL.Common.Extensibility.Item.MvoCheck(EFCurrentSession cs)
	   at ApexSQL.Common.Extensibility.Item.RaiseOnClick()
	Inner exception: Unable to cast object of type 'System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection' to type 'Microsoft.Data.SqlClient.SqlAuthenticationProviderConfigurationSection'.
	Type: System.InvalidCastException
		Stack trace
		   at Microsoft.Data.SqlClient.SqlAuthenticationProviderManager..cctor() in H:\tsaagent2\_work\11\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlAuthenticationProviderManager.cs:line 31

Problematic code inside Microsoft.Data.SqlClient is the following as it try to load section SSMS.exe.config which is

(File: SqlAuthenticationProviderManager.cs Line : 30)

try
{
        configurationSection  = (SqlAuthenticationProviderConfigurationSection)ConfigurationManager.GetSection(SqlAuthenticationProviderConfigurationSection.Name);
}
catch (ConfigurationErrorsException e)
{
       // Don't throw an error for invalid config files
      SqlClientEventSource.Log.TraceEvent("Unable to load custom SqlAuthenticationProviders. ConfigurationManager failed to load due to configuration errors: {0}", e);
}

<section name="SqlAuthenticationProviders" type="System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

Help needed

Is there any way to override or do something to avoid this behavior.

Thanks in advance!

[ErikEJ]

Bug fixed in 2.0.1-preview1

[adrijan1976]

@ErikEJ Thanks for the answer.
I am using the latest build.

The problem is that I am not in control of Sssms.exe.config in order to add/remove something. By default the Ms.Data.SqlClient loads app.config section which is not mine and i don't want to use it.

[ErikEJ]

I guess you have to use the legacy client or wait for an update.

You are aware that SSMS extensions are not really supported by Microsoft?

[adrijan1976]

Thanks for answer.

Yes.

[cheenamalhotra]

@adrijan1976 have you tested with latest preview version v2.1.0-preview1?

The fix in PR #702 was released in this version on Friday.

[adrijan1976]

@cheenamalhotra Thanks i have tested now, and conform that works with preview.

I will close the issue.