0

I have successfully setup an instance of a SQL Server 2017 instance on the latest Debian release, and a domain that points to that server, and I have gotten the respective certificates by using certbot. I also managed to create a TXT record for the acme-challenge, initiated by using the certbot command to issue the certificates. Buut I am failing to encrypt the connections to the server. For example, when I connect from SQL Server Management Studio (SSMS), I always have to tick the following option in order to connect:

enter image description here

And if I try to connect through the sqlcmd utility, I can only connect without the -N option. I am getting the following error on SSMS (and a less verbose one on the sqlcmd, but pretty much the same):

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.) (.Net SqlClient Data Provider)

The connection fails even if I try to login in the server itself with the sqlcmd utility with the -N flag, with the same error message!

I tried configuring the Apache server by adding the path to two of the generated files, namely the fullchain.pem and privkey.pem in the /etc/apache2/apache2.conf file, but the error persisted.

Then I tried to generate a PFX certificate with OpenSSL, by using the command below, as per https://support.microsoft.com/en-us/help/2914662/how-to-use-pfx-formatted-certificates-in-sql-server.

$ openssl pkcs12 -export -out \path\to\the\PFX\certificate.pfx -inkey privkey.pem -in cert.pem -certfile chain.pem

I imported this certificate into the client, in the certlm, under Certificates - Local computer >> Personal >> Certificates, but the result was the same. I tried also to import this certificate into SQL Server, either through SSMS on Windows or using the sqlcmd utility directly on the Linux host, by running the following SQL query:

CREATE CERTIFICATE msSqlServerCert
   FROM FILE = 'C:\pathToTheKeyPairs\certificate.der_2.cer'
   WITH PRIVATE KEY (FILE = 'C:\pathToTheKeyPairs \certificate.der_2.pvk',
   DECRYPTION BY PASSWORD = '<EncryptionPassword>');

I got the following error:

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

Lastly, I tried a totally different Apache configuration, as per https://hostadvice.com/how-to/configure-apache-with-tls-ssl-certificate-on-ubuntu-18

And even though on the client I got exactly the same error message as I was getting before, on the server itself, by running the sqlcmd utility, I got a different error message:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSL Provider: [error:1416F086: SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate].

So I am guessing the problem is with the way I generated the certificates. Any idea how can I make the encryption work?

Improve this question
2
  • If you don't want to force the client to trust the server certificate, you need to use a certificate the client already trusts. That means 1) installing that cert in the client cert store, or 2) installing a cert generated by your PKI (client should be set up to trust your infrastructure and root certs), or 3) getting a cert from one of the major providers (such as VeriSign).
    – Tony Hinkle
    Commented Feb 23, 2020 at 16:21
  • Hi @TonyHinkle, thanks for trying to help. I had already imported the certificate in the .pfx format (generated with "openssl pkcs12 -export ...", in certlm app. The result is the same! I'll check how to get a cert from one of the major providers
    – ccoutinho
    Commented Feb 23, 2020 at 21:04

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.