Sqlclient: app.config breaks the library

[yorek]

Describe the bug

If you have an app.config file in your solution it will cause the error "The type initializer for 'Microsoft.Data.SqlClient.SqlConnection' threw an exception"

An exception of type 'System.TypeInitializationException' occurred in Microsoft.Data.SqlClient.dll but was not handled in user code: 'The type initializer for 'Microsoft.Data.SqlClient.SqlConnection' threw an exception.'
 Inner exceptions found, see $exception in variables window for more details.
 Innermost exception     System.Xml.XmlException : Data at the root level is invalid. Line 1, position 1.
   at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.Throw(String res, String arg)
   at System.Xml.XmlTextReaderImpl.ParseRootLevelWhitespace()
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at System.Xml.XmlTextReaderImpl.Read()
   at System.Xml.XmlTextReader.Read()
   at System.Configuration.XmlUtil..ctor(Stream stream, String name, Boolean readToFirstElement, ConfigurationSchemaErrors schemaErrors)
   at System.Configuration.BaseConfigurationRecord.InitConfigFromFile()

To reproduce

Just create a sample console app that uses Microsoft.Data.SqlClient and add an app.config file. It could be even an empty file.

Expected behavior

Do not crash :)

Further technical details

Microsoft.Data.SqlClient version: 1.0.19269.1
.NET target: (e.g. Framework Core 2.1)
Operating system: (e.g. Windows 10)

[yukiwongky]

@yorek From the error you're getting (Data at the root level is invalid. Line 1, position 1.), looks like your app.config file is invalid. If I use an app config autogenerated by visual studio

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
</configuration>

I was able to initialize SqlConnection just fine.

[saurabh500]

@yukiwongky What is the behavior on .Net framework v4.8 ?

I know that SqlConnection class tries to load some values from the App.Config to initialize providers for always encrypted and for auth providers for AAD.

It might be worth checking out the behavior of DbProviderFactory (an API which I picked up, which depends on App.config) to see what the behavior is, in case of a malformed app.config

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/obtaining-a-dbproviderfactory

If the API fails to load the db provider factory silently, then it may perhaps be good to make SqlConnection static constructor to fail silently instead of preventing initialization.

[yorek]

@yukiwongky right but if the file is there and is empty, or contains json, you'll get the error. If "app.config" file must be an XML, it's better to call it out clearly in the doc, as with System.Data.SqlClient, there are no problem instead.

[yukiwongky]

@saurabh500 I see the exception comes from SqlConnection.cs#L40 when the configuration file can not be loaded. And I also found in DbProviderFactories.cs that the exception is just ignored. We can do the same in the MDS driver as well, instead of throwing an exception, make sqlColumnEncryptionEnclaveProviderConfigurationSection null if the configuration file is not found.

[saurabh500]

Yeah I agree with the approach

[yukiwongky]

@yorek we're working towards removing the need for app.config as mentioned in #158. Please stay tune.

[yorek]

@yukiwongky great! :)

[yukiwongky]

Hi @yorek, we have the fix in v1.1.0-preview2. Can you give it a try?

[yorek]

Hi. Just tried it. Now I receive just this error:

