Managed SNI (TCP) doesn't handle localhost for Certificate validation, fails during pre-login

[cheenamalhotra]

Describe the bug

Managed SNI fails to validate remote certificate for some reason, even though I am able to make connection successfully with Native SNI.

Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)
 ---> System.Security.Authentication.AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
   at System.Net.Security.SslStream.CompleteHandshake(SslAuthenticationOptions sslAuthenticationOptions)
   at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](Boolean receiveFirst, Byte[] reAuthenticationData, CancellationToken cancellationToken)
   at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions)
   at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.EnableSsl(UInt32 info, SqlConnectionEncryptOption encrypt, Boolean integratedSecurity, String serverCertificateFilename)
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst, String serverCert)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   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, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   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.<>c__DisplayClass48_0.<CreateReplaceConnectionContinuation>b__0(Task`1 _)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
ClientConnectionId:17491170-5b62-4ccc-a99e-3009112a9ed9
Error Number:-2146893019,State:0,Class:20

Actual error

Exception Message: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
   at System.Net.Security.SslStream.CompleteHandshake(SslAuthenticationOptions sslAuthenticationOptions) in /_/src/libraries/System.Net.Security/src/System/Net/Security/SslStream.IO.cs:line 537
   at System.Net.Security.SslStream.<ForceAuthenticationAsync>d__146`1.MoveNext() in /_/src/libraries/System.Net.Security/src/System/Net/Security/SslStream.IO.cs:line 333
   at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions) in /_/src/libraries/System.Net.Security/src/System/Net/Security/SslStream.cs:line 329
   at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)

To reproduce

Console app targeting .NET 6/7

    public static async Task Main()
    {
        // When switch is commented out, connection is successful!
        AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true);

        using (SqlConnection conn = new("Server=tcp:localhost\\MSSQLSERVER02; Integrated Security=true; Pooling=false;"))
        {
            try
            {
                await conn.OpenAsync(); // conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }

Expected behavior

Connect.

Further technical details

Microsoft.Data.SqlClient version: 5.1.1, main
.NET target: .NET 6/7
Microsoft SQL Server 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64)
Jan 23 2023 17:02:42
Copyright (C) 2022 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 22621: ) (Hypervisor)
Operating system: Windows 11

[cheenamalhotra]

I figured this is due to HostName in certificate being set to Machine name as I use localhost in servername - but I wonder why Managed SNI cannot detect machine name while Native SNI does?

Mitigation: Specify HostNameInCertificate=<machinename> in connection string.

[JRahnama]

@cheenamalhotra alias name could be added to SANs, but for instances of of SQL server I found this article. I can do some testing to validate it.

On some other note for managed SNI we can also test with this PR to see more info and check at what part it fails. I will do more testing to get an answer to the question and will get back to you on this.

If you test with Net7.0 all the SANs will be validated.

[JRahnama]

After investigating the issue it seems that if you provide Read access to private key for the instance name it works fine. for example if the user name for default instance is NT Service\MSSQLSERVER you will need to provide access to NT Service\MSSQL$MSSQLSERVER02 as well. In my test case doing so made the application work. Now the question is why native code works without that access. I will look into native code next week.

[JRahnama]

here is how I created a self-signed certificate

# Get FQDN of the machine
    $fqdn = [System.Net.Dns]::GetHostByName(($env:computerName)).HostName

    Write-Host $fqdn

    # Create a self-signed certificate
    $params = @{
      Type              = "SSLServerAuthentication"
      Subject           = "CN=$fqdn"
      KeyAlgorithm      = "RSA"
      KeyLength         = 2048
      HashAlgorithm     = "SHA256"
      TextExtension     = "2.5.29.37={text}1.3.6.1.5.5.7.3.1", "2.5.29.17={text}DNS=$fqdn&DNS=localhost&IPAddress=127.0.0.1&IPAddress=::1"
      NotAfter          = (Get-Date).AddMonths(36)
      KeySpec           = "KeyExchange"
      Provider          = "Microsoft RSA SChannel Cryptographic Provider"
      CertStoreLocation = "Cert:\LocalMachine\My"
    }

    $certificate = New-SelfSignedCertificate @params
    Write-Host "Certificate created successfully"
    Write-Host "Certificate Thumbprint: $($certificate.Thumbprint)"

This part

$fqdn = [System.Net.Dns]::GetHostByName(($env:computerName)).HostName

will get the full name if there is a domain instead of computer name.

[swh-cb]

This issue may be related to #2115. Currently, Native SNI requires the computer name as SAN in the certificate while managed SNI requires localhost as SAN in this case. If the SQL server certificate only contains the computer name, the connection can only be established with Native SNI. Actually, the native SNI should not establish the connection either, because the server name in the connection string (localhost\MSSQLSERVER02) does not match the subject alternative names in the certificate.

[JRahnama]

@cheenamalhotra can you provide server setup and certificate setup process please?

[cheenamalhotra]

SQL Server is below, running in a managed instance:

Microsoft SQL Server 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64)
Jan 23 2023 17:02:42
Copyright (C) 2022 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 22621: ) (Hypervisor)

I created self signed certificate using New-SelfSignedCertificate, you can create using:

$cert = New-SelfSignedCertificate -Subject {machine name}
$cert | Format-List -Property *

Properties:

Attach this certificate to the SQL instance running and attempt connection.

[JRahnama]

@cheenamalhotra can you test with the latest SNI hotfix release to see if the behavior identical for managed and native code?