Connect to SQL Server via Windows Authentication

Hi, just discovered and downloaded Metabase. Looks really cool.

I’m trying to set it up for the first time. I’ve got it installed, but when I try to set up the connection to my data, I can’t seem to get it to connect using my Windows domain credentials. Is this not possible with Metabase?

I put my domain name under “Windows domain”, my Windows username under “Database username” and my Windows password under “Database password”. That returns this error (domain and username are stand-ins):

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ‘bungledomain\bungle’.

EDIT:
Metabase Version: 0.27.2
Installed locally on a Windows 10 machine. Using Firefox v57 to access GUI.
Trying to connect to SQL Server 2014, installed on Windows Server 2012 R2

I believe it’s possible in your environment. Best description I know of is this specific comment: https://github.com/metabase/metabase/issues/6056#issuecomment-342901500 by maltobelli.

I added my personal experiences in this comment on the same issue.

1 Like

Thank you. This is a bit above my head…so I need to follow the instructions there to activate Kerberos, whatever that is?

If I understand correctly, this is essentially a workaround to tell SQL Server to allow Keberos authentication instead of using Windows NT/Active Directory Authentication. Is that correct?

Yes, or maybe more correctly (if I understood all the terms right) that Windows Domain Authentication used to run on the old NTLM protocol and some JDBC drivers supported that. The current protocol and configuration recommended by Microsoft with their own JDBC driver is Kerberos.

You are right that at the moment it’s not as straightforward as it used to be.

Below is a copy of the krb.conf I succesfully used back in November - but changed to exactly match full Windows domain LOCAL.NETWORK.ORG as given in maltobelli’s screenshot. It’s hopefully a little less confusing than the example in Microsofts own JDBC driver documentation which frankly also gets me slightly seasick :worried: .

I believe to have read domain name case is important, so: Replace 2 x UPPERCASE and 2 x lowercase versions of domain to match your environment.

After that your next step could be to check your krb.conf works with <Java path>\bin\kinit.exe [user] … as I just did below.

Template for krb.conf:

[logging]
# default = FILE:/var/log/krb5libs.log
# kdc = FILE:/var/log/krb5kdc.log
# admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 dns_lookup_realm = false
 ticket_lifetime = 24h
 renew_lifetime = 7d
 forwardable = true
 rdns = false
# default_realm = EXAMPLE.COM
 default_realm = LOCAL.NETWORK.ORG

[realms]
# EXAMPLE.COM = {
#  kdc = kerberos.example.com
#  admin_server = kerberos.example.com
# }
 LOCAL.NETWORK.ORG = {
  kdc = local.network.org
  admin_server = local.network.org
 }

I just re-checked the kinit step. Here’s my output when running in the directory with my krb.conf file:

C:\Hub\kerberos>"\Program Files\Java\jdk1.8.0_152\bin\kinit.exe"
Password for MYUSER@LOCAL.NETWORK.ORG:
New ticket is stored in cache file C:\Users\myuser\krb5cc_myuser
1 Like

Thank you for the detailed response.
Do I need to enable anything server-side for this to work? I’m not quite sure what exactly the steps are to get this working…Where does krb.conf go?

I’m unsure if you have to make anything server-side (I didn’t have to with our SQL Server, if that was due to that someone already set it up or they come as such nowadays, well hard for me to verify). I guess maltobelli was connecting from Linux, and it’s not clear to me if his SQL Server is also running there.

You can put krb.conf anywhere you like, as long as you do the kinit.exe testing in the same directory. When you have done that verification you give the path to it as a parameter when you start metabase.jar as I detailed in step 3. in my issue #6056 comment.

Can’t remember if Java on Windows had a default location it would search. Update: googling reveals a mighty detailed sequence

Thanks. I can’t seem to get this to work.
Any idea if there are plans for Metabase to handle this scenario better in the future?

What error message do you get when you try to run kinit with your krb.conf? Or did you get succesfully past that point? Would be helpful to narrow down to a working config…

The Metabase core developers have marked it as a priority 1 bug. But as I understand it they don’t really have access to a SQL Server on a Windows domain, let alone all the possible variations of environments. That makes it pretty futile for them to solve.

