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\username. 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\username. 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\username 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
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.
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
• The following stanza should reside in
[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
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.
Follow-up question to this:
What's the domain_name setting for SQL 2008 R2?
no this applies to both linux/windows splunk running dbx2
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
It may be that the spaces are required - meaning that domain_name=SQLSERVER doesn't pass but domain_name = SQLSERVER will send domain=SQLSERVER.
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.
Having the same issue here as well. Can confirm the fix below by lagnone_splunk worked for me.
I have the same issue. Any advice would be greatly appreciated.