Score:0

Getting an error in SQL Server 2019: The certificate chain was issued by an authority that is not trusted

tr flag

I've enabled "Encrypt connection" I've enabled "Trust server certificate"

I've added the TrustServerCertificate=true to my connection string

I've confirmed the service is running - there is another service called "SQL Server Agent" that refuses to start however

I have a self signed cert that I generated with powershell - and I've added it into SQL Server configuration manager.

Here is the 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.)

This is on SQL Express 2019

Any help would be appreciated!

cn flag
Need to provide details about the application with the "connection string".
ng flag
I don't see anywhere where you say that you installed the certificate to the trusted root authority of the machine. Did you do this? That error seems to indicate you did not.
Shanebagel avatar
tr flag
So once I add the self signed cert to the 'Trusted Root Certificate Authorities' store - it doesn't show up in the SQL server settings - like it's blank I cannot import it? It only shows up when it's under the 'Personal' certificate store
Shanebagel avatar
tr flag
https://imgur.com/a/85RDPBg Here is a screenshot - the certificate shows up when it's in the personal store
Shanebagel avatar
tr flag
https://imgur.com/a/G0hK7YD Here is the connection string. Not sure what I'm doing wrong - I have no issue connecting through SSMS with Encryption disabled, and Trusting the servers cert. The cert is missing when I drag it into the 'trusted root certificate authorities' store - it will only show up when it's in the personal store
pk flag
trust server certificate=true in the connection string disables the certificate validation check, so you shouldn't be getting that error. period. Restart the SQL service and verify that the certificate is being loaded successfully by checking the ERRORLOG.
Shanebagel avatar
tr flag
How do you check the error log in sql server? Can you run a command or do it through ssms
pk flag
SSMS, "Management" -> "SQL Server Logs" -> Current (the top one). you should see a line "The certificate [Cert Hash(sha1) "<cert thumbprint>" ] was successfully loaded for encryption"
pk flag
since you're using trustServerCertificate=true, I'd try removing the custom cert and see if that works. safest way is to update the registry directly, vs using the sql configuration tool. also, when you created the cert, did your custom cert meet the requirements per sql documentation here: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/certificate-requirements?view=sql-server-ver16
Shanebagel avatar
tr flag
Thanks a ton for the info guys - I'll try all these recommendations!
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.