Connect Metabase to Sql Server

Question

I installed Java on my Windows 10 machine and downloaded Metabase.jar, and ran it.

Now I could access metabase at localhost:3000

But I'm unable to setup my SQL Server Database as Data reference.

I'm either getting:

Couldn't connect to the database. Please check the connection details.

or

Timed out after 5000 milliseconds.

Anyone who has done Metabase - SQL Server connection successfully?

Nowise

Answer 1

Currently, as of Metabase version 0.30.x the answer depends on if you can use a user configured directly on SQL Server or not:

• The easier option to setup is with a user configured directly on SQL Server. Then config is just Name: (anything), Host: (full name), Database Name, Username, Password as you would expect. (I believe you should NOT fill in the Windows domain).

• On Windows only you can also use native integrated authentication with sqljdbc_auth.dll, just specify integratedSecurity=true connection property (and optionally authenticationScheme=NativeAuthentication). I found if I dropped the x64 sqljdbc_auth.dll in my <java path>\jre\bin folder it was picked up. Metabase config as in the screenshot below, apart from dropping authenticationScheme=JavaKerberos

• The cross platform authenticationScheme=JavaKerberos option to use Windows Authentication with a domain user is still doable, but harder. It's these steps:

  1. Create a krb.conf and verify login works with <Java path>\bin\kinit.exe [user]. Here's a template edited to match the Metabase database setup in the screenshot below.

  2. Start the Metabase.jar file with parameters to load the krb.conf. It works for me with java -Djava.security.krb5.conf=c:/<path>/kerberos/krb.conf -jar metabase.jar.

  3. Now configure the database settings in Metabase according to the screenshot below

  4. Moment of truth! ...

  5. Optional: Still not working? Maybe your SQL Server isn't configured for Kerberos? Maybe you need SPN setup? Maybe you need to specify a java.security.auth.login.config (I have not experienced any of that myself, only thing I can offer is you scroll to the bottom of this post and dive in then edit your question with where you got to and what config you have)

Metabase SQL Server setup using Kerberos:

For background, current (August 2018) very long :| sum up with lots of links of how to connect to SQL Server is here in a Metabase discussion topic Note: In v0.30.0 an issue with dynamic port handling (particularly for people using named instances) was fixed.

Related: A neat StackOverflow answer on Kerberos on a Mac

Uticas

Answer 2

If you are connecting to a "Named Instance" ({SERVER_NAME}/{INSTANCE_NAME}) instead of "Default Instance" ({SERVER_NAME}), your server may be configured to use Dynamic Port.

• A quick dirty fix is you can find the current port with below scripts and then use it on Metabase connection parameters. But this port most probably will change when service restart occurs.

  SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL

or

USE master
GO
Xp_readerrorlog

(look for "Server is listening on ..." line)

• A Cleaner way is setting port for your instance by "SQL Server Configuration Manager" - "SQL Server Network Configuration" - "TCP/IP" - Right Click properties - "TCP Dynamic Ports" (There should be 0 and it should be deleted) and "TCP Port" (New default port should be set here). And also "SQL Server Browser" service should run. A more detailed explanation is here

Adrienadriena

Latest version of Metabase seems to find the correct port. Just leave it blank the port field in the connection settings. – Adrienadriena