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

[matthijsk]

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

[romantic_warrio]

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

[lagnone_splunk · Accepted solution]

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.

[laserval]

Follow-up question to this:

• Does this require running DBConnect on Windows?

[virabadrasana]

What's the domain_name setting for SQL 2008 R2?

[mcronkrite]

no this applies to both linux/windows splunk running dbx2

[splunk-support0]

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

[bgstein]

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

[matthijsk]

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.

[jhartmann9854]

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_authen...

[ibondarets]

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