Splunk DB Connect 2: Why am I unable to connect to MSSQL with encryption?

[coltwanger]

I am using the DB Connect 2 app to connect to a SQL Server 2008R2 database from Splunk 6.3.2 and JRE8. I have one working connection to a SQL Server 2008R2 database with no encryption forced. Using the exact same configurations for a different database only WITH forced encryption, I am unable to connect with DB Connect 2.

From db_connections.conf:

### Working
[DB_Lookup]
connection_type = mssql_jtds_win_auth
database = master
host = hostname.abc.com
identity = splunkservice
jdbcUrlFormat = jdbc:jtds:sqlserver://<host>:<port>/<database>;useCursors=true;domain=<domain_name>;useNTLMv2=true
jdbcUrlSSLFormat = jdbc:sqlserver://<host>:<port>;databaseName=<database>;selectMethod=cursor;integratedSecurity=true;encrypt=true;trustServerCertificate=true
disabled = 0
jdbcUseSSL = 0
port = 1433

### Not working
[DB_Lookup_1]
connection_type = mssql_jtds_win_auth
database = databasename
host = securehost.abc.com
identity = splunkservice
jdbcUrlFormat = jdbc:jtds:sqlserver://<host>:<port>/<database>;useCursors=true;domain=<domain_name>;useNTLMv2=true
jdbcUrlSSLFormat = jdbc:sqlserver://<host>:<port>;databaseName=<database>;selectMethod=cursor;integratedSecurity=true;encrypt=true;trustServerCertificate=true
jdbcUseSSL = 0
port = 1433

I've tried the various other drivers -- this particular one "MS-SQL Server Using jTDS Driver With Windows Authentication" works well with the first database, but doesn't allow me to select "SSL" from the interface. None of the other drivers (even those that allow enabling SSL) work at all.

The message I get from Splunk is:

Validating connection with URL [jdbc:jtds:sqlserver://securehost.abc.com:1433/databasename;useCursors=true;domain=abc.com;useNTLMv2=true] failed: java.sql.SQLException:,I/O Error: DB server closed connection. 

And the message from the DB side:

DESCRIPTION:      Encryption is required to connect to this server but the client library does not support encryption; the connection has been closed. Please upgrade your client library. [CLIENT: 123.123.123.123] 

Turning off encryption on the DB is not an option and I'm at a loss as to what steps to take from here.

[coltwanger · Accepted solution]

Worked with Support to get a resolution to this. The JDTS driver does not support SSL, so we had to use the Generic MS SQL driver with Windows Authentication. In addition to this, the professional services consultant had mistakenly installed JRE 8 32-bit instead of JRE 8 64-bit. The 32-bit version apparently does not support the SSL connectivity.

For reference, here is my working stanza with encryption forced at the DB side:

[DB_Lookup_1]
connection_type = generic_mssql_with_windows_auth
database = databasename
host = securehost.abc.com
identity = splunkservice
jdbcUrlFormat = jdbc:sqlserver://<host>:<port>;databaseName=<database>;integratedSecurity=true;selectMethod=cursor
jdbcUrlSSLFormat = jdbc:sqlserver://<host>:<port>;databaseName=<database>;selectMethod=cursor;encrypt=true;trustServerCertificate=true
jdbcUseSSL = 0
port = 1433
disabled = 0

[jkat54]

You're not removing the old connection string that isn't ssl AND you haven't enabled jdbcssl. Here try this one instead:

[DB_Lookup_1]
connection_type = mssql_jtds_win_auth
database = databasename
host = securehost.abc.com
identity = splunkservice
jdbcUrlSSLFormat = jdbc:sqlserver://:;databaseName=;selectMethod=cursor;integratedSecurity=true;encrypt=true;trustServerCertificate=true
jdbcUseSSL = 1
port = 1433