I've created countless Docker containers running SQL Server since I first wrote about it back in 2016, but I recently had my first foray into configuring SQL Server 2019 on a real live Linux machine.

It did not go as smoothly as I expected, so I wanted to share the solution to a particular problem I haven't seen described elsewhere.

First, let me retrace my steps. I logged into the server (I'll call it "FunkyChicken" for simplicity) via ssh, and got into root mode. This probably makes some purists gasp, but it's so much easier than typing sudo or pretty-pretty-please all the time:

sudo -s

(You know you're root because the command prompt changes from a $ to a #; whoami returns root; and everything becomes easier.)

We use CentOS 7.5, so I grabbed the latest 2019 repo from here:

curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo

I installed SQL Server and started configuration:

yum -y install mssql-server
/opt/mssql/bin/mssql-conf setup

I chose Developer Edition (this is a proof of concept in our dev environment, after all), accepted the EULA, and set the sa password to something like 5w0rDf15h. Then I installed the command line tools, and connected via sqlcmd:

yum -y install mssql-tools
sqlcmd -S localhost -U sa

Entered my password, checked the version, and everything looked good:

1> SELECT @@version;
2> GO
 
---------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) 
    May 27 2021 17:34:14 
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core)) <X64>

I cracked a cider to celebrate my success.

However…

…when I tried to connect from my own machine or a jump box, both of which have latest sqlcmd / ADS / SSMS, I received these errors:

I checked the SQL Server errorlog, which usually gives more details about login failures, but these attempts were not making it to the errorlog. Searches for the error messages bore little fruit aside from suggestions to correct TLS settings in the Windows registry (!) and a KB article about a similar issue that was fixed in SQL Server 2017 for Linux (KB #4548001, which had me chasing OpenSSL updates and repairs that also had no impact).

I verified using PowerShell that I could connect on the right ports:

Test-NetConnection FunkyChicken -Port 1433
Test-NetConnection FunkyChicken -Port 1434

Those both came back successful; still, I checked, and neither ufw nor firewalld were active, so I knew it wasn't any kind of IP rejection or routing issue. After consulting with my good friend Anthony Nocentino (blog) and validating with Wireshark, we agreed this was some kind of SSL/TLS-related issue. I set up an Extended Events session to capture process_login_finish and captured error 17830 (state 105) coinciding with my failed login attempt. This further solidified that my requests were getting to SQL Server, but then getting rejected. So I checked the system health session and, lo and behold, a more detailed error message was captured within connectivity_ring_buffer_recorded:

I knew this wasn't a timeout, and the additional error code led me to this post:

• SQL Server connectivity, Kerberos authentication and SQL Server SPN (Service Principal Name for SQL Server)

While I could be certain this wasn't a Kerberos issue, this did lead me to request some help from one of our folks well-versed in both Linux and security in general.

A solution?

It turns out we needed a local certificate so SQL Server can negotiate TLS, and this certificate wasn't in place when the machine was built (now we know!). I used openssl to create one, then set permissions so that SQL Server would have no problems accessing them:

openssl req -x509 -nodes                  \
        -newkey rsa:2048                  \
        -subj '/CN=FunkyChicken'          \
        -keyout /etc/ssl/certs/mssql.key  \
        -out /etc/ssl/certs/mssql.pem     \
        -days 365
 
chown mssql:mssql /etc/ssl/certs/mssql.key
chown mssql:mssql /etc/ssl/certs/mssql.pem
chmod 400 /etc/ssl/certs/mssql.key
chmod 400 /etc/ssl/certs/mssql.pem
 
ls -al /etc/ssl/certs/

Output:

...
-r--------. 1 mssql mssql 1704 Jun 23 11:48 mssql.key
-r--------. 1 mssql mssql 1180 Jun 23 11:48 mssql.pem
...

Then I had to update the [network] properties of mssql.conf, so SQL Server would know to use this cert. Since I used RSA I only included the RSA ciphers; YMMV.

/opt/mssql/bin/mssql-conf set network.forceencryption 0
/opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/mssql.pem
/opt/mssql/bin/mssql-conf set network.tlsciphers \
        ECDHE-RSA-AES128-GCM-SHA256:   \
        ECDHE-RSA-AES256-GCM-SHA384:   \
        ECDHE-RSA-AES128-SHA256:       \
        ECDHE-RSA-AES256-SHA384:       \
        ECDHE-RSA-AES256-SHA:          \
        ECDHE-RSA-AES128-SHA
/opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/certs/mssql.key
/opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2

To verify:

view /var/opt/mssql/mssql.conf

That section now looks like this:

Finally, I restarted SQL Server:

systemctl restart mssql-server

Success

Then, voila! sqlcmd and SSMS on the jump boxes, as well as Azure Data Studio on my own workstation, could connect successfully. That at least solves the SQL Authentication problem… next I have to move on to adding all our groups and roles from AD/Kerberos. But we're getting somewhere!

Further reading

Some additional articles that helped along the way:

• Install Microsoft SQL Server 2019 on CentOS 7 / Fedora
• Configure SQL Server on Linux with the mssql-conf tool
• Encrypting Connections to SQL Server on Linux
• SQL Server 2019 on Linux, configuring SSL connections