DB Connect 1.2.2 - Cannot connect to MSSQL - Connection Reset

[ejharts2015]

Upgraded from DB Connect 1.0 and started getting these error messages:

2016-03-08 22:41:35.033 monsch1:ERROR:Scheduler - Error while reading stanza=[dbmon-dump://database_name/database_table]: com.splunk.config.SplunkConfigurationException: Error validating dbmonDump for monitor=dbmon-dump://database_name/database_table: Error getting database connection: Network error IOException: Connection reset with query =

[ejharts2015 · Accepted solution]

Posting here in the hops of saving folks a lot of time and headache.

First off: 1.2 DB Connect requires JVM 8 not JVM 7 as in pervious versions of DB Connect v 1.x

Download the zip from Microsoft here: https://www.microsoft.com/en-us/download/details.aspx?id=11774

Un gzip and un tar it and located the sqljdbc42.jar file

Put ONLY the sqljdbc42.jar into dbx/bin/lib/ folder - Make sure it's the 42.jar not 4.jar or 41.jar
Also make sure splunk can access this. Typically I do a chown splunk:splunk to the file after I move it.

Copy the database_types.conf from default to local and add in the following stanza:
Also note that the testQuery is required, otherwise your connections will fail.

[generic_mssql]
displayName = MS-SQL Server Using MS Generic Driver
jdbcDriverClass = com.microsoft.sqlserver.jdbc.SQLServerDriver
connectionUrlFormat = jdbc:sqlserver://{0}:{1};databaseName={2};selectMethod=cursor
defaultPort = 1433
defaultCatalogName = master
testQuery = SELECT 1

Then update your database.conf in your local folder for each of your mssql databases:

[database_stanza]
database = database_name
host = database_host
isolation_level = DATABASE_SETTING
readonly = 1
#type = mssql
type = generic_mssql
username = Splunkservice
disabled = 0
password = [encrypted_password]

Reboot splunk