SQL Server: 'The certificate chain was issued by an authority that is not trusted'

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.) means Microsoft.Data.SqlClient tried to encrypt the connection, which it does by default since version 4.0, and could not validate the certificate the server presented. On your own development machine, add TrustServerCertificate=True to the connection string and the same code connects.

That keeps the traffic encrypted but skips the server's identity check, so use it only on a developer PC. In production, give the server a certificate from an authority the client trusts.

The error

PS> .\FixLab.exe 'Server=.\SQLEXPRESS;Database=master;Integrated Security=true' 'SELECT @@VERSION'
ex.Number = -2146893019
Microsoft.Data.SqlClient.SqlException: 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.)
InnerException: System.ComponentModel.Win32Exception (NativeErrorCode -2146893019): The certificate chain was issued by an authority that is not trusted.

Why it happens

Microsoft.Data.SqlClient 4.0 changed the default of Encrypt from false to true, which today means Encrypt=Mandatory. With encryption mandatory, the client checks the server's certificate during login: the chain must end at an authority it trusts, the certificate must be valid, and the name must match. An instance that was never given a certificate makes its own at startup; this Express log said so: A self-generated certificate was successfully loaded for encryption. Nothing on the client trusts a self-generated certificate, so the TLS handshake fails after the network connection succeeded. ex.Number is not a SQL Server error number here; it is the Windows code 0x80090325 printed as a negative integer.

LocalDB does not show this error. The same connection string pointed at (localdb)\MSSQLLocalDB connected, and sys.dm_exec_connections reported a named pipe with encrypt_option FALSE: the 4.0 release notes list "Disable encryption when connecting to SQL LocalDB". That is why the error tends to appear the day a project moves from LocalDB to Express or a full SQL Server, or when an old app is upgraded from System.Data.SqlClient.

The fix

PS> .\FixLab.exe 'Server=.\SQLEXPRESS;Database=master;Integrated Security=true;TrustServerCertificate=True' 'SELECT net_transport, encrypt_option, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID'
Shared memory | TRUE | NTLM
OK (-1 row(s) affected) in 0.0 s

PS> .\FixLab.exe 'Server=.\SQLEXPRESS;Database=master;Integrated Security=true;Encrypt=False' 'SELECT net_transport, encrypt_option, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID'
Shared memory | FALSE | NTLM
OK (-1 row(s) affected) in 0.0 s

With TrustServerCertificate=True the connection is encrypted (TRUE) but the certificate is not validated, so an impostor server would be accepted. Encrypt=False, and Encrypt=Optional on a server that does not force encryption, connect too but leave the session unencrypted (FALSE); Microsoft's documentation says only the login packets are encrypted then. For production, install a certificate from a public or company authority that includes the name clients connect with, configure SQL Server to use it, and keep TrustServerCertificate=false, as described in Encryption and certificate validation in Microsoft.Data.SqlClient.

An ASP.NET Core app with the same string in appsettings.json fails the same way: it started normally, and the first request that queried through EF Core returned 500 with this log:

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
      An error occurred using the connection to database 'master' on server '.\SQLEXPRESS'.
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'AppDbContext'.
      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.

Adding ;TrustServerCertificate=True to the Default value fixed the request (200, ["master"]).

How it was reproduced

A console app from dotnet new console -n FixLab -f net10.0 (.NET SDK 10.0.402) with Microsoft.Data.SqlClient 7.1.1, which runs the SQL in its second argument against the connection string in its first. The server was SQL Server 2022 Express 16.0.1210.3 (.\SQLEXPRESS, self-generated certificate) on Windows 11, with LocalDB 16.0.1210.3 for the comparison. The web check used dotnet new web with Microsoft.EntityFrameworkCore.SqlServer 10.0.13, listening on 127.0.0.1.

Frequently asked

How do I fix The certificate chain was issued by an authority that is not trusted in SQL Server?

On a development machine, add TrustServerCertificate=True to the connection string. In production, give SQL Server a certificate from an authority the client trusts, with the server name in it, and keep TrustServerCertificate=false.

Is TrustServerCertificate=True safe?

It keeps the connection encrypted but skips validation of the server certificate, so the client cannot tell the real server from an impostor. Use it on your own PC or an isolated test network, not as a production fix.

Why does LocalDB connect but SQL Server Express fails with the certificate error?

Microsoft.Data.SqlClient 4.0 and later turns encryption off for LocalDB, so no certificate is checked. Express and full SQL Server get Encrypt=Mandatory by default and fail on their self-generated certificate.

To see every key explained, paste your connection string into the connection string checker. LocalDB and Express side by side are covered in the databases part of Set Up a Windows PC for .NET Development. More decoded errors in the Fixes category.