Trouble connecting to SQLEXPRESS data

kenykill84 · February 9, 2021, 10:56pm

When trying to connect to my local SQLEXPRESS database, I get the “Timed out after 5,0 s” error.

I see in the docker logs that it says Database connection error.

I have enabled TCP/IP and set all TCP Ports to 1433

I am trying these values for my connection…

Database type: SQL Server
Name: <made up>
Host: <MACHINE NAME>\SQLEXPRESS
Port: 1433
Database name: master
Database instance name: SQLEXPRESS
Username:<username>
Password:<password>

I know the username and password are valid. Any ideas? I’d love to try creating some dashboards with Metabase. Cheers.

Luiggi · February 10, 2021, 1:37am

Hi @kenykill84, how are you deploying your SQLExpress (docker? local installation?) and Metabase? can you share your troubleshooting info?

thanks!

kenykill84 · February 10, 2021, 4:15am

Hi Luiggi. Thank you for the response.

My SQL Server Express is hosted locally on my WIN10 machine. I used Sql Server Connection Manager to enable TCP/IP and set all TCP Ports to 1443.

After filling out the Add Database fields, it always times out.

flamber · February 10, 2021, 9:44am

@kenykill84

Post “Diagnostic Info” from Admin > Troubleshooting.

<MACHINE NAME>\SQLEXPRESS is not a host - <MACHINE NAME> is

Try using another tool like DBeaver.io - it might help you figuring out what to input in each field.

kenykill84 · February 10, 2021, 10:53pm

Thank you for pointing me towards the logs. Here is my log after I try and connect.

[6ba46084-813c-4d7d-ab4c-9e6d2e61a0b3] 2021-02-10T14:48:32-08:00 ERROR metabase.api.database Cannot connect to Database
java.lang.Exception: Timed out after 5.0 s
at metabase.driver.util$can_connect_with_details_QMARK_.invokeStatic(util.clj:40)
at metabase.driver.util$can_connect_with_details_QMARK_.doInvoke(util.clj:23)
at clojure.lang.RestFn.invoke(RestFn.java:442)
at metabase.api.database$test_database_connection.invokeStatic(database.clj:412)
at metabase.api.database$test_database_connection.doInvoke(database.clj:402)
at clojure.lang.RestFn.invoke(RestFn.java:425)
at metabase.api.database$fn__60285$test_connection_details__60290$fn__60291.invoke(database.clj:459)
at metabase.api.database$fn__60285$test_connection_details__60290.invoke(database.clj:441)
at metabase.api.database$fn__60343.invokeStatic(database.clj:493)
at metabase.api.database$fn__60343.invoke(database.clj:480)
at compojure.core$wrap_response$fn__1993.invoke(core.clj:160)
at compojure.core$wrap_route_middleware$fn__1977.invoke(core.clj:132)
at compojure.core$wrap_route_info$fn__1982.invoke(core.clj:139)
at compojure.core$wrap_route_matches$fn__1986.invoke(core.clj:151)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005.invoke(core.clj:200)
at metabase.middleware.auth$enforce_authentication$fn__72935.invoke(auth.clj:14)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005.invoke(core.clj:200)
at compojure.core$make_context$handler__2033.invoke(core.clj:287)
at compojure.core$make_context$fn__2035.invoke(core.clj:296)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at compojure.core$make_context$fn__2035.invoke(core.clj:297)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at compojure.core$make_context$fn__2035.invoke(core.clj:297)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at compojure.core$make_context$fn__2035.invoke(core.clj:297)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at compojure.core$make_context$fn__2035.invoke(core.clj:297)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at compojure.core$make_context$fn__2035.invoke(core.clj:297)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at compojure.core$make_context$fn__2035.invoke(core.clj:297)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at metabase.api.routes$fn__73056$fn__73058.invoke(routes.clj:73)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005.invoke(core.clj:200)
at clojure.lang.AFn.applyToHelper(AFn.java:160)
at clojure.lang.AFn.applyTo(AFn.java:144)
at clojure.core$apply.invokeStatic(core.clj:665)
at clojure.core$apply.invoke(core.clj:660)
at metabase.routes$fn__74385$fn__74386.doInvoke(routes.clj:60)
at clojure.lang.RestFn.invoke(RestFn.java:436)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005.invoke(core.clj:200)
at compojure.core$make_context$handler__2033.invoke(core.clj:287)
at compojure.core$make_context$fn__2035.invoke(core.clj:296)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at compojure.core$wrap_route_matches$fn__1986.invoke(core.clj:153)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at compojure.core$wrap_route_matches$fn__1986.invoke(core.clj:153)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at compojure.core$wrap_route_matches$fn__1986.invoke(core.clj:153)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005$f__2006$respond_SINGLEQUOTE___2007.invoke(core.clj:197)
at metabase.routes$fn__74373$fn__74375.invoke(routes.clj:44)
at compojure.core$routes$fn__2005$f__2006.invoke(core.clj:198)
at compojure.core$routes$fn__2005.invoke(core.clj:200)
at metabase.middleware.exceptions$catch_uncaught_exceptions$fn__73047.invoke(exceptions.clj:96)
at metabase.middleware.exceptions$catch_api_exceptions$fn__73044.invoke(exceptions.clj:84)
at metabase.middleware.log$log_api_call$fn__74833$fn__74834.invoke(log.clj:197)
at toucan.db$_do_with_call_counting.invokeStatic(db.clj:216)
at toucan.db$do_with_call_counting.invoke(db.clj:209)
at metabase.middleware.log$log_api_call$fn__74833.invoke(log.clj:191)
at metabase.middleware.security$add_security_headers$fn__73010.invoke(security.clj:143)
at metabase.middleware.json$wrap_json_body$fn__74536.invoke(json.clj:64)
at metabase.middleware.json$wrap_streamed_json_response$fn__74554.invoke(json.clj:100)
at ring.middleware.keyword_params$wrap_keyword_params$fn__75099.invoke(keyword_params.clj:55)
at ring.middleware.params$wrap_params$fn__75115.invoke(params.clj:69)
at metabase.middleware.misc$maybe_set_site_url$fn__34513.invoke(misc.clj:59)
at metabase.middleware.session$bind_current_user$fn__69514$fn__69515.invoke(session.clj:278)
at metabase.middleware.session$do_with_current_user.invokeStatic(session.clj:260)
at metabase.middleware.session$do_with_current_user.invoke(session.clj:252)
at metabase.middleware.session$bind_current_user$fn__69514.invoke(session.clj:277)
at metabase.middleware.session$wrap_current_user_info$fn__69501.invoke(session.clj:238)
at metabase.middleware.session$wrap_session_id$fn__69487.invoke(session.clj:184)
at metabase.middleware.auth$wrap_api_key$fn__72943.invoke(auth.clj:27)
at ring.middleware.cookies$wrap_cookies$fn__75019.invoke(cookies.clj:216)
at metabase.middleware.misc$add_content_type$fn__34498.invoke(misc.clj:28)
at metabase.middleware.misc$disable_streaming_buffering$fn__34521.invoke(misc.clj:76)
at ring.middleware.gzip$wrap_gzip$fn__75061.invoke(gzip.clj:86)
at metabase.middleware.misc$bind_request$fn__34524.invoke(misc.clj:93)
at metabase.middleware.ssl$redirect_to_https_middleware$fn__74850.invoke(ssl.clj:39)
at metabase.server$async_proxy_handler$fn__74611.invoke(server.clj:72)
at metabase.server.proxy$org.eclipse.jetty.server.handler.AbstractHandler$ff19274a.handle(Unknown Source)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:516)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:556)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:273)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:773)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:905)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.util.concurrent.TimeoutException: Timed out after 5.0 s
at metabase.util$deref_with_timeout.invokeStatic(util.clj:315)
at metabase.util$deref_with_timeout.invoke(util.clj:307)
at metabase.util$do_with_timeout.invokeStatic(util.clj:321)
at metabase.util$do_with_timeout.invoke(util.clj:318)
at metabase.driver.util$can_connect_with_details_QMARK.invokeStatic(util.clj:34)
… 106 more
[6ba46084-813c-4d7d-ab4c-9e6d2e61a0b3] 2021-02-10T14:48:32-08:00 DEBUG metabase.middleware.log POST /api/database 400 10.0 s (0 DB calls)
{:valid false, :dbname “Timed out after 5.0 s”, :message “Timed out after 5.0 s”}

