11

I am using a .NET console app to test SQL Server 2019 database connection and get following 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.)

The user id and password are valid. The C# code is not using any encryption. I have not configured any certificate in SQL Server configuration.

  1. Is certificate required in SQL Server 2019?
  2. Can I get a self generated cert from the SQL Server?
  3. If no self-generated cert available, where can I get a valid certificate for free?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bedrock
  • 263
  • 1
  • 2
  • 15
  • 2
    _I have not configured any certificate in SQL configuration._ Oh, but you have. SQL Server gets installed with a Self-Signed X.509 certificate which you'll be able to find in Manage Computer Certificates > Personal. In your connection string either set `Encrypt=false;` to use an unencrypted connection, or `TrustServerCertificate=true;` to accept the self-signed cert. Or, if you're so inclined, create a new certificate from your organization's CA and install it via [SQL Server Configuration Manager](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/manage-certificates). – AlwaysLearning Dec 17 '21 at 23:42
  • 1. The issue has been resolved by creating self-signed certification using "Certificate" snap-in in MS Management Console /mmc. Following YouTube video provides detailed instructions: https://www.youtube.com/watch?v=m7ONeACYBdE 2. When connecting to SQL DB in a program, the connection string should include **TrustServerCertificate=True**. Otherwise, the same error related to certificate will occur. – bedrock Dec 18 '21 at 16:21
  • 2
    `TrustServerCertificate=True` is effectively saying "I couldn't care less", you may as well do `Encrypt=false` for all the difference it would make, at least you gain a little performance by not encrypting. Ideally you should have a proper signed certificate, and your client machine needs to trust the CA that signed that certificate. – Charlieface Dec 18 '21 at 18:04

1 Answers1

9

To improve the answer, let me sum up the comments:

While setting TrustServerCertificate=True or Encrypt=false in the connection string is a quick fix, the recommended way of solving this issue is to provide a proper certificate for your SQL Server from a trusted CA.

To install a certificate for a single SQL Server instance (source):

  1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.

  2. Right-click Protocols for <instance Name>, and then select Properties.

  3. Choose the Certificate tab, and then select Import.

  4. Select Browse and then select the certificate file.

  5. Select Next to validate the certificate. If there are no errors, select Next to import the certificate to the local instance.

gpinkas
  • 2,291
  • 2
  • 33
  • 49
  • 2
    But for step 4, where do we get the certificate file from? – DumbCoder Jun 07 '23 at 12:47
  • You have to create one by a CA (certificate authority) that is trusted by the client. For example you could use DigiCert, LetsEncrypt or an internal CA. There's a whole process necessary, here is a starting point: https://stackoverflow.com/questions/21297139/how-do-you-sign-a-certificate-signing-request-with-your-certification-authority/21340898 – gpinkas Jun 07 '23 at 13:19