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
pretty-pretty-please all the time:
(You know you're root because the command prompt changes from a
$ to a
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
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.
…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
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
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.
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/
... -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
That section now looks like this:
forceencryption = 0
tlscert = /etc/ssl/certs/mssql.pem
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
tlskey = /etc/ssl/certs/mssql.key
tlsprotocols = 1.2
Finally, I restarted SQL Server:
systemctl restart mssql-server
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!
Some additional articles that helped along the way: