topic Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication? in All Apps and Add-ons

How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

matthijsk — Wed, 15 Apr 2015 07:52:06 GMT

Hi,

I am trying to set up DB Connect v2 for reading data from my MS SQL servers. I already have version 1 running and it works perfectly. With version 1 I could use one Active Directory account to connect to all my different SQL instances. This does not seem to work with version 2, it seems with version 2 I can no longer use Windows Authentication with NTLM to connect to a MS SQL server (this is how it worked for version 1 as far as I can tell).

I define a credential In Splunk DB Connect with username = domain&#92username. I then try to set up a connection using that credential. This does not work, neither with the "Microsoft JDBC Driver for SQL Server" of with the "jTDS driver". With the Microsoft driver I get an error "Login failed for user domain&#92username. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: x.x.x.x]". So the JDBC driver does not see the domain&#92username as a Windows account but as a SQL account.

It does work when i use a SQL account, but this is not a preferred solution as it would require me to create SQL accounts for all SQL instances.

I have looked through any related answer that I could find, but no luck.

Some help and insight would be appreciated.

Best regards

Matthijs

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

ibondarets — Wed, 22 Apr 2015 12:18:13 GMT

I have the same issue. Any advice would be greatly appreciated.

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

jhartmann9854 — Wed, 22 Apr 2015 17:51:09 GMT

Having the same issue here as well. Can confirm the fix below by lagnone_splunk worked for me.

update. As of DBConnect 2.0.4 MSSQL is supported with the MSSQL drivers http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Troubleshooting#Can.27t_use_Windows_authentication_for_Microsoft_SQL_Server_with_Microsoft_JDBC_Driver_for_SQL_Server

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

lagnone_splunk — Thu, 23 Apr 2015 14:08:14 GMT

It's a known issue for DBX 2.0

DBX - 1694: SQL server connection does not work in windows authentication mode

Try this as a workaround
Note - This only works with the JTDS driver, not the generic MSSQL driver

1. Add the below entries to db_connections.conf(for the particular connection) OR db_connection_types.conf(for the connection type)
jdbcUrlFormat = jdbc:jtds:sqlserver://<host>:<port>/<database>;useCursors=true;domain=<domain_name>;useNTLMv2=true
domain_name = MSSQLSERVER12
2. Change the Username in the identity to have only the username(bamboo) instead of domain-name\username(MSSQLSERVER12\bamboo)
3. Once the above changes are made, validate all flows and make sure they work.

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

matthijsk — Tue, 28 Apr 2015 12:56:16 GMT

Thank you, this works. I hope we can get a solution for the generic MSSQL driver also as according to the documentation this is now the preferred driver.

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

splunk-support0 — Mon, 28 Sep 2020 19:42:31 GMT

The db_connection_types.conf didn't work for us. The db_connection_types.conf did, however the value wasn't being successfully substituted so we had to hardcode the domain value. ie.

jdbcUrlFormat = jdbc:jtds:sqlserver://:/;useCursors=true;domain=MYORG;useNTLMv2=true

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

laserval — Wed, 27 May 2015 08:00:20 GMT

Follow-up question to this:

Does this require running DBConnect on Windows?

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

bgstein — Tue, 29 Sep 2020 06:42:11 GMT

It may be that the spaces are required - meaning that domain_name=SQLSERVER doesn't pass but domain_name = SQLSERVER will send domain=SQLSERVER.

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

mcronkrite — Fri, 17 Jul 2015 15:33:57 GMT

no this applies to both linux/windows splunk running dbx2

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

virabadrasana — Mon, 24 Aug 2015 11:27:11 GMT

What's the domain_name setting for SQL 2008 R2?

Re: How to connect Splunk DB Connect 2.x to MS SQL using Windows Authentication?

romantic_warrio — Tue, 29 Sep 2020 10:51:35 GMT

One of my colleagues worked it out. Here are my notes:

• In setting for the app (DB Connect v2), use the Java8 JDK not JRE.
• Make sure ‘jtds-1.3.1.jar’ is in \bin\lib directory.
• The following stanza should reside in \local\db_connection_types only once:

[mssql]
displayName = MS-SQL Server Using jTDS Driver
serviceClass = com.splunk.dbx2.MSSQLJtdsJDBC
jdbcDriverClass = net.sourceforge.jtds.jdbc.Driver
jdbcUrlFormat = jdbc:jtds:sqlserver://:/;useCursors=true
#jdbcUrlFormat = jdbc:sqlserver://:;databaseName=;selectMethod=cursor;integratedSecurity=true
#jdbcUrlFormat = jdbc:jtds:sqlserver://:/;useCursors=true;domain=;useNTLMv2=true
#jdbcUrlFormat = jdbc:jtds:sqlserver://:/;useCursors=true;useNTLMv2=true
# seems this driver cannot use pooled datasource.
useConnectionPool = false
testQuery = SELECT 1

[generic_mssql]
useConnectionPool = false
displayName = MS-SQL Server Using MS Generic Driver
serviceClass = com.splunk.dbx2.MSSQLJDBC
jdbcDriverClass = com.microsoft.sqlserver.jdbc.SQLServerDriver
#jdbcUrlSSLFormat = jdbc:sqlserver://:;databaseName=;selectMethod=cursor;encrypt=true;trustServerCertificate=true
#jdbcUrlFormat = jdbc:sqlserver://:;databaseName=;selectMethod=cursor
#jdbcUrlFormat = jdbc:sqlserver://:;databaseName=;selectMethod=cursor;integratedSecurity=true
jdbcUrlFormat = jdbc:jtds:sqlserver://:/;useCursors=true;domain=;useNTLMv2=true
port = 1433
testQuery = SELECT 1