Can't connect to MS SQL Server

Get Help
1

Hi I am running the latest Metabase app on Mac and trying to connect to MS SQL Server Express.

I can connect fine via Azure Data Studio on the same Mac with the same connection info and Encrypt connection set to true, however i get the following error in Metabase:

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:XXXXXXXXXXXXXX

Any ideas on how to resolve this?

thanks

2

Hi @ssbssb
Do you define the certificate in ADS? Or are you just adding the connection string parameter? If you're not added a certificate, then I'm guessing the default of ADS might just be the following, which you can add to Connection String in Metabase > Admin > Databases > (your-db):
encrypt=true;trustServerCertificate=true;

If you have a certificate, then you'll need to add it into the TrustStore - and then I would not recommend using the Mac App - use the JAR file instead.
https://docs.microsoft.com/en-us/sql/connect/jdbc/configuring-the-client-for-ssl-encryption?view=sql-server-ver15

4

Hi - what is ADS? I am running MS SQL on an Ubuntu VPS with an SSL certificate from Lets Encrypt.

5

@ssbssb ADS = Azure Data Studio.

6

Ah ok - No - i didn't define or load any specific certificate in Azure Data Studio.

It's set to Encrypt=True and Trust Server Certificate=False in ADS

7

@ssbssb Okay, I don't know ADS, so don't know which certificate handling it does, but Metabase uses Java, so you'll need to make sure that Java understands the certificate from your server.

8

OK - how can i do that?

9

@ssbssb If you follow the link that I posted, then Microsoft have written a short article on how to import certificates into Java TrustStore. It's a huge subject and there are many resources on the internet, since it's not specific to Metabase.
But to begin with, make sure that you're not using the Mac App, but the JAR file instead.

10

Hi i have had a look - it still does not seem to be working. Not sure what the issue is because i have had no trouble connecting with ADS or .net core applications running on the same box without having to load any certificates

11

a lot of the examples are for writing your own Java code to connect. So i'm not sure how this would work for Metabase.

12

@ssbssb Java is not the same as ADS or .Net - it is it's own monster. But use the JAR and download Java 11: https://www.metabase.com/docs/latest/operations-guide/java-versions.html
It should include common CA's by default, so you shouldn't need to do anything.
Dealing with the TrustStore or KeyStore in Java is just painful to put it mildly.

13

OK - when I type java -version i have the following (this is on a Win10 machine). I'm not at the Mac right now.

java version "16.0.1" 2021-04-20
Java(TM) SE Runtime Environment (build 16.0.1+9-24)
Java HotSpot(TM) 64-Bit Server VM (build 16.0.1+9-24, mixed mode, sharing)

Is this the supported version?

I'm running Metabase from the JAR file

The CA I am using is:
Issued to: DST Root CA X3

  • DST Root CA X3
    -----R3
    ---------mydomain.com
14

Hi @ssbssb, we support Java 16 but we recommend you use OpenJDK 11 (https://adoptopenjdk.net/)

I don't know if you can send us the full logs of the instance when trying to connect so we can check that, and also try upgrading to 40.1 which has been released yesterday

thanks :wink:

15

Hi i've just upgraded to 40.1 and still getting the same error. How can i send you the logs?

I've also tried replacing the sqlserver.metabase-driver.jar in the plugins folder with one i downloaded from the Microsoft website (mssql-jdbc-9.2.1.jre15.jar).

It is all the same error unable to find a valid ceritifcation path to requested target.

16

@ssbssb You cannot replace the driver. Metabase uses specific drivers, not general JDBC drivers.

Post "Diagnostic Info" from Admin > Troubleshooting.
Check your MSSQL server log - perhaps there's some more logging there.

The problem is not the driver, but the certificate is not in your Java TrustStore. I'm not sure why, since I can connect to a database with LetsEncrypt certificate - guess it must be something on Windows.
https://stackoverflow.com/questions/34110426/does-java-support-lets-encrypt-certificates
https://community.letsencrypt.org/t/will-the-cross-root-cover-trust-by-the-default-list-in-the-jdk-jre/134

You can try with a tool like DBeaver.io, which is also Java based and used JDBC drivers.

17

Got it - there was three certificates that I needed to import and now it seems to work. Seems to work even with the MS SQL drivers i copied into the plugin folder

18

It wasn't also working on MacOS - i will import the other certificates there and give it ago. Apologies i'm not that familiar with Java / JDK so was a bit of a struggle to get this up and running

19

@ssbssb Perfect. Guess you might have been using another certificate (or included the wrong one).

The driver gets replaced, when you start Metabase, with the one included - this is to ensure old drivers are upgraded - so that's why the driver works (it's not the one you included).

Trust me - everyone is "new" to TrustStore/KeyStore until you have grey hair. Most other databases (Postgres, MySQL, etc) allows you to define the certificate in the Connection String, so you can avoid TrustStore.
We are working on adding certificate management in the interface of Metabase, but it's a difficult project and hopefully will be finished this year.

1 Like