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:
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?