DB Connect 1.2 / Java 8 / MSSQL Integrated Authentication

[xdp4]

Made the jump on Friday to DBConnect 1.2. Started troubleshooting why our two MSSQL connections aren't working, after reading that Java 8 was required and getting that setup. I'm pretty sure that our config/install is working as our zOS/DB2 DB connection returns data like it did before the upgrade.

Reading/Searching around, it appears that integrated authentication with DBX 1.2 is problematic, but I can't seem to get a clear understanding of what needs to transpire for this to work. I created a custom db type in database_types.conf with the following stanza:
[generic_mssql]
displayName = MS-SQL Server Using MS Generic Driver
jdbcDriverClass = com.microsoft.sqlserver.jdbc.SQLServerDriver
testQuery = SELECT 1
connectionUrlFormat = jdbc:sqlserver://{0}:{1};databaseName={2};selectMethod=cursor

Trying to update my configured database connections returns an error: Encountered the following error while trying to update: In handler 'databases': Error connecting to database: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'domain/\user'. ClientConnectionId:cde99ae2-ee12-4375-b624-cafc0e5e5d9a hint: It's not a bad username | password

I've read the documentation, and posts from other frustrated users, but is there a bonafide step by step specific to DBX 1.2 setup guide for MS-SQL? I copied the sqljdbc4.jar from the JDBC driver 4.0 from https://www.microsoft.com/en-us/download/confirmation.aspx?id=11774 to /opt/splunk/etc/apps/dbx/bin/lib, but I haven't found the winning combination yet.

[BP9906]

Support resolved this issue for me and thus I'm passing along to resolve everyone else. 🙂

If you're using DBX 1.2.2 and have MS SQL Server with Windows Auth, you need to do this:

cd $SPLUNK_HOME/etc/apps/dbx/bin/lib/
mv jtds-1.2.6.jar jtds-1.2.6.jar.org
wget http://sourceforge.net/p/jtds/bugs/_discuss/thread/16113049/f77e/4151/f53d/attachment/jtds-1.3.1-v20...
--ensure you have java 8 installed (i.e. java -version)
restart Splunk


If you're using DBX 1.2.2 and have MS SQL Server with sa authentication, you need to do this:

1. Download the zip from Microsoft here: https://www.microsoft.com/en-us/download/details.aspx?id=11774

1. Get the sqljdbc42.jar and put it in dbx/bin/lib/ folder (dont put the sqljdbc4 or 4.1 there)

2. Per http://docs.splunk.com/Documentation/DBX/1.2.2/DeployDBX/Installdrivers do the following:

3a. edit $SPLUNK_HOME/etc/apps/dbx/local/database_types.conf

3b. add this

[generic_mssql]

displayName = MS-SQL Server Using MS Generic Driver

jdbcDriverClass = com.microsoft.sqlserver.jdbc.SQLServerDriver

connectionUrlFormat = jdbc:sqlserver://{0}:{1};databaseName={2};selectMethod=cursor

defaultPort = 1433

defaultCatalogName = master

testQuery = SELECT 1

1. now change your MS SQL configuration (databases.conf) to match. (ie change type=mssql to type=generic_msssql)

2. recycle splunk

[xdp4]

We ended up going to v2 of DBConnect and Oracle Java 8. I wouldn't recommend upgrading, even though the interface and features are nicer in DBConnect 2, it runs much slower in our environment and parses out results differently causing a lot of headache in our dashboards.

[simony]

Take the DB Connect 1.1.7 app and java7 and you'll see it works.
That was my step because i had the same problem with the MSSQL databases if i upgrade to 1.2 and java8.
Another problem is the encryption on the MSSQL database.
Disable that on the database and the connection works.
I hope this helps.

[jcoates_splunk]

Hi, I updated the documentation here http://docs.splunk.com/Documentation/DBX/1.2.0/DeployDBX/Installdrivers and here http://docs.splunk.com/Documentation/DBX/1.2.0/DeployDBX/DefineanewDB this weekend with a set of steps that work. Let me know where you are stuck.

[xdp4]

Thanks jcoates. I'm still having authentication errors on all our SQL server connections even using different accounts, so I believe it's connection based. Looking at the docs, it appears that it implies arg.useNTLMv2=true if username\domain is specified. I can't verify with the DB owners at this time, but is it possible that it's forcing NTLMv2 and the server is choking on it? In our environment an account should lock with 3 failed login attempts, but it's not locking the account which makes me think the authentication attempt is never making it to the AD servers.

[jcoates_splunk]

I see, that makes sense -- would it be possible to use a SQL account, or does it have to be a domain account?

[xdp4]

Our environment requires the use of domain accounts to comply with our security policies, so using local SQL authentication isn't an option unfortunately.