I managed to configure an instance of an instance of SQL Server 2017 on the latest version of Debian and a domain that points to this server, and I obtained the respective certificates using certbot. I also managed to create a TXT record for the acme challenge, launched using the certbot command to issue the certificates. Buut I can't encrypt the connections to the server. For example, when I connect from SQL Server Management Studio (SSMS), I always have to check the following option to connect:
And if I try to connect via the sqlcmd utility, I can only connect -NOT option. I get the following error on SSMS (and a less talkative on the sqlcmd, but pretty much the same):
A connection was successfully established with the server, but a
an error occurred during the connection process. (provider: SSL provider,
error: 0 – The certificate chain was issued by an authority which is
not approved.) (.Net SqlClient Data Provider)
So I guess the error is about encrypting the connection: if I try to force it, the connection will fail. In fact, the encrypted connection fails even if I try to connect to the server itself with the sqlcmd utility with the -NOT flag!
I tried to configure 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, using the command below, according to https://support.microsoft.com/en-us/help/2914662/how-to-use-pfx -formatted-certificates-in-SQL server. But when I tried to import this certificate into SQL Server, via 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:pathToTheKeyPairscertificate.der_2.cer' WITH PRIVATE KEY (FILE = 'C:pathToTheKeyPairs certificate.der_2.pvk', DECRYPTION BY PASSWORD = '
I had the following error:
The certificate, asymmetric key or private key file is not valid or
does not exist; or you don't have permissions for it.
Finally, I tried a completely different Apache configuration, according to https://hostadvice.com/how-to/configure-apache-with-tls-ssl-certificate-on-ubuntu-18
And even if on the client I received exactly the same error message that I received before, on the server itself, by running the sqlcmd utility, I have a different error message:
Sqlcmd: error: Microsoft ODBC driver 17 for SQL Server: SSL provider:
(error: 1416F086: SSL
routines: tls_process_server_certificate: certificate verification failed: auto
So I guess the problem comes from the way I generated the certificates. Any idea how can I make encryption work?