MS SQL data source: Disable SSL certificate verification doesn't work

[jericvizmanos]

What would you like to be added:
Disable SSL certificate verification for MS SQL Server Datasource

Why is this needed:
Encryption for MS SQL Server datasource has been added recently on #13629

While most data source allow disabling TLS verification, MS SQL datasource does not. Currently, there is no way to provide the CA Certificate in the datasource's configuration resulting in an error.

Error:
TLS Handshake failed: x509: certificate signed by unknown authority

[marefr]

Have you tried configured encrypt to disable?

[marefr]

Also what Grafana version are you using? That feature was implemented quite long time ago and not recently.

[jericvizmanos]

Hi @marefr - Thanks for checking this out! Yup. If encrypt = false, it goes through properly. However, when you set encrypt = true, it throws the error. Currently, there's no way to attach or place the certificate.

It's for Grafana v6.7.3.

[fifofonix]

Are we sure that encryption works at all for SQLServer connections?

We are getting the same TLS handshake error despite using a properly signed certificate.

We are using Grafana v6.5.2 (742d165) deployed in docker connecting to SQL Server 2017 (RTM) - 14.0.1000.169 (X64).

Unencrypted connection work fine. And we are able to establish encrypted connections to SQLServer via other tools, e.g. SQLServerMgmtStudio without the need to explicitly trust the certificate (meaning it is validated via machine-configured trusted CAs) and also DBeaver (although because this is Java we need to specify a trust bundle).

Furthermore from the Grafana docker container command line we are able to execute openssl handshakes to other TLS-secured resources with the same CA chain as our SQLServer certificate. And, we are able to complete openssl handshakes to the SQLServer itself - although I should note that this is a bizarre handshake as it shows no cipher selection - but I believe this is because Microsoft does something strange with its secure connections - schannel?

Without a connection-specific means to specify a trusted CA, my assumption is that the docker container's trust chain will be used, but perhaps as OP suggests there may need to be an explicit way to pass in a trusted CA if this is not respected?

[marefr]

I would suggest have a look at readme/issues at library we're using for connecting to mssql to figure out what's possible missing in Grafana: https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn

[fifofonix]

Thanks @marefr. I cloned the repo and was able to run a few tests on my Mac. I have two observations. For the purposes of these I'm presuming that grafana builds a DSN string behind the scenes in the sqlserver:// format that this library supports.

• Passing TrustServerCertificate=true will solve OP's issue

% export SQLSERVER_DSN=sqlserver://<hostname>?user+id=DOMAIN\\<username>\&encrypt=true\&password=<URL escaped password>\&TrustServerCertificate=true
% go test # passes

• (Go) Certificate Handling

It seems Go has its own way of specifying trusted platform CAs and on a Mac this does NOT involve using those in KeyChain. For this reason in my tests I received same error as OP when using a validly signed cert on SQLServer. However, the library does support specifying CA certs explicitly and tests of this work:

% export SQLSERVER_DSN=sqlserver://<hostname>?user+id=DOMAIN\\<username>\&encrypt=true\&password=<URL escaped password>\&certificate=/a/local/ca/cert.pem
% go test # passes

Whether the right longer term solution for Grafana is to allow the cert to be explicitly specified via UI or configuration, or to have users modify their local platform installation to include certs in a Go-supported location is up for debate. Regardless, an update to SQLServer Data Source documentation in the short term, so it is understood that encryption with certificate verification requires that certificates for trusted CAs must be in Go-supported locations, may be appropriate.

Testing with a minimal alpine docker container I have validated that the go library will use /etc/ssl/cert.pem for its certificates if it is present, and if not use /etc/ssl/certs/ca-certificates.crt. Even if your CA root of trusts are set correctly in ca-certificates you will get the error message OP posted. In the base alpine Go docker image it is perhaps for this reason that /etc/ssl/cert.pem is symbolically linked to ca-certificates.crt thereby avoiding confusion. This is not the case in grafana/grafana:6.5.2.

If you want to bake a missing CA cert into your own grafana docker image, and have them picked up by the go library the following additions to your dockerfile might do the trick (works for me):

...
USER root
RUN rm /etc/ssl/cert.pem
RUN ln -s /etc/ssl/certs/ca-certificates.crt /etc/ssl/cert.pem
ADD ./<your-ca-cert> /usr/local/share/ca-certificates
RUN update-ca-certificates
USER 472
...

[marefr]

@fifofonix sorry for the late response. Thanks for great investigation and writing this up, appreciate it. I would suggest you to open a PR if your interesting in getting this feature implemented. Similar functionality have already been added to the Postgres data source so should be pretty straight forward (I think) implementing this looking at the postgres implementation:

Postgres frontend code and backend code for reference.

Let me know if you have any questions/concerns

[mortenaa]

Hi, we had this same issue when connecting to a Mssql server with a self signed certificate, so I had a go at fixing it.
#31865