How to encrypt connection on SQL Server on Linux via SSL

  • 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.
sudo apt install certbot python3-certbot-apache
sudo certbot certonly --standalone --preferred-challenges http -d sql.mydomain.com
sudo certbot renew --force-renewal

Problems I have faced

At this point, I encountered various problems, so I make it simpler for you:

  • The Certbot certificates are not readable by SQL server because they are generated by another user, and they don’t have the correct permission

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:

#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
sudo chown -R mssql:mssql /var/opt/mssql/sslcerts/
sudo chmod -R 700 /var/opt/mssql/sslcerts/
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 0
systemctl restart mssql-server.service
Encrypt the connection
sudo /opt/mssql/bin/mssql-conf set network.forceencryption 1

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Vincenzo Pucarelli

Vincenzo Pucarelli

1 Follower

Project Manager and former Architect & Developer specialized in Microsoft technologies since 2004. Living in Madrid, Spain. Techie photographer, travels, wines.