Loading SQL Server data using Logstash

Elastic Stack Logstash
1

Hi All,
I am planning to load data from SQL Server database to Elastic. While trying to load this data using Logstash JDBC plugin, I am facing issue. I am pasting details as below, please suggest what is missing here.

(Please note that driver files have 777 privilege and Elastic has access to the same. We are able to load data from Oracle JDBC, but facing issue while loading from SQLServer)

Try1: When I give absolute path of JDBC driver (sqljdbc4-2.0.jar), I am getting:
Error: com.microsoft.sqlserver.jdbc.SQLServerDriver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?

Try2: Copy the driver to path /logstash/product/logstash-7.10.2/logstash-core/lib/jars and assign jdbc_driver_library => "sqljdbc4-2.0.jar":
Error: unable to load sqljdbc4-2.0.jar from :jdbc_driver_library, file not readable (please check user and group permissions for the path) <

Try3: Give absolute path of installation jar folder to jdbc_driver_library (/logstash/product/logstash-7.10.2/logstash-core/lib/jars/sqljdbc4-2.0.jar):
Error: com.microsoft.sqlserver.jdbc.SQLServerDriver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library? />

Try4: Remove the line jdbc_driver_library => "sqljdbc4-2.0.jar" from config file:
Error: com.microsoft.sqlserver.jdbc.SQLServerDriver not loaded. :jdbc_driver_library is not set, are you sure you included the proper driver client libraries in your classpath? />

Try5: Change the JDBC driver from sqljdbc4-2.0.jar to mssql-jdbc-9.4.0.jre16.jar
Error: unable to load mssql-jdbc-9.4.0.jre16.jar from :jdbc_driver_library, file not readable (please check user and group permissions for the path) />

Thanks in advance!

2

Hello Sunil,

I am using Oracle instead of SQL Server but the config should be similar, so here is my working configuration:

input {
	jdbc {
		jdbc_driver_library => "/home/tomcat/logstash/ojdbc6-12.1.0.2.jar"
		jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
		jdbc_connection_string => "..."
		jdbc_user => "..."
		jdbc_password => "..."
		jdbc_validate_connection => true
		schedule => "* * * * *"
		statement => "..."
		tracking_column => "id"
		use_column_value => true
		last_run_metadata_path => "..."
	}
}

As you see, I have used the full path in jdbc_driver_library and the jdbc_driver_class is prefixed with Java:: as described here:

Per Error: oracle.jdbc.driver.OracleDriver not loaded. · Issue #43 · logstash-plugins/logstash-input-jdbc · GitHub, prepending Java:: to the driver class may be required if it appears that the driver is not being loaded correctly despite relevant jar(s) being provided by either via the jdbc_driver_library setting or being placed in the Logstash Java classpath. This is known to be the case for the Oracle JDBC driver (ojdbc6.jar), where the correct jdbc_driver_class is "Java::oracle.jdbc.driver.OracleDriver" , and may also be the case for other JDBC drivers.

Can you try that?

Best regards
Wolfram

1 Like
3

Thanks Wolfram for the response!
I myself an oracle data engineer and have tried loading from ojdbc successfully. Issue is with SQL Server driver, as similar setup is not working.

Thanks for reaching out and providing suggestions! :slight_smile:

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.