How do I use DB Connect on Linux to a Win SQL server?

[Dworsnop]

Hi all,

I'm using Splunk Enterprise version 6.5.1 with Splunk DB Connect version 2.4.0 on a heavy forwarder running Linux.

I'm trying to connect to an MS SQL DB on a Microsoft 2014 server with a backslash in the name (name\name:non-default port).

I have tried numerous combinations of driver types and credentials (Win authenticated and not). I was using the generic MS one having followed previous answers/documentation regarding downloading the correct version (I've got 4.1 showing in my driver list - there wasn't an older version available on the Microsoft site) and I thought I was getting somewhere with an error message saying the "Login failed for user 'domain\username'" (despite knowing the creds were valid) until I read an answer here...

https://answers.splunk.com/answers/556315/splunk-db-connect-3-why-am-i-unable-to-login-using.html

... saying that I need to be using the 'MS-SQL Server Using jTDS Driver'. Only problem is when I do this it tells me that the server host name is "Unknown" so then if I change the backslash to a forward-slash (thinking Linux would prefer this) it now tells me...

com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Network error IOException: Connection refused (Connection refused)

I have also tried the accepted answer here...

https://answers.splunk.com/answers/228878/how-to-connect-splunk-db-connect-2x-to-ms-sql-usin.html?ut...

...but to no avail. I did change the "MSSQLSERVER12" to my organisations domain which might have been a mistake but I'm running a 2014 server so would that change things?

Really starting to pull my hair out with this one but I'm determined to get it working. Help me splunk>answers, you're my only hope.

[FrankVl · Accepted solution]

Well, I'm not 100% sure what that part behind the \ is supposed to be and why that does work in excel. But if Splunk specifically expects just a hostname in that setting, then there shouldn't be any slashes in that.

So I would try with just the hostname part and leave the \app02a out.

[Dworsnop]

This worked!! Could have sworn I tried this earlier but mustn't have had the correct combination of other parameters.

Thank you so much for your help.

[FrankVl]

Glad to hear that 🙂

I've converted my comment to an answer, so you can mark it as accepted.

[Rob2520]

Try IP address in place of hostname. If you are using windows authentication use JTDS driver. If not generic MS driver works pretty fine.

[richgalloway]

Have you tried escaping the backslash?

[Dworsnop]

Which character would I use to do this? Can you give me an example please.

[richgalloway]

I'd use a backslash. name\\name:non-default port

[FrankVl]

Can you show a screenshot of the DBConnect settings as you put them in (masking any sensitive info)? You say you get an error on the hostname and changed the \, but that shouldn't be in the hostname, right? That is part of the database name I guess?

[Dworsnop]

No screenshot as I'm out of the office now but the hostname that I connect to is something like...

fakedbnameapp02a\app02a

I don't know why, I didn't set it up and I'm no DB admin. ;oD

Databse name is 'sx'.

I can tell you though that I was able to connect to it via an Excel DB connection using the same server name and credentials that I'm trying to get working in splunk and I know the port number is correct because I took a pcap when I tested it in Excel where I could see all the tables etc.