How to encrypt connection on SQL Server on Linux via SSL

  • 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.
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

  • 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

The Solution

#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

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

SSL Decoded …

{UPDATE} 星星大消除 - 不一样的游戏规则 Hack Free Resources Generator

{UPDATE} Hangman Pro Hack Free Resources Generator

Gear up community for @thedapplist 11th Quiz! ✨

The Impossible Role of the CISO

NIST 800–53 Series: “IMPLEMENTING ACCESS CONTROLs-Part-1 SOPs”

{UPDATE} 暴走英雄壇-自由武俠 逗趣江湖 Hack Free Resources Generator

#EPNS $PUSH is live on @kucoincom 🚀🚀

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

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

More from Medium

OData for ASP.NET Core 6.0 on Mac via command line

Setup Docker Container as Test environment in Local Development using shell script

Git and GitLab vs Github

How to Upload Your Code to GitHub Using GitHub Desktop