sql 2023-01-13 | Slack Archive

vlad_poh20:01:21

Hi trying to use next.jdbc to connect to Microsoft SQL Server Database (using the microsoft driver) and running into the following error

#error {:cause "unable to find valid certification path to requested target"
        :via
        [{:type com.microsoft.sqlserver.jdbc.SQLServerException
          :message "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:d1b2f97e-d40c-4ff4-a156-fe84d8ffa5c4"
          :at [com.microsoft.sqlserver.jdbc.SQLServerConnection terminate "SQLServerConnection.java" 3806]}
         {:type javax.net.ssl.SSLHandshakeException
          :message "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target"
          :at [sun.security.ssl.Alert createSSLException "Alert.java" 131]}
         {:type sun.security.validator.ValidatorException
          :message "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target"
          :at [sun.security.validator.PKIXValidator doBuild "PKIXValidator.java" 388]}
         {:type sun.security.provider.certpath.SunCertPathBuilderException
          :message "unable to find valid certification path to requested target"
          :at [sun.security.provider.certpath.SunCertPathBuilder build "SunCertPathBuilder.java" 141]}]

Has anyone run into this issue? Not sure what to do next?

Cam Saul20:01:58

Can you connect to it with a JDBC connection string using java.sql.DriverManager/getConnection?

Cam Saul20:01:45

Which SQL Server JDBC driver are you using? com.microsoft.sqlserver/mssql-jdbc ?

seancorfield20:01:10

Is it a local SQL Server instance? Does it actually support SSL connections?

vlad_poh20:01:25

• same error with java.sql.DriverManager/getConnection • yep using com.microsoft.sqlserver/mssql-jdbc • No its not local and it supports ssl I was using clojure.jdbc.java and switched to next last night. It worked flawlessly in the repl. Restarted my machine this morning and it stopped working.

vlad_poh20:01:20

Old config

{:subprotocol "jtds:sqlserver"
       :classname   "net.sourceforge.jtds.jdbc.Driver"
       :dbtype "mssql"
       :dbname "x"
       :subname "//0.0.0.0:1433;database=x;user=x;password=x;ssl=yes"}

New config

{:dbtype "mssql"
      :dbname "x"
      :host "0.0.0.0"
      :user "x"
      :password "x"
      }

Cam Saul20:01:27

that's a different driver too, right?

Cam Saul20:01:49

JTDS is a different JDBC driver than com.microsoft.sqlserver/mssql-jdbc

seancorfield20:01:49

:dbtype "jtds" matches your old config.

Cam Saul20:01:13

If you want to switch over, I switched Metabase over from JTDS to the official one maybe 5 years ago. Here were the changes we had to make to the connection properties https://github.com/metabase/metabase/pull/5491/files#diff-7fca0c19daa91ab0488d8e9591fbba68d9fe2b822f95fff2ff11037be7a4a9a8L51-R79

👍 2

seancorfield20:01:58

:dbtype "mssql" uses the official Microsoft driver, :dbtype "jtds" uses the open-source jTDS driver.

seancorfield20:01:10

(you may need to add :ssl "yes" to get the same connection string if that isn't the default)

seancorfield20:01:12

Since the exception is coming from the MS driver, I assume you have added that as a dependency -- in addition or instead of the jTDS driver?

vlad_poh21:01:44

my lein project.clj

(defproject rxrdiag "0.1.1"
  :dependencies [[org.clojure/clojure "1.11.1"]
                 [compojure "1.7.0"]
                 [http-kit "2.6.0"]
                 [ring/ring-core "1.9.6"]
                 [ring/ring-jetty-adapter "1.9.6"]
                 [net.sourceforge.jtds/jtds "1.3.1"]
                 [com.microsoft.sqlserver/mssql-jdbc "11.2.3.jre18"]
                 [metosin/reitit "0.5.18"]
                 [cheshire "5.11.0"]
                 [org.clojure/data.xml "0.0.8"]
                 [tupelo/tupelo "22.08.03"]
                 [com.layerware/hugsql "0.5.3"]
                 ;[org.clojure/java.jdbc "0.7.12"]
                 [com.github.seancorfield/next.jdbc "1.3.847"]
                 [com.github.seancorfield/honeysql "2.4.962"]]
  :plugins  [[lein-ancient "1.0.0-RC4-SNAPSHOT"]
             [jonase/eastwood "1.2.3"]]
  :paths ["src"]
  :main rxr.core
  :uberjar-name "rxr.jar"
  :jar-name "rxr.jar"
  :repl-options {:init-ns rxr.core})

Cam Saul21:01:03

You don't need both

[net.sourceforge.jtds/jtds "1.3.1"]
                 [com.microsoft.sqlserver/mssql-jdbc "11.2.3.jre18"]

since they both are for SQL Server. Just use one or the other

👍 2

vlad_poh21:01:44

Setting

:ssl "yes"

didn’t work but setting

:trustServerCertificate "true"

worked.

Cam Saul21:01:53

I don't think jTDS has had a commit in 6 or 7 years, while the Microsoft driver is being actively developed

2023-01-13

Channels