SQL Server 2019 on Linux, configuring SSL connections

Logo

Introduction

Modifying the port of SQL Server installed on a WAN/DMZ area is not enough from a security point of view, it decreases potential attacks but does not prevent some sniffer tools from reading TCP packets.

Architecture Linux SQL Server 2019

Using tcpdump, it is easy to capture network TDS packets and then read the content of these packets (requests and responses) with WireShark for example :

root@vps$ tcpdump -nnXSs 0 'port 35027' -w mssql.pcap
mssql.pcap (read by Wireshark)
SQL Batch (Request)  Response TDS
===================  ================
S.E.L.E.C.T. .T.     0.9.1...a.d.v.e.
O.P. .(.1.0.0.0.     n.t.u.r.e.-.w.o.
). .[.B.u.s.i.n.     r.k.s.\.s.a.m.e.
e.s.s.E.n.t.i.t.     e.r.0...........
y.I.D.]..... . .     ..z.........8.P.
 . . . .,.[.N.a.     r.o.d.u.c.t.i.o.
                     n. .T.e.c.h.n.i.
...                  c.i.a.n. .-. .W.
                     ...
e.]..... . .F.R.
O.M. .[.A.d.v.e.
n.t.u.r.e.W.o.r.
k.s.]...[.H.u.m.
a.n.R.e.s.o.u.r.
c.e.s.]...[.E.m.
p.l.o.y.e.e.].

Queries, table and column names, data sent and received are human readable.

Let’s see how to encrypt the packets with SSL / TLS 1.2 for SQL Server on Linux. The encryption is initiated and forced by the SQL Server engine using a self-signed certificate : that’s the easier method, there is no need to validate the server certificate by an authority for the connections, method that implies a configuration to be done on clients.

openssl

Check the OpenSSL version installed on the system :

mssql@vps$ which openssl
/usr/bin/openssl
mssql@vps$ openssl version
OpenSSL 1.1.1  11 Sep 2018

If for any reason (security and so on…) a specific version of OpenSSL other than the system one must be used : in the directory /opt/mssql/lib/ create symbolic links named libcrypto.so and libssl.so to the right version of openssl librairies.

Generating certificates

As root, with OpenSSL, generate the certificates in the directory where you want to install them : 

root@vps$ cd /opt/mssql/dba/srvmssql/ssl

root@vps$ openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=vps.mydomain.com' \
            -keyout mssql.key \
            -out mssql.pem \
            -days 365
  • The parameter CN must be the host fully-qualified name (FQN).
  • The certificates are valid for 1 year (-days 365)

The output should be like this : 

Generating a RSA private key
........................................................+++++
..........................................................+++++
writing new private key to 'mssql.key'

If the following error message is displayed : 

Can't load /root/.rnd into RNG
140480710107584:error:2406F079:random number generator:RAND_load_file:Cannot open file:../crypto/rand/randfile.c:88:Filename=/root/.rnd

Comment out the following line in the file /etc/ssl/openssl.cnf : 

/etc/ssl/openssl.cnf
# RANDFILE              = $ENV::HOME/.rnd

Set ownership to mssql and appropriate permissions on the generated certificates : 

root@vps$ chown mssql:mssql mssql.pem mssql.key
          
root@vps$ chmod 600 mssql.pem mssql.key

SQL Server configuration with mssql-conf

When the encryption is initiated by the SQL Server engine, just need the following parameters to be applied with the utility mssql-conf.

mssql@vps$ /opt/mssql/bin/mssql-conf set network.tlscert /opt/mssql/dba/srvmssql/ssl/mssql.pem 
mssql@vps$ /opt/mssql/bin/mssql-conf set network.tlskey /opt/mssql/dba/srvmssql/ssl/mssql.key 
mssql@vps$ /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2 
mssql@vps$ /opt/mssql/bin/mssql-conf set network.forceencryption 1

The service mssql-server is then restarted : 

mssql@vps$ sudo systemctl restart mssql-server

In the log file of SQL Server, if there is no problem, the following informations should be logged : 

2019-06-01 14:09:35.51 Server      Successfully initialized the TLS configuration.
Allowed TLS protocol versions are ['1.2'].
Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].

Verifying SSL / TLS 1.2 connections

Using sqlcmd, SQL Server Management Studio 18, ODBC… encryption is now forced by the server.

The view sys.dm_exec_connections confirms the encryption : 

mssql@vps$ sqlcmd -Usa -S vps
        
select encrypt_option from sys.dm_exec_connections where session_id=64
go
encrypt_option
----------------------------------------
TRUE
ODBC Test connection with encryption

Another capture of the packets with tcpdump shows now TLS protocol 1.2 with encrypted data

root@vps$ tcpdump -nnXSs 0 'port 35027' -w mssql.pcap
Wireshark capture TLS 1.2 Protocol

Conclusion

Steps to implement encryption with SSL/TLS 1.2 for SQL Server on Linux are very easy, we could even say easier than on Windows.

The port is modified, SSL/TLS encryption is implemented for client/server connections : now your SQL Server / Linux in the WAN/DMZ area is very more secured.