Adding SQL Server with named Instance as source

1

Hi all, we are trying out Metabase, and we have a SQL Server 2016 database as source, with a named instance. Metabase is installed on AWS Elastic Beanstalk.

I’ve tried putting the details in, using the Instance Name field for the Instance Name. For SSMS we use the format IP Address\INSTANCENAME, and that format doesn’t seem to work for Metabase. We always get a time-out.

We also have a default instance running on the same server, which works just fine. So the issue seems to be the named instance. Does anyone have any tricks for connecting a SQL Server database with a non-default instance name?

2

We expect the hostname to be a valid dns entry.

We use http://jtds.sourceforge.net/ to connect to SQL Server. You might be able find an alternative connection string format ther.

3

Is there a way to see the generated connection string? I removed any special characters in the password that may have been mangled in the URL encoding, but still no luck.

May 04 15:01:13 ERROR metabase.driver :: Failed to connect to database: Timed out after 5000 milliseconds. May 04 15:01:18 ERROR metabase.driver :: Failed to connect to database: Timed out after 5000 milliseconds. May 04 15:01:18 DEBUG metabase.middleware :: POST /api/database 400 (10 s) (0 DB calls) {:valid false, :dbname "Timed out after 5000 milliseconds.", :message "Timed out after 5000 milliseconds."}

4

Did you manage to fix this? I’m having the same problem.

5

Fixed it myself (why is it that asking a question makes it easier to find the answer?)

For anybody else who comes along:
If you’re running SQL on a named instance, rather than the defaul, there’s a good chance it’s using a non-standard port. Metabase defaults to 1433, so you’ll need to enter the correct port.
A normal ODBC connection has the sense to first connect to the listener on 1434 to find out the correct port.
I’m not sure how often the dynamic port changes. I’ve left mine set for now, but may need to change SQL to use a fixed port if it changes.

For the record, quickest way to get the correct port is
SELECT DISTINCT
local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL

2 Likes
6

Thanks @AndrewMBaines, you helped me fix my problem that has been plaguing me since Metabase changed to the Microsoft JDBC driver in 0.26. When I first read your post, I didn’t think your description pertained to me because I have always connected (via Metabase, .NET apps, etc) via port 1433. But your other sentence explained why those worked and new driver won’t:

“A normal ODBC connection has the sense to first connect to the listener on 1434 to find out the correct port.”

I changed SQL Server to use a fixed port, entered that port in Metabase (0.28.6) and it finally worked.

Your SQL to obtain the correct port did not work for me. Using the following did work:

USE master
GO

Xp_readerrorlog

And then look for “Server is listening on…” to see the port being used. When using dynamic ports, the port is changed whenever the server instance is started.

To set a fixed port, see the following:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-2017

Only change those IPs that have Enabled set to Yes - be sure to remove any numbers on the TCP Dynamic Ports entry before setting the static port. If you don’t, you will get the following problem:

Be sure to restart the server after making the change.

3 Likes