All Apps and Add-ons

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

matthijsk
Explorer

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

1 Solution

lagnone_splunk
Splunk Employee
Splunk Employee

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.

View solution in original post

romantic_warrio
New Member

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
0 Karma

lagnone_splunk
Splunk Employee
Splunk Employee

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
Communicator

Follow-up question to this:

  • Does this require running DBConnect on Windows?
0 Karma

virabadrasana
Engager

What's the domain_name setting for SQL 2008 R2?

0 Karma

mcronkrite
Splunk Employee
Splunk Employee

no this applies to both linux/windows splunk running dbx2

0 Karma

splunk-support0
Explorer

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

0 Karma

bgstein
Path Finder

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

0 Karma

matthijsk
Explorer

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.

0 Karma

jhartmann9854
Engager

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

0 Karma

ibondarets
Explorer

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...