kenykill84 · February 10, 2021, 10:56pm

I have removed the ‘\SQLEXPRESS’ from the Host field. Now it is only <MACHINE NAME>

Still no luck. I figure I could get it working on something besides SQL Server Express, but I’m not quite done trying to sort this out yet. I appreciate the help.

flamber · February 11, 2021, 10:32am

@kenykill84

Post "Diagnostic Info" from Admin > Troubleshooting - not the logs.

The machine name needs to be accessible from where Metabase runs. Check your DNS and firewall.

And try testing with a different tool like DBeaver.io
I don't know your setup, so I cannot tell you what to input in the various fields.
But this is how it looks on one of my setups, where Metabase is running on the same host as MSSQL:

kenykill84 · February 11, 2021, 10:04pm

My bad. Here are my diagnostics. Also, I added a new inbound rule on windows firewall on port 1433 to allow connections. (dunno if this was the right thing to do though)

{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.10+9”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.10”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.10+9”,
“os.name”: “Linux”,
“os.version”: “5.4.72-microsoft-standard-WSL2”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“h2”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2021-01-26”,
“tag”: “v0.37.8”,
“branch”: “release-x.37.x”,
“hash”: “490cea7”
},
“settings”: {
“report-timezone”: null
}
}
}

flamber · February 11, 2021, 10:13pm

@kenykill84 I have no idea how WSL2 handles localhost, but try a different tool or use Docker instead (which then means you should not use localhost, since that’s the docker container itself).

kenykill84 · February 11, 2021, 10:39pm

Thank you for the suggestions. I’ll keep plugging away.

kenykill84 · February 11, 2021, 11:29pm

I connected DBeaver to my SQL Server successfully, but obviously that was with host:localhost. I have Metabase running in a Docker container. I tried using my ip address in the host field and it ‘succeeds’ but the data is just the default folders…

Spt Fallback Db
Spt Fallback Dev
Spt Fallback Usg
Spt Monitor
Spt Values

kenykill84 · February 11, 2021, 11:52pm

Heyooo. So I tried writing a query in Metabase and wouldn’t you know it, I AM connected. Now I am just confused as to why I only see those 5 folders when I ‘Browse Data’.

Thank you for taking the time to help.

kenykill84 · February 12, 2021, 12:23am

…and now that I’ve swapped ‘master’ for the name of the specific db I was working with, my tables show.

I think I’m off and running now. Thanks again for the help!

turker.tunali · February 17, 2021, 2:52pm

For SQL Server connection, you may also try to empty the port. SQL Server uses dynamic port for each instance. I mean SERVER_NAME\SQLEXPRESS may use port 5000 and SERVER_NAME (Which uses default instance) may use port 6000.
If you don’t define the port in the connection settings, Metabase tries to find the dynamic port. DBeaver also may behave like that because of the underlying java library.