TLS connection issue due to incomplete certificate chain

[Yannik]

Hi,

I have configured mssql server with TLS using this role, however, connections from linux systems to the SQL server fail with a certificate validation error. This is most likely due to the intermediate certificate not being provided by the SQL server, and the certificate chain therefore being incomplete. (Note: connecting from windows works fine due to AIA-fetching).

Thus, my question is: how to configure this in a way that the intermediate certificate is correctly supplied by the mssql server?
I have tried configuring mssql_tls_cert to a file that contains both the intermediate and the server cert, but that didn't help.

Best regards
Yannik

[spetrosi]

Hello @Yannik, thank you for opening an issue.
Please share the playbook that you used, and what operating system you run for the Ansible managed and control nodes?

[Yannik]

Hi @spetrosi

This is the config I used:

mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula: true
mssql_accept_microsoft_cli_utilities_for_sql_server_eula: true
mssql_accept_microsoft_sql_server_standard_eula: true
mssql_version: 2022
mssql_password: "XXX"
mssql_edition: Express
mssql_manage_firewall: true
mssql_tls_enable: true
mssql_tls_remote_src: true
mssql_tls_cert: /etc/ssl/letsencrypt/mssql.XXX.com/chained.crt
mssql_tls_private_key: /etc/ssl/letsencrypt/mssql.XXX.com/domain.key
mssql_tls_force: true

Server OS: almalinux9.

My computer (=control node) is running fedora 39.

[amitkh-msft]

Could you also please share the error message you see when connecting to SQL Server? Can you please help explain more about the intermediate certificate issue you are talking about. If your client trusts the server certificate then you should not have an issue.

[Yannik]

Hi @amitkh-msft

This is the error message:
[08S01] The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:772205c5-6173-4f19-8593-9ac3c1f0b43e unable to find valid certification path to requested target..

Can you please help explain more about the intermediate certificate issue you are talking about. If your client trusts the server certificate then you should not have an issue.

The server cert is a normal certificate issued by a public CA.
As it is the common practice (mandated by the CA/Browser forum, most likely), the server certificate is not signed by the Root CA, but by an intermediate CA.
The client trusts the root CA.
To make sure the client can correctly build the trust-path between the server cert and the root CA, the server must send the intermediate certificate alongside the server certificate. For some reason, mssql on linux is not doing that correctly.

Does that help? That is like the 101 on how PKI works, and it does work without issue running mssql on windows.