Unable to use SSL Certificates in SQL Server on Linux

Question

Hello everyone so here is the problem:

SQL Server 2019 installed on Ubuntu 20.04 (working before setting SSL certificates)

I was following the Official guide of Microsoft to secure my SQL server with SSL on Ubuntu 20.04.

I firstly tried using directly CA certificates created with Certbot and Let's encrypt. I spend hours trying without any luck.

So I decided to try the tutorial as it was with self signed certificates following the guide AS IS without any change.

These are my commands:

root@racknerd:~# openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=beta.mydomain.com' -keyout mssql.key -out mssql.pem -days 365
Generating a RSA private key
................+++++
.......................................................................................+++++
writing new private key to 'mssql.key'
-----
root@racknerd:~# sudo chown mssql:mssql mssql.pem mssql.key
root@racknerd:~# sudo chmod 600 mssql.pem mssql.key
root@racknerd:~# sudo mv mssql.pem /etc/ssl/certs/
root@racknerd:~# sudo mv mssql.key /etc/ssl/private/
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/mssql.pem
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/private/mssql.key
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# sudo /opt/mssql/bin/mssql-conf set network.forceencryption 0
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@racknerd:~# systemctl stop mssql-server

Obviously I used a real domain that it's mapped to this machine IP but this is not the problem. Following Microsoft's guide I restart the service and I keep having this error:

Unable to open one or more of the user-specified certificate file(s)

2021-01-16 08:58:05.35 spid23s     Error: 49940, Severity: 16, State: 1.
2021-01-16 08:58:05.35 spid23s     Unable to open one or more of the user-specified certificate file(s). Verify that the certificate file(s) exist with read permissions for the user and group running SQL Server.
2021-01-16 08:58:05.37 spid23s     Error: 49939, Severity: 16, State: 1.
2021-01-16 08:58:05.37 spid23s     Unable to initialize user-specified certificate configuration. The server is being shut down. Verify that the certificate is correctly configured. Error[30]. State[51].
2021-01-16 08:58:05.39 spid21s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

This is the same error that I get when I do the process using the Let's Encrypt certificates. I suppose so that stating I set the right permission on the certificates, the right owner there is something wrong with the official documentation of Microsoft or there is no way to make this work. I saw that someone else had the same problems but didn't find any fix for that, has something changed since then?

Many thanks

Answer 1

Two things are at play here: file permissions and using LetsEncrypt in general.

Regarding the permissions for the certificate, the owner of the files needs to be set to mssql and the permissions to 440. Also see the Microsoft documentation.

In general, I have not been able to use MSSQL on Docker with LetsEncrypt certificates. Actually deploying it via the Microsoft instructions it not the issue. MSSQL on Docker does not seem to serve the intermediate LetsEncrypt certificate, hence the connection will never be trusted by any client. Also see this issue on Github for more information.