I am hosting a few linux SQL Server 2019 instances. All connections to these instances should be encrypted, and therefore I enforce the encryption on the server side like this (following the official instructions here):
/opt/mssql/bin/mssql-conf set network.tlscert /etc/opt/mssql/mssql.pem
/opt/mssql/bin/mssql-conf set network.tlskey /etc/opt/mssql/mssql.key
/opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
/opt/mssql/bin/mssql-conf set network.forceencryption 1
The mssql certificate is a self-signed certificate.
To verify that connections are indeed encrypted I use the following script on the server:
select session_id, encrypt_option from sys.dm_exec_connections
When I execute this on the server I can see that indeed all connections are encrypted (except some entries without a session ID, which I assume are internal connections).
Now comes the puzzling thing: since the certificate is self-signed I expect clients to reject connections if they do not explicitly trust that certificate. However, that appears to only work if the client also explicitly enforces the encryption.
With sqlcmd on a Windows machine that does not trust the certificate I get the following:
> sqlcmd -S db.mydomain.com -U user -P password -Q "select encrypt_option from sys.dm_exec_connections where session_id = @@spid"
encrypt_option
----------------------------------------
TRUE
(1 rows affected)
It seems the encryption is working and I do not get a certificate validation error. However, if I enforce encryption on the client (i.e. -N) I get this:
> sqlcmd -S db.mydomain.com -U user -P password -Q "select encrypt_option from sys.dm_exec_connections where session_id = @@spid" -N
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SSL Provider: The certificate chain was issued by an authority that is not trusted.
.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.
If I trust the certificate explicitly (i.e. -C) then the connection succeeds again:
> sqlcmd -S db.mydomain.com -U user -P password -Q "select encrypt_option from sys.dm_exec_connections where session_id = @@spid" -C -N
encrypt_option
----------------------------------------
TRUE
(1 rows affected)
If I add the self-signed certificate to my trusted root authorities on the client machine all connections (with or without -N) also succeed as expected.
The same happens when connecting through SQL Server Management Studio (SSMS). When I just connect to the database with default settings, it works without validation error. If I select Encrypt connection in the Connection Properties then I get a certificate validation error.
Is this normal? I would have expected the client to always validate the certificate, regardless of who enforces encryption.
