Deployment Architecture

Splunk DB Connect: Unable to connect to MSSQL. Why is Jbridge not executing DB commands?

chrisboy68
Contributor

Folks,

Been banging my head for hours. Cannot get DB to Connect to a MSSQL instance. To continue my trouble shooting, I installed a fresh instance of the Splunk Heavy Forwarder with DB Connect and a local instance of MSSQL Express. I have verified in MSSQL that no log in attempts have occurred from splunk query execution. It seems they just die at the jbridge server.

Here are DBX Debug logs:

2015-03-11 13:54:26.844 dbx9731:INFO:ExecutionContext - Execution finished in duration=218038 ms
2015-03-11 13:54:26.843 dbx9731:DEBUG:ExecutionContext - Destroying execution context...
2015-03-11 13:54:26.843 dbx9731:DEBUG:BridgeSession - Shutting down socket IO...
2015-03-11 13:54:26.843 dbx9731:INFO:BridgeSession - Execution finished in duration=218036 ms with status=0
2015-03-11 13:54:26.843 dbx9731:ERROR:DatabaseQueryCommand - Error while executing command: Error getting database connection: Network error IOException: Connection reset
2015-03-11 13:54:04.563 dbx8553:INFO:ExecutionContext - Execution finished in duration=10 ms
2015-03-11 13:54:04.563 dbx8553:DEBUG:ExecutionContext - Destroying execution context...
2015-03-11 13:54:04.563 dbx8553:INFO:Splunkd - Splunkd REST Keep-alive successful for user splunk-system-user
2015-03-11 13:54:04.553 dbx8553:DEBUG:Splunkd - Performing Splunkd Request: RESTRequest{GET authentication/current-context/context}
2015-03-11 13:54:04.553 dbx8553:DEBUG:Splunkd - Requesting Splunkd REST service keep-alive...
2015-03-11 13:54:04.553 dbx8553:DEBUG:ExecutionContext - Initializing execution context with app.ctx=dbx and execId=dbx8553
2015-03-11 13:53:56.850 dbx6918:INFO:ExecutionContext - Execution finished in duration=230887 ms
2015-03-11 13:53:56.850 dbx6918:DEBUG:ExecutionContext - Destroying execution context...
2015-03-11 13:53:56.849 dbx6918:DEBUG:BridgeSession - Shutting down socket IO...
2015-03-11 13:53:56.849 dbx6918:INFO:BridgeSession - Execution finished in duration=230876 ms with status=0
2015-03-11 13:53:56.840 dbx6918:ERROR:DatabaseInfoCommand - Error while executing command: Error getting database connection: Network error IOException: Connection reset
2015-03-11 13:50:48.812 dbx9731:DEBUG:MicrosoftSqlServer - Connecting to database=MicrosoftSqlServer using JDBC url=jdbc:jtds:sqlserver://10.160.82.29:1443/cjctest;instance=SQLEXPRESSCJC;ssl=request;appName=Splunk;progName=Splunk
2015-03-11 13:50:48.812 dbx9731:DEBUG:MicrosoftSqlServer - Connecting the MSSQL named-instance=SQLEXPRESSCJC
2015-03-11 13:50:48.811 dbx9731:DEBUG:MicrosoftSqlServer - Found slash in database name. Will split into instance and database name.
2015-03-11 13:50:48.811 dbx9731:DEBUG:Database - Connecting to database local
2015-03-11 13:50:48.811 dbx9731:DEBUG:PooledConnectionFactory - Creating database connection for database=local
2015-03-11 13:50:48.810 dbx9731:DEBUG:PooledConnectionFactory - Fetching connection from pool...
2015-03-11 13:50:48.810 dbx9731:DEBUG:Database - Fetching connection from factory...
2015-03-11 13:50:48.810 dbx9731:DEBUG:DatabaseQueryCommand - srInfo: null
2015-03-11 13:50:48.810 dbx9731:WARN:DatabaseQueryCommand - SearchResultsInfo file null doesn't exist
2015-03-11 13:50:48.810 dbx9731:DEBUG:DatabaseQueryCommand - infoPath not in command headers... reading it from job sid=null directly...
2015-03-11 13:50:48.808 dbx9731:INFO:DatabaseQueryCommand - action=executing SQL query="select 1 as test" on database=local
2015-03-11 13:50:48.807 dbx9731:DEBUG:BridgeSession - Bridge commands arguments: [__EXECUTE__, local, limit=1000, "select 1 as test"]
2015-03-11 13:50:48.807 dbx9731:INFO:BridgeSession - Executing Bridge command: BridgeExecutionInfo{className=com.splunk.dbx.command.DatabaseQueryCommand}
2015-03-11 13:50:48.806 dbx9731:DEBUG:ExecutionContext - Initializing execution context with app.ctx=dbx and execId=dbx9731
2015-03-11 13:50:48.806 main:DEBUG:LRUCache - Cache hit for key com.splunk.dbx.command.DatabaseQueryCommand (no need to handleResult producer)
2015-03-11 13:50:48.806 main:DEBUG:BridgeSession - Starting BridgeSession...