I edited the krb.conf template you posted above, replacing the 4 domains with mine. Put that in C:\Program Files (x86)\Java\jre1.8.0_151\bin, the same directory as kinit.
When I run kinit, it asks for my password, then stores ticket in cache file krb5cc_<username>, just like your output above. I tried intentionally putting in the wrong password and it did not work…I don’t know what that signifies other than that it is correctly validating my password.

Now I open cmd to the path of metabase.jar and run this:
java -Djava.security.krb5.conf="C:\Program Files (x86)\Java\jre1.8.0_151\bin\krb.conf" -jar metabase.jar

Metabase starts, but when I try connecting to SQL Server I get this in the console:

01-25 16:15:10 e[1mERROR metabase.drivere[0m :: Failed to connect to database: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:a0ff5754-d87f-4006-a526-443a0cf2957b

01-25 16:15:10 e[1mERROR metabase.drivere[0m :: Failed to connect to database: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '<user>@<DOMAIN>.LOCAL'. ClientConnectionId:8be91ad4-bc7a-438b-91a3-0d7300c1d9d4

Wow! One step further … seems your krb.conf is OK and …
"The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target" gives a pretty good clue!

First few things to check (/me: points to maltobellis SQL Server screenshot):

  • make sure you have trustServerCertificate=true; as part of Additional JDBC settings (it means ignore validation errors)

  • make sure you have the full SQL server hostname (maybe check with C:\> ping <sqlservername> it may answer with another alias name)

Then … any change in error message?

Ok, added trustServerCertificate=true; and that got rid of the SSL error. However it still shows the second error, Login Failed.

I verified the hostname is correct by logging into the SQL Server machine and running hostname in cmd. Furthermore I tried changing the Host field in Metabase to something intentionally incorrect, and it just returned a timeout error. So it seems to be finding and connecting to the server correctly, but having trouble logging in for some reason…

Thanks again for your help with this I really appreciate it.

EDIT: I also tried adding the entire string (not just trustServerCertificate) from maltobellis’ screenshot: trustServerCertificate=true;integratedSecurity=true;authenticationScheme=JavaKerberos and oddly that returned a timeout error, not a “login failed”.

Hmm - yes, your EDIT sounds odd… :worried:

I’m currently suspecting of your SQL Server is missing SPN etc. for Kerberos i.e. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication Would be good if there was an easy (non-Metabase) validator for that part …

Unfortunately it’s now past 2am here so long past bedtime. Will check back when I can …

Grrr … couldn’t help it: https://technet.microsoft.com/en-us/library/cc280745(v=sql.105).aspx

Muzzzt stop googleling :zzz: :dizzy_face:

IntegratedSecurity=true just means that it will try to use the current user’s token to authenticate.
I’ve been using Kerberos security with Tomcat for a few years. It can be an absolute pain to setup. I’ve not tried with Metabase yet.

jornh: I hope you managed to get some sleep :slight_smile:
I saw those links but still have to read through them. At first glance this seems like something I should not or may not even have permission to do…

AndrewMBaines: Thanks, so Kerberos security has to be “activated” from Tomcat? Have you ever attempted doing that with SQL Server?

I’ve only used Kerberos for authenticating a Tomcat session agains AD. The software I normally use does the database connection via a Windows service, so just uses the account that the service is using. Much easier. I don’t think that running Metabase with a windows account would achieve this.
I’ll try and take a look next week some time to see how it all fits together.

@Bungle yes, even managed to sleep twice now :slight_smile: (now in car on my way to Austria for a few days on ski)

Realize my last post wasn’t that clear. My point was it has a hint on how you can check if sql server can serve via Kerberos with

SELECT auth_scheme FROM sys.dm_exec_

I’m thinking that might be useful as a next step. It might depend on permissions as you indicate though.

I was not able to check that, my permissions did not allow it.
In the end I just created a SQL Server user, and used that instead of Windows Authentication.
And I’m in! It’s working great and I’m quite impressed with this program, I can see it being very useful.
It would be nice to get Windows Authentication working normally again but I’m sure there are better things for the developers to be working on…

Thank you both for your help, enjoy your ski trip @jornh

1 Like

Apparently I didn’t have anything better to do than trying to do yet another write up on this topic as a StackOverflow answer.

Turned out on Windows there’s a third option (2nd bullet in my SO answer):

  • On Windows only you can also use native integrated authentication with sqljdbc_auth.dll

Would be fun to hear if it works for others :smiley_cat: