Issue with dbconnect version 2 with Windows authentication for SQL Server

[cdo_splunk]

Issue with dbconnect version 2 with Windows authentication for SQL Server using sql server jar file

[cdo_splunk]

To connect to Microsoft SQL Server with DB Connect using the Microsoft JDBC Driver for SQL Server and Windows authentication, follow these instructions. Be aware that these instructions assume that you're running Splunk Enterprise on Microsoft Windows.

Modify the jdbcUrlFormat setting in the db_connection_types.conf file to include integratedSecurity=true. For example:

jdbcUrlFormat = jdbc:sqlserver://<host>:<port>;databaseName=<database>;selectMethod=cursor;integratedSecurity=true

From the Microsoft JDBC Driver 4.0 for SQL Server download, locate the sqljdbc_auth.dll file. This file is at the following path, where <region_code> is the three-letter region code (for example, enu for U.S. English), and <architecture> is the processor type (x86 or x64):

    Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\<region_code>\auth\<architecture>\sqljdbc_auth.dll

Copy the sqljdbc_auth.dll file to C:\Windows\System32 on your Splunk Enterprise server.
From the Windows Control Panel, go to Services, and then get properties on "Splunkd Service."
Click the Log On tab, and then change the "Log on as" setting from the Local System account to that of the logged on domain user.

    Note: The domain user should have sufficient privileges to access the SQL Server instance. 

Save your changes, and then restart the Splunk Enterprise server for the changes to take effect.

[cdo_splunk · Accepted solution]

DBconnect version 2 with window authentication for sql server only worked with jTDS driver

Workaround
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
Change the Username in the identity to have only the username(ex.bamboo) instead of domain-name\username(MSSQLSERVER12\bamboo).
Once the above changes are made, validate all flows and make sure they work.
--Db connect version 1.x and db connect version 2 only support sql server window authentication using jTDS driver. The jTDS driver does not support connecting over Secure Sockets Layer (SSL), nor does it support database connection pooling. If you want to use sql server database driver , you need to use sql server authentication.