Here is the only thing Jbridge Client logs:

2015-03-11 13:46:02,599 DEBUG spp.dbx.javabridge Executing java bridge command com.splunk.bridge.stats.SystemStatus [()]
2015-03-11 13:46:21,012 DEBUG spp.dbx.javabridge Executing java bridge command com.splunk.dbx.command.DatabaseInfoCommand [('database=local', 'type=schemas')]
2015-03-11 13:50:05,956 DEBUG spp.dbx.javabridge Executing java bridge command com.splunk.dbx.command.DatabaseInfoCommand [('tables', 'database=local', 'schema=*')]
2015-03-11 13:50:43,928 DEBUG spp.dbx.javabridge Executing java bridge command com.splunk.dbx.command.DatabaseQueryCommand [('__GETINFO__', 'local', 'limit=1000', '"select 1 as test"')]
2015-03-11 13:50:48,795 DEBUG spp.dbx.javabridge Executing java bridge command com.splunk.dbx.command.DatabaseQueryCommand [('__EXECUTE__', 'local', 'limit=1000', '"select 1 as test"')]

I have an OSX instance of Splunk DB Connect and that system has the same issues. Is there something I'm missing with the Jbridge server configuration? I'm out of ideas. I even opened a support ticket with Splunk....

Thank you in advance.

Chris

0 Karma

rmanevskicba
Engager

I had the same issue with MSSQL 2012 where one database configuration was working while the other one refused and seemed to hang indefinitely. The difference between the two turned out to be forced encryption on the MSSQL database that wasn't working.

After spending a bit of time on Google and trying a few things I finally got this working... Hope this helps.

$ cat database.conf
[instance1-database2]
database = instance1/database2
host = xxx.xxx.xxx.xxx
isolation_level = DATABASE_SETTING
password =
port = 1433
readonly = 1
type = mssql
username = aDBUserWithDB_READERaccess
arg.ssl = require

Unfortunately, we have to use Windows here but the same should apply to Linux environments. The key thing that got this working for me was the databases.conf option "arg.ssl = require" which enables "ssl=require" as a JDBC argument. As a result, the command line argument becomes:

url=jdbc:jtds:sqlserver://xxx.xxx.xxx.xxx:1433/database1;ssl=require;instance=instance1;appName=Splunk;progName=Splunk

You can also simply enter "ssl=require" as an additional JDBC parameter for the same result.

The "Splunk DB Connect" app doesn't come with a databases.conf.spec file so referencing this might also prove useful: http://docs.splunk.com/Documentation/DBX/1.1.7/DeployDBX/databasespec

The next change you might need to make involves the java.conf file.

$ cat java.conf
[java]
home = C:\Program Files\Java\jre7
options = -Xmx2G -Dfile.encoding=UTF-8 -server -Djsse.enableCBCProtection=false -Duser.language=en -Duser.region=

[logging]
level = DEBUG
console = false
logger.com.splunk.dbx = DEBUG

The option "-Djsse.enableCBCProtection=false" finally did the trick. Database connectivity was subsequently tested to both environments, SSL connections and NON SSL and both work perfectly.

jcoates_splunk
Splunk Employee
Splunk Employee

Thanks rmanevskicba!

0 Karma

chrisboy68
Contributor

Thanks. Tried your suggestions and I still get the same failed results. Ugh! Got a powershell script with embeded SQL working instead.

Tx

Chris

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

It's not able to connect to your database. Recent versions of MS SQL don't listen on predictable ports by default -- can you double check its configuration? http://support.microsoft.com/kb/823938

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

SSL and TLS are enabled in JavaBridge by default: http://docs.splunk.com/Documentation/DBX/1.1.7/ReleaseNotes/Releasenotes#Fixed_issues

There's also a number of MS specific tweaks that might be needed: http://docs.splunk.com/Documentation/DBX/1.1.7/DeployDBX/Addadatabaseconnection

0 Karma

chrisboy68
Contributor

Thanks but this is still not working. I'm giving up and trying a script. Just hangs no errors in the logs

Chris

0 Karma

chrisboy68
Contributor

Doesnt look like it. I couldn't tell so I turned on Forced Encryption on the DB Instance and now the other tool cannot connect. Does DB Connect require SSL?

Thank you,

Chris

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

Error while executing command: Error getting database connection: Network error IOException: Connection reset

when we try to talk to your database it says no. I see you're using IP address and network port directly, and you're validating the user name and password... are you using SSL with the other JDBC tool?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...