How to encrypt connection on SQL Server on Linux via SSL
Using Certbot to generate a Let’s Encrypt SSL certificate and adding it to SQL Server 2019
In the last days, I spent hours researching websites the proper way to secure connections between SQL Server and clients. I spent hours because there isn’t a proper post anywhere to do in the correct way, so here’s one tested by me.
In this post, I will use a wonderful tool called Certbot which It’s an automatic tool that generates legit CA SSL certificates using Let’s Encrypt.
Here’s what you will need
- A webserver running Linux (I’m using Ubuntu 20.04 for this tutorial) where it is installed SQL Server 2019. You can find the official Microsoft SQL Server installation procedure here, I assume you follow it and you have already SQL Server up and running.
- A registered domain that points to your machine address, it can be a subdomain, it doesn’t matter, just be sure your DNS point to the machine and you can ping it.
- Certbot installed in your machine.
Let’s start by installing Certbot on Ubuntu with the following command (take into account that I will always use sudo but it could be not necessary in your case)
sudo apt install certbot python3-certbot-apacheOnce you have Certbot installed you can generate your certificate, consider this: I’m using the standalone mode. Certbot can work with Apache, Nginx and in standalone mode, this means that you will run a webserver in your machine, to respond to the call of Let’s Encrypt servers, and for avoiding any error, be sure that the Certbot internal webserver will respond by guarantee that Certbot web server it’s the only one running at that moment. This means you have to turn off momentaneously any other webserver like Apache or Nginx if you have one installed in your machine.
Let’s assume your domain is sql.mydomain.com we will launch the following command:
sudo certbot certonly --standalone --preferred-challenges http -d sql.mydomain.comIf everything goes fine you should have the following message
Congratulations! Your certificate and chain have been saved at:
/etc/letsencrypt/live/sql.mydomain.com/fullchain.pem
Your key file has been saved at:
/etc/letsencrypt/live/sql.mydomain.com/privkey.pem
If you have arrived so far you have already a valid SSL certificate, for free, expiring in 90 days (this policy cannot be changed). Consider that Certbot automatically renews the certificates when they are about to expire, so you do not have to worry but in case you want to renew it manually, you can run this command:
sudo certbot renew --force-renewalProblems I have faced
At this point, I encountered various problems, so I make it simpler for you:
- The Certbot certificate key file is not in a format that SQL Server would understand
- The Certbot certificates are not readable by SQL server because they are generated by another user, and they don’t have the correct permission
So what to do now? It’s very simple if there is someone that looked into documentation for hours (me) that did this guide for you…
The Solution
What we need to do is: copy certificates in another folder, convert the key in the proper format and assign the folder and files the right permission, so let’s start:
The two files we need are in the folder we saw before /etc/letsencrypt/live/sql.mydomain.com/ and they are called fullchain.pem and privkey.pem
#Let's create a directory where to copy the files let's use one like these /var/opt/mssql/sslcerts/sudo mkdir /var/opt/mssql/sslcerts/#Let's copy the certificate that it is valid for SQL server and it is in the correct format to our foldersudo cp /etc/letsencrypt/live/sql.mydomain.com/fullchain.pem /var/opt/mssql/sslcerts/fullchain.pem#Now we need to copy the certificate key but as I said before the format is not the correct one for SQL server, so we need to convert it with the openssl tool that is integrated in most of Linux distributions, we take advantage of it and we create the correct file directly from the original directory to the destination one, so there is no need to copy it manuallysudo openssl rsa -in /etc/letsencrypt/live/sql.mydomain.com/privkey.pem -out /var/opt/mssql/sslcerts/privkey.key
Once you copied the certificate and created the RSA converted version of the key you should have the two files fullchain.pem and privkey.key in the folder /var/opt/mssql/sslcerts/
The next thing we need to do is changing the owner and permission of these files and folders, according to Microsoft documentation they say that the permission 400 (only msql group can read) should be fine but it didn’t work for me. What worked was 700 which means that the mssql group owner can read, write, execute so we do it so, I use the -R parameter to reflect the change recursively to the folder and files contained in it:
sudo chown -R mssql:mssql /var/opt/mssql/sslcerts/
sudo chmod -R 700 /var/opt/mssql/sslcerts/Now the last thing we need to do is follow the official guide and set the parameters of the SQL Server service to state that it can encrypt the connections with clients and use our certificates to do it so
sudo /opt/mssql/bin/mssql-conf set network.tlscert /var/opt/mssql/sslcerts/fullchain.pem
sudo /opt/mssql/bin/mssql-conf set network.tlskey /var/opt/mssql/sslcerts/privkey.key
sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
sudo /opt/mssql/bin/mssql-conf set network.forceencryption 0systemctl restart mssql-server.service
With the last command, we restarted SQL Server and the only thing we should do is to check that everything works as expected. In order to try to connect by SQL Management Studio, we need to flag the Encrypt connection in the following way:
If everything works fine and you connect, you did it! Consider that for connecting you will need to use the domain sql.mydomain.com and not the IP address of the machine. By not flagging the Trust server certificate, you guarantee that you connect to a server that has a valid CA certificate and if you want that the client is forced to connect with encryption, you should change the command so:
sudo /opt/mssql/bin/mssql-conf set network.forceencryption 1What we will need to do at the end, will be put a CRON task to copy the certificate and convert the key to RSA, every week, for example, to be sure that when the certificate is automatically regenerated we have the new version copied in the SQL Server folder.
If you connect with .Net to SQL Server don’t forget to add these parameters to the connection string encrypt=true;trustServerCertificate=false;
Documentation used for this article
