System.ArgumentException: Use of key 'Failover Partner' requires the key 'Initial Catalog' to be present.

[mufukuha]

Describe the bug

When you try to connect to SQL Server by Microsoft.Data.SqlClient v5 and meet the following conditions, you will get an error.

• The connection string does not include Initial Catalog keyword.
  Example: Data Source=<servername>;Integrated Security=true;Pooling=False;Encrypt=False;
• The login's default database is mirrored by Database Mirroring.

Exception message: System.ArgumentException: Use of key 'Failover Partner' requires the key 'Initial Catalog' to be present.
Stack trace:

   at Microsoft.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at Microsoft.Data.SqlClient.SqlConnectionPoolGroupProviderInfo.CreateFailoverPermission(SqlConnectionString userConnectionOptions, String actualFailoverPartner)
   at Microsoft.Data.SqlClient.SqlConnectionPoolGroupProviderInfo.FailoverCheck(Boolean actualUseFailoverPartner, SqlConnectionString userConnectionOptions, String actualFailoverPartner)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   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, Func`3 accessTokenCallback)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   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.TryOpenInner(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()

To reproduce

• The connection string does not include Initial Catalog keyword.
  Example: Data Source=<servername>;Integrated Security=true;Pooling=False;Encrypt=False;
• The login's default database is mirrored by Database Mirroring.

            string connectionString = @"Data Source=SQL2019CL1;Integrated Security=true;Pooling=False;Encrypt=False;";
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                connection.Open();
                Console.WriteLine("Connected");
                connection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

Expected behavior

No error in Microsoft.Data.SqlClient v5.

Further technical details

In Microsoft.Data.SqlClient v3, there is no error, and connection succeeds. However, Microsoft.Data.SqlClient v4 gets KeyNotFoundException, and Microsoft.Data.SqlClient v5 gets ArgumentException. I expect there should be no error in Microsoft.Data.SqlClient v5 like v3.

• Microsoft.Data.SqlClient v3: No error.
• Microsoft.Data.SqlClient v4: Error - System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
  --> Fixed in Fix | FailoverPartner key check in SqlConnectionPoolGroupProviderInfo by lcheunglci · Pull Request #1614 · dotnet/SqlClient (github.com)
• Microsoft.Data.SqlClient v5: Error - System.ArgumentException: Use of key 'Failover Partner' requires the key 'Initial Catalog' to be present.

Looking into the code, the cause appears to be that _parsetable changed from Hashtable to Dictionary, and "if condition" modified due to the bug fix.

Release Stable Release v3.1.3 · dotnet/SqlClient · GitHub

        private System.Security.PermissionSet CreateFailoverPermission(SqlConnectionString userConnectionOptions, string actualFailoverPartner)
        {

            if (null == userConnectionOptions[SqlConnectionString.KEY.FailoverPartner])
            {
                keywordToReplace = SqlConnectionString.KEY.Data_Source;  <------ Goes down here
            }
            else
            {
                keywordToReplace = SqlConnectionString.KEY.FailoverPartner;
            }

userConnectionOptions[SqlConnectionString.KEY.FailoverPartner] tries to get a value from _parsetable that is Hashtable. If the key is not found in the Hashtable, it returns null.

\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\Common\DbConnectionOptions.cs

        private readonly Hashtable _parsetable;

Release Stable Release v4.0.0 · dotnet/SqlClient · GitHub

        private System.Security.PermissionSet CreateFailoverPermission(SqlConnectionString userConnectionOptions, string actualFailoverPartner)
        {

            if (null == userConnectionOptions[SqlConnectionString.KEY.FailoverPartner]) <------ KeyNotFoundException
            {
                keywordToReplace = SqlConnectionString.KEY.Data_Source;
            }
            else
            {
                keywordToReplace = SqlConnectionString.KEY.FailoverPartner;
            }

userConnectionOptions[SqlConnectionString.KEY.FailoverPartner] tries to get a value from _parsetable that is Dictionary. If the key is not found in the Dictionary, it throws KeyNotFoundException.

	\src\Microsoft.Data.SqlClient\src\Microsoft\Data\Common\DbConnectionOptions.Common.cs
	
	        private readonly Dictionary<string, string> _parsetable;

This exception was addressed by #1614 and fixed in v5.0.0.
Fix | FailoverPartner key check in SqlConnectionPoolGroupProviderInfo by lcheunglci · Pull Request #1614 · dotnet/SqlClient (github.com)

Release Stable Release v5.0.0 · dotnet/SqlClient (github.com)

        private PermissionSet CreateFailoverPermission(SqlConnectionString userConnectionOptions, string actualFailoverPartner)
        {

            if (userConnectionOptions.ContainsKey(SqlConnectionString.KEY.FailoverPartner) &&
                null == userConnectionOptions[SqlConnectionString.KEY.FailoverPartner])
            {
                keywordToReplace = SqlConnectionString.KEY.Data_Source;
            }
            else
            {
                keywordToReplace = SqlConnectionString.KEY.FailoverPartner;  <------ Goes down here
            }

	\src\Microsoft.Data.SqlClient\src\Microsoft\Data\Common\DbConnectionOptions.Common.cs

	        private readonly Dictionary<string, string> _parsetable;

The exception does not occur. It moves forward and fails connection string check.

src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\SqlConnectionString.cs

internal SqlConnectionString(string connectionString) : base(connectionString, GetParseSynonyms())
{

    if (!string.Equals(DEFAULT.FailoverPartner, _failoverPartner, StringComparison.OrdinalIgnoreCase))
    {
        // fail-over partner is set

        if (_multiSubnetFailover)
        {
            throw SQL.MultiSubnetFailoverWithFailoverPartner(serverProvidedFailoverPartner: false, internalConnection: null);
        }

        if (string.Equals(DEFAULT.Initial_Catalog, _initialCatalog, StringComparison.OrdinalIgnoreCase))
        {
            throw ADP.MissingConnectionOptionValue(KEY.FailoverPartner, KEY.Initial_Catalog);  <------ Goes down here
        }
    }

Additional context
SQL Server Management Studio (SSMS) 19 uses Microsoft.Data.SqlClient v3, and SSMS 20 uses Microsoft.Data.SqlClient v5. This behavior change and bug fix may have some impacts after upgrading to SSMS 20.

[dauinsight]

Hi @mufukuha, we will look to address this issue. In the meantime you may update your connection string to either add Failover Partner = null or look to add an Initial Catalog

[mufukuha]

Yes, the current workaround is to add an Initial Catalog. The error still occurs after adding Failover Partner = null.

[kjohnston-icm]

I noticed this occurred when using an SQL account that does not have permissions to view/list all databases on the server.