The certificate chain was issued by an authority that is not trusted.

[qbqmbur]

We are using a .Net 8 console application. With this we want to create a database connection using Microsoft.Data.SqlClient package because System.Data.SqlClient is marked as deprecated.

For this we are using a connection string with the format Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<database>;Data Source=<server>;Encrypt=True;TrustServerCertificate=True;.

Executing the code we get this error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

When we change the used package to System.Data.SqlClient the application works without any other changes.

[ErikEJ]

What are you connecting to?

[qbqmbur]

SQL Server 2022

[mdaigle]

Hi @qbqmbur, can you please provide the additional information requested in the bug report template?
Specific versions of Microsoft.Data.SqlClient, Sql Server, environment (e.g. local server vs. azure) and repro steps will all be helpful for us in investigating the issue.
Thank you!

[qbqmbur]

Error Message

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

Stack trace:

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
 ---> System.ComponentModel.Win32Exception (0x80090325): The certificate chain was issued by an authority that is not trusted.
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlConnectionEncryptOption encrypt)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword, SqlConnectionEncryptOption encrypt)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, 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, 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.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, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at AutoDeploy.DbConnectionFactory.CreateOpenConnection(TargetDb target) in C:\Project\AutoDeploy\DbConnectionFactory.cs:line 18
   at Project.AutoDeploy.Deploy.Run() in C:\Project\AutoDeploy\Deploy.cs:line 26
   at Program.<Main>$(String[] args) in C:\Project\AutoDeploy\Program.cs:line 41
ClientConnectionId:49313e44-37c9-4b1c-bf69-8634aa4d2a69
Error Number:-2146893019,State:0,Class:20

Code to reproduce:

SqlConnection connection = new("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<database>;Data Source=<server>;Encrypt=True;TrustServerCertificate=True;");
connection.Open();

Expected behavior

Open a connection to the SQL Server 2022 which is in a local network.

Further technical details

Microsoft.Data.SqlClient version: 6.0.1
.NET target: .NET 8 (Console Application)
SQL Server version: SQL Server 2022
Operating system: Windows Server 2022 Datacenter, 10.0.20348 Build 20348

Additional context

• using Windows Authentication

[mdaigle]

Thanks for the additional info @qbqmbur. Can you check if you have "Force strict encryption" enabled on your server? You can see it in Sql Server Configuration Manager under SQL Server Network Configuration > Right click > Properties. For example:

[cheenamalhotra]

When we change the used package to System.Data.SqlClient the application works without any other changes.

Hi @qbqmbur

Please note that with Microsoft.Data.SqlClient, "Encrypt" connection property is "True" or "Mandatory" by default, which was not the case with System.Data.SqlClient. So you will need to configure server certificates on client in order to connect successfully, or provide "Encrypt=false" on connection string in order to unblock yourself temporarily.

To ensure optimum security, we recommend setting up encryption certificate as default security requirement.

[qbqmbur]

It looks like we are connecting to SQL Server 2019 instead of SQL Server 2022.
The "Force strict encryption" option is not there.
We also tried already to set "Encrypt=false" but that sadly didn't change anything.

[mdaigle]

Can you confirm whether you're using managed networking? You can check for this AppContext switch: "Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows"

[qbqmbur]

I've checked with AppContext.TryGetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", out bool isEnabled); - the value was false.
Now I've added AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true); and I get another error.

Unhandled exception. 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: Certificate failed chain validation. Error(s): 'A certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider., [Status: UntrustedRoot]
'.
Certificate name mismatch. The provided 'DataSource' or 'HostNameInCertificate' does not match the name in the certificate.

   at Microsoft.Data.SqlClient.SNI.SNICommon.ValidateSslServerCertificate(Guid connectionId, String targetServerName, String hostNameInCertificate, X509Certificate serverCert, String validationCertFileName, SslPolicyErrors policyErrors)
   at Microsoft.Data.SqlClient.SNI.SNITCPHandle.ValidateServerCertificate(Object sender, X509Certificate serverCertificate, X509Chain chain, SslPolicyErrors policyErrors)
   at System.Net.Security.SslStream.VerifyRemoteCertificate(RemoteCertificateValidationCallback remoteCertValidationCallback, SslCertificateTrust trust, ProtocolToken& alertToken, SslPolicyErrors& sslPolicyErrors, X509ChainStatusFlags& chainStatus)
   at System.Net.Security.SslStream.CompleteHandshake(ProtocolToken& alertToken, SslPolicyErrors& sslPolicyErrors, X509ChainStatusFlags& chainStatus)
   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, SqlCommand command, 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, TimeoutTimer timeout, SqlConnectionString connectionOptions, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, 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, 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.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, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Project.AutoDeploy.DbConnectionFactory.CreateOpenConnection(TargetDb target) in C:\Project\AutoDeploy\DbConnectionFactory.cs:line 18
   at Project.AutoDeploy.Deploy.Run() in C:\Project\AutoDeploy\Deploy.cs:line 26
   at Program.<Main>$(String[] args) in C:\Project\AutoDeploy\Program.cs:line 45
ClientConnectionId:d6b36091-e047-43d2-ac67-4e3479609b69
Error Number:-2146893019,State:0,Class:20

I assume that this is now something I should check with a server admin or can it still be something in the package?

[mdaigle]

Yeah, all of the information you've provided so far does indicate that the server certificate is being evaluated. Either the server settings are enforcing "strict" encryption (which causes Trust Server Certificate to be ignored), or the options in your connection string are being overridden somehow.

Maybe you can debug into the TdsParser.ConsumePreLoginHandshake method and inspect the state of the parameters? We would expect to see that encrypt is true, trustServerCert is true, integratedSecurity is true and tlsFirst is false. It will also be interesting to see if we have a value for serverCert.

[cheenamalhotra]

According to error: "Certificate name mismatch. The provided 'DataSource' or 'HostNameInCertificate' does not match the name in the certificate."

Can you try to use the same hostname as specified in the certificate?

[qbqmbur]

Maybe you can debug into the TdsParser.ConsumePreLoginHandshake method and inspect the state of the parameters? We would expect to see that encrypt is true, trustServerCert is true, integratedSecurity is true and tlsFirst is false. It will also be interesting to see if we have a value for serverCert.

How can I debug into this? It's pretty deep in the call-chain.

[mdaigle]

There's a few things I'd recommend for you to try:

1. We publish symbols to nuget.org and you can set that up as a symbol server location in visual studio: https://devblogs.microsoft.com/nuget/improved-package-debugging-experience-with-the-nuget-org-symbol-server/#consume-snupkg-from-nuget.org-in-visual-studio
   Once you have that enabled, you can step through the driver source code during debugging. Just set a breakpoint in the method I mentioned above and you'll hit it after your program makes a call to open a connection.
2. Inspect the certificate that you have configured on your sqlserver instance and make sure that it's configured properly, according to the steps here: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver16
3. If your certificate is configured correctly, try what Cheena has suggested. See if using the hostname value from the certificate as the datasource in your connection string helps. If you can, please also share these values.