All Apps and Add-ons

Splunk DB Connect 2: Fails to connect to MS SQL using configured identity

Path Finder

I have recently installed DB Connect v2.3.0 on Windows Server 2012 R2, following instructions found at this URL:

docs.splunk.com/Documentation/DBX/2.3.0/DeployDBX/AboutSplunkDBConnect

I do have an older version of DB Connect (v.1.1.6) on this server which appeared to use JDK 1.7.0_17 and the jTDS-1.2.6 driver. I left that completely alone, i.e. I made no attempt to migrate configuration.

With the new install, I would like to use SSL-encrypted connections and connection pooling, which I believe are not supported by the jTDS driver.

I have installed JRE 1.8.0_92 on the server, along with Microsoft JDBC Driver 4.0 for SQL Server, copying files to the following locations:

  • sqljdbc4.jar in C:\Program Files\Splunk\etc\apps\splunkappdb_connect\bin\lib\
  • sqljdbc_auth.dll in C:\Windows\System32 (i.e. a folder in the default path)

I want to connect to my SQL server using a specific Active Directory account, i.e. NOT SQL authentication and NOT "integrated" authentication with the account the service is running under. I set up the account I wanted to use as an Identity with Use Windows Authentication Domain set.

I did have some confusion over how to configure the connection, despite the documentation. At first I tried to follow the section named Install the SQL Server database driver when using Windows Authentication, but this setting seemed to cause the Identity drop-down to be greyed-out as if this were the option that were to use the Splunk service account. Note that this setting does work for a database where the Splunk service account has read permission on the database, but does not work for a different database to which it does not have permission (as expected).

Following advice found here in Answers and also in the troubleshooting areas of the documentation I tried to connect using the ...with Kerberos Authentication option. This gives an Internal Server Error message when validating the connection.

Connection URL:
jdbc:sqlserver://MYSERVER:MYPORT;databaseName=MYDATABASE;selectMethod=cursor;integratedSecurity=true;authenticationScheme=javaKerberos;

Error seen in log file:
Exception: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Integrated authentication failed. ClientConnectionId:268684b4-b611-4167-bdb7-e9d73a9fe523

Apparently the connection attempt is not even visible from the SQL server.

Path Finder

Anybody have had any success on it? Running through the same issue.

0 Karma

Path Finder

No, but as a work around i used the other identity option (can't recall the name (not kerberos)) and the DBAs had to create SQL users within the MSSQL DB. Then it worked as expected. Funnily enough you still needed to enter the domain etc for it work.

0 Karma

Engager

I'm still interested in the 'proper' answer as well. I've worked around the issue for now by using an Active Directory service account to run the Splunkd Service as a Windows service. That same Active Directory account has access to the MS SQL DBs I need to query so the workaround works but it isn't ideal because I was hoping to use a different account for running the Spunkd Service compared to querying the MS SQL DBs...

0 Karma

Path Finder

Old post, I realise. But defintely an issue with the documentation regarding AD Users with MSSQL access.
As mentioned selecting: "MS-SQL Server Using MS Generic Driver With Windows Authentication" causes the Identity option to be greyed out.
Which is not the expected behaviour....

0 Karma