Does anyone have experience using JDBC drivers to access a Snowflake database? I've been able to access the sample database via the snowsql command line tool with no problem, but when I try to access the same using the Snowflake JDBC driver from the same machine, I get the following errors: `clj꞉demo.core꞉>` `(-main)` `-main - enter` `#object[next.jdbc.connection$url_PLUS_etc$reify__7177 0x79e563a6 jdbc:snowflake://<my-account>.<http://snowflakecomputing.com?networkTimeout=10&db=SNOWFLAKE_SAMPLE_DATA&warehouse=COMPUTE_WH&role=ACCOUNTADMIN|snowflakecomputing.com?networkTimeout=10&db=SNOWFLAKE_SAMPLE_DATA&warehouse=COMPUTE_WH&role=ACCOUNTADMIN>]` _`; Sep 28, 2023 1:45:13 PM net.snowflake.client.jdbc.RestRequest execute`_ _`; SEVERE: Error response: HTTP Response code: 403, request: POST https://<my-account>.<http://snowflakecomputing.com:443/session/v1/login-request?databaseName=SNOWFLAKE_SAMPLE_DATA&warehouse=COMPUTE_WH&roleName=ACCOUNTADMIN&requestId=e12be18c-65e7-4d3c-ad77-b37054c62ba7|snowflakecomputing.com:443/session/v1/login-request?databaseName=SNOWFLAKE_SAMPLE_DATA&warehouse=COMPUTE_WH&roleName=ACCOUNTADMIN&requestId=e12be18c-65e7-4d3c-ad77-b37054c62ba7> HTTP/1.1`_ _`;`_ _`; Sep 28, 2023 1:45:13 PM net.snowflake.client.core.HttpUtil executeRequestInternal`_ _`; SEVERE: Error executing request: POST https://<my-account>.<http://snowflakecomputing.com:443/session/v1/login-request?databaseName=SNOWFLAKE_SAMPLE_DATA&warehouse=COMPUTE_WH&roleName=ACCOUNTADMIN&requestId=e12be18c-65e7-4d3c-ad77-b37054c62ba7|snowflakecomputing.com:443/session/v1/login-request?databaseName=SNOWFLAKE_SAMPLE_DATA&warehouse=COMPUTE_WH&roleName=ACCOUNTADMIN&requestId=e12be18c-65e7-4d3c-ad77-b37054c62ba7> HTTP/1.1`_ _`;`_ _`; Sep 28, 2023 1:45:13 PM net.snowflake.client.jdbc.SnowflakeUtil logResponseDetails`_ _`; SEVERE: Response status line reason: Forbidden`_ _`;`_ _`; Sep 28, 2023 1:45:13 PM net.snowflake.client.jdbc.SnowflakeUtil logResponseDetails`_ _`; SEVERE: Response content: <html>`_ _`; <head><title>403 Forbidden</title></head>`_ _`; <body>`_ _`; <center><h1>403 Forbidden</h1></center>`_ _`; </body>`_ _`; </html>`_ _`;`_ _`;`_ _`; Execution error (SnowflakeSQLException) at net.snowflake.client.core.HttpUtil/executeRequestInternal (HttpUtil.java:781).`_ _`; JDBC driver encountered communication error. Message: HTTP status=403.`_ with this stack trace: `net.snowflake.client.core.HttpUtil/executeRequestInternal (HttpUtil.java:781)` `net.snowflake.client.core.HttpUtil/executeRequest (HttpUtil.java:684)` `net.snowflake.client.core.HttpUtil/executeGeneralRequest (HttpUtil.java:601)` `net.snowflake.client.core.SessionUtil/newSession (SessionUtil.java:612)` `net.snowflake.client.core.SessionUtil/openSession (SessionUtil.java:292)` `net.snowflake.client.core.SFSession/open (SFSession.java:481)` `net.snowflake.client.jdbc.DefaultSFConnectionHandler/initialize (DefaultSFConnectionHandler.java:112)` `net.snowflake.client.jdbc.DefaultSFConnectionHandler/initializeConnection (DefaultSFConnectionHandler.java:85)` `net.snowflake.client.jdbc.SnowflakeConnectionV1/initConnectionWithImpl (SnowflakeConnectionV1.java:116)` `net.snowflake.client.jdbc.SnowflakeConnectionV1/<init> (SnowflakeConnectionV1.java:96)` `net.snowflake.client.jdbc.SnowflakeDriver/connect (SnowflakeDriver.java:187)` `java.sql.DriverManager/getConnection (DriverManager.java:681)` `next.jdbc.connection/get-driver-connection (connection.clj:409)` `next.jdbc.connection/get-driver-connection (connection.clj:403)` `next.jdbc.connection/url+etc (connection.clj:428)` `next.jdbc.connection/make-connection (connection.clj:449)` `next.jdbc.connection/make-connection (connection.clj:436)` `next.jdbc.connection/eval7198 (connection.clj:481)` `next.jdbc.protocols/eval6928 (protocols.clj:25)` `next.jdbc/get-connection (jdbc.clj:177)` `next.jdbc/get-connection (jdbc.clj:147)` `demo.core/-main (core.clj:22)` `demo.core/-main (core.clj:10)` `demo.core/eval10359 (NO_SOURCE_FILE:28)` `... <more below> ...` The code from core.clj is as follows: `(ns coldcql.core` `(:require` `[next.jdbc :as jdbc :refer [execute-one! get-datasource get-connection]]))` `(def snow-url "jdbc:snowflake://<my-account>.<http://snowflakecomputing.com?networkTimeout=10&db=SNOWFLAKE_SAMPLE_DATA&warehouse=COMPUTE_WH&role=ACCOUNTADMIN|snowflakecomputing.com?networkTimeout=10&db=SNOWFLAKE_SAMPLE_DATA&warehouse=COMPUTE_WH&role=ACCOUNTADMIN>")` `(def snow-query "select CURRENT_DATABASE(), CURRENT_SCHEMA();")` _`;(def snow-ds (jdbc/get-datasource snow-url))`_ _`;-----------------------------------------------------------------------------`_ `(defn -main` `[& _]` `(println "-main - enter")` `(let [snow-ds (get-datasource snow-url)` `_ (println snow-ds)]` `(with-open [snow-conn (get-connection snow-ds "<my-username>" "<my-password>" {:read-only true})]` `(println snow-conn)` `(println (-> (execute-one! snow-conn snow-query) first val)))` `(println "-main - leave")))` The system seems to build the data source fine, but when I try to do a get-connection, it throws the aforementioned error. My deps.edn is using the latest versions of next.jdbc and the Snowflake JDBC driver: `:deps` `{org.clojure/clojure {:_mvn_/version "1.11.0"}` `org.clojure/tools.cli {:_mvn_/version "1.0.206"}` `com.github.seancorfield/next.jdbc {:_mvn_/version "1.3.894"}` `net.snowflake/snowflake-jdbc {:_mvn_/version "3.13.33"}` `}` Any ideas as to why I'd be getting an internal communication error from Snowflake when the same query works fine through their snowsql command line from the same machine? I've tried searching Snowflake's support and Googling the error, but nothing I've found seems salient to the issue.
yeah Properties is a string => string map
I finally got it to work. What did it was some combination of using the URL <my-organization>-<my-account>.http://snowflakecomputing.com/ (note the leading organization specified and the trailing "/") or setting the property hash to use "true" rather than true for the :read-only property.
I wrote a driver for it at Metabase. Give me a second
are you sure your creds are correct?
Yes. They're the same ones I used with snowsql. The password is the third parameter in the get-connection call. I've used the correct one in my code.
Try this. This works for me locally with my Snowflake creds
(let [account "ls12345.us-east-2.aws"
properties (doto (java.util.Properties.)
(.setProperty "user" "SNOWFLAKE_DEVELOPER")
(.setProperty "password" "*********")
(.setProperty "warehouse" "COMPUTE_WH"))]
(with-open [conn (java.sql.DriverManager/getConnection (format "jdbc:snowflake://%s.snowflakecomputing.com" account) properties)]
(next.jdbc/execute! conn ["SELECT current_database();"])))(replacing account/user/password/warehouse with the appropriate values)
Also, what Java version are you using? We're currently on Snowflake JDBC driver 3.14.1 and it doesn't work correctly in Java 17+... if you see an error like
Failed to initialize MemoryUtil. Was Java started with `--add-opens=java.base/java.nio=ALL-UNNAMED`? (See ) [in thread "nREPL-session-7bc65879-52fb-4985-8d79-672666acf49b"]
at net.snowflake.client.jdbc.internal.apache.arrow.memory.util.MemoryUtil.<clinit>(MemoryUtil.java:141)
Then try using Java 11 and that should fix itI got further, the code suggested gives the error: Execution error (SSLPeerUnverifiedException) at net.snowflake.client.jdbc.internal.apache.http.conn.ssl.SSLConnectionSocketFactory/verifyHostname (SSLConnectionSocketFactory.java:507). ; Certificate for <<http://<my-account>.http://us-west-1.aws.snowflakecomputing.com|<my-account>.http://us-west-1.aws.snowflakecomputing.com>> doesn't match any of the subject alternative names: [.http://prod3.us-west-2.snowflakecomputing.com, .http://us-west-2.snowflakecomputing.com, .http://global.snowflakecomputing.com, .http://snowflakecomputing.com, *.http://prod3.us-west-2.aws.snowflakecomputing.com] and the stack trace shows me getting a bit farther. I do have the --add-opens stuff added to the command line, so I don't get the MemoryUtil crash.
Try adding (.setProperty "ssl" "true") in there?
It also looks like they don't include the AWS region in your account name in any of the examples in the Snowflake dox https://docs.snowflake.com/developer-guide/jdbc/jdbc-configure#label-other-jdbc-connection-string-examples https://docs.snowflake.com/user-guide/admin-account-identifier
jdbc:I'll try going through east-1.
yeah so if I try to use just my account locator e.g. ls12345 I get that 403 forbidden error too
Maybe try us-west-2? It looks like that one would be valid
I'm also able to repro your exact error if I change the account in the connection string from us-east-2 to us-east-1 btw. So I'm guessing your region is supposed to be something else
With us-west-2, I'm back to the original error/stacktrace. I'll do some more digging on my end. I've got a meeting to attend soon. Thanks for your suggestions. I'll post back on this thread if I come up with anything new.