Unhandled exception. System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SqlAuthenticationProviderManager' threw an exception.
 ---> System.InvalidOperationException: Failed to read the config section for authentication providers.
 ---> System.Configuration.ConfigurationErrorsException: Configuration system failed to initialize
 ---> System.Configuration.ConfigurationErrorsException: Data at the root level is invalid. Line 1, position 1. (C:\Work\_lab\azure-sql-connection\bin\Debug\netcoreapp3.0\azure-sql-connection.dll.config line 1)
 ---> System.Xml.XmlException: Data at the root level is invalid. Line 1, position 1.
   at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.Throw(String res, String arg)
   at System.Xml.XmlTextReaderImpl.ParseRootLevelWhitespace()
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at System.Xml.XmlTextReaderImpl.Read()
   at System.Xml.XmlTextReader.Read()
   at System.Configuration.XmlUtil..ctor(Stream stream, String name, Boolean readToFirstElement, ConfigurationSchemaErrors schemaErrors)
   at System.Configuration.BaseConfigurationRecord.InitConfigFromFile()
   --- End of inner exception stack trace ---
   at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal)
   at System.Configuration.BaseConfigurationRecord.ThrowIfParseErrors(ConfigurationSchemaErrors schemaErrors)
   at System.Configuration.BaseConfigurationRecord.ThrowIfInitErrors()
   at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
   --- End of inner exception stack trace ---
   at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
   at System.Configuration.ClientConfigurationSystem.PrepareClientConfigSystem(String sectionName)
   at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName)
   at System.Configuration.ConfigurationManager.GetSection(String sectionName)
   at Microsoft.Data.SqlClient.SqlAuthenticationProviderManager..cctor()
   --- End of inner exception stack trace ---
   at Microsoft.Data.SqlClient.SqlAuthenticationProviderManager..cctor()
   --- End of inner exception 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, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)     
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Azure.SQLDB.Samples.Connection.Program.Main(String[] args) in C:\Work\_lab\azure-sql-connection\Program.cs:line 12

I'm using this simple code to test the library:

using Microsoft.Data.SqlClient;

namespace Azure.SQLDB.Samples.Connection
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connection string can be found in the portal
            using(var conn = new SqlConnection("Server=localhost; Integrated Security=SSPI;"))
            {
                conn.Open();
                var cmd = new SqlCommand("SELECT Column1 FROM dbo.Table1", conn);
                var result = cmd.ExecuteScalar();
                conn.Close();
            }           
        }
    }
}

and this is the .csprj file:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.0</TargetFramework>
    <RootNamespace>Azure.SQLDB.Samples.Connection</RootNamespace>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="1.1.0-preview2.19309.1" />
  </ItemGroup>
</Project>

It works nice until you create an app.config file in the same folder. The app.config file is set to be

{}

[cheenamalhotra]

Addressed in #319

[johnsimons]

I don't think this is fixed in v1.1.0

I'm still getting the following exception when my config looks something like:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="Logging.Level" value="Debug" />
  </appSettings>
  <system.diagnostics>
    <sources>
      <source name="Halibut">
        <listeners>
          <add name="nlog" />
        </listeners>
      </source>
    </sources>
    <switches>
      <add name="Halibut" value="Verbose" />
    </switches>
    <sharedListeners>
      <add name="nlog" type="NLog.NLogTraceListener, NLog" />
    </sharedListeners>
    <trace autoflush="true" />
  </system.diagnostics>
  <runtime>
    <gcServer enabled="true" />
  </runtime>
</configuration>

Exception stacktrace:

--Inner Exception--
The type initializer for 'Microsoft.Data.SqlClient.SqlAuthenticationProviderManager' threw an exception.
System.TypeInitializationException
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
  ....

--Inner Exception--
Failed to read the config section for authentication providers.
System.InvalidOperationException
   at Microsoft.Data.SqlClient.SqlAuthenticationProviderManager..cctor()

--Inner Exception--
Configuration system failed to initialize
System.Configuration.ConfigurationErrorsException
   at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
   at System.Configuration.ClientConfigurationSystem.PrepareClientConfigSystem(String sectionName)
   at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName)
   at System.Configuration.ConfigurationManager.GetSection(String sectionName)
   at Microsoft.Data.SqlClient.SqlAuthenticationProviderManager..cctor()

--Inner Exception--
Unrecognized configuration section system.diagnostics. (C:\buildAgent\work\abb2fbfce959a439\tests\Octopus.E2ETests.API.dll.config line 6)
System.Configuration.ConfigurationErrorsException
   at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal)
   at System.Configuration.BaseConfigurationRecord.ThrowIfParseErrors(ConfigurationSchemaErrors schemaErrors)
   at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)