0

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!

Shanebagel
  • 1
  • Need to provide details about the application with the "connection string". – Greg Askew Jun 01 '23 at 02:25
  • 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. – DubStep Jun 01 '23 at 13:36
  • 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 Jun 01 '23 at 15:55
  • https://imgur.com/a/85RDPBg Here is a screenshot - the certificate shows up when it's in the personal store – Shanebagel Jun 01 '23 at 16:00
  • 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 – Shanebagel Jun 01 '23 at 18:58
  • 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. – StrayCatDBA Jun 02 '23 at 19:19
  • How do you check the error log in sql server? Can you run a command or do it through ssms – Shanebagel Jun 02 '23 at 19:36
  • SSMS, "Management" -> "SQL Server Logs" -> Current (the top one). you should see a line "The certificate [Cert Hash(sha1) "" ] was successfully loaded for encryption" – StrayCatDBA Jun 02 '23 at 19:57
  • 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 – StrayCatDBA Jun 02 '23 at 20:00
  • Thanks a ton for the info guys - I'll try all these recommendations! – Shanebagel Jun 03 '23 at 03:44

0 Answers0