Jdbc for sqlserver

Elastic Stack Logstash
1

I am trying to connect to microsoft sqlserver but no luck
I can connect to this db using microsoft sqlserver management studio.
What am I missing here?

   input {
        jdbc {
            jdbc_validate_connection => true
            jdbc_driver_library => "/root/sqljdbc_7.4/enu/mssql-jdbc-7.4.1.jre12.jar"
            jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
            jdbc_connection_string => "jdbc:sqlserver://server1:1433;databaseName=h_db"
            jdbc_user => "xyz"
            jdbc_password => "xxx"
            statement => "select * from v_H_view"
            clean_run=>true
           }
    }

Getting following error.
Error: com.microsoft.sqlserver.jdbc.SQLServerDriver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?
Exception: LogStash::ConfigurationError
Stack: /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/jdbc.rb:163:in open_jdbc_connection' /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/jdbc.rb:221:inexecute_statement'
/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/inputs/jdbc.rb:277:in execute_query' /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/inputs/jdbc.rb:263:inrun'
/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:309:in inputworker' /usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:302:inblock in start_input'
[ERROR] 2019-09-04 07:58:01.022 [[main]<jdbc] javapipeline - A plugin had an unrecoverable error. Will restart this plugin.

Pipeline_id:main
Plugin: <LogStash::Inputs::Jdbc jdbc_user=>"xyz", jdbc_validate_connection=>true, jdbc_password=>, statement=>"select * from v_H_view", clean_run=>true, jdbc_driver_library=>"/root/sqljdbc_7.4/enu/mssql-jdbc-7.4.1.jre12.jar", jdbc_connection_string=>"jdbc:sqlserver://server1:1433;databaseName=h_db", id=>"2dd70d10625903fc97b655f7bde6cca1187a89e8dac5d97a4ca0d8876020fa36", jdbc_driver_class=>"com.microsoft.sqlserver.jdbc.SQLServerDriver", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_f7d9a927-b440-453b-b31d-edfc25071e90", enable_metric=>true, charset=>"UTF-8">, jdbc_paging_enabled=>false, jdbc_page_size=>100000, jdbc_validation_timeout=>3600, jdbc_pool_timeout=>5, sql_log_level=>"info", connection_retry_attempts=>1, connection_retry_attempts_wait_time=>0.5, parameters=>{"sql_last_value"=>1970-01-01 00:00:00 UTC}, last_run_metadata_path=>"/root/.logstash_jdbc_last_run", use_column_value=>false, tracking_column_type=>"numeric", record_last_run=>true, lowercase_column_names=>true>

2

Anyone out there who has done this successfully?

I came across exactly one thread who has done it but didn't work for me
I am running ELK 7.3.1

[root@elktst01 conf.d]# java -version
java version "1.8.0_152"

input {
    jdbc {
        jdbc_validate_connection => false
        jdbc_driver_library => "/root/sqljdbc_4.2/enu/jre8/sqljdbc42.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => "jdbc:sqlserver://server1:1433;databaseName=db1;integratedSecurity=false;"
        jdbc_user => "user1"
        jdbc_password => "password1"
        statement => "select * from dbo.v_ASSET"
        clean_run=>true
       }
}

[ERROR] 2019-09-10 14:54:22.101 [[main]<jdbc] jdbc - Unable to connect to database. Tried 1 times {:error_message=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: Login failed for user 'user1'. ClientConnectionId:69dffba5-4185-442e-8a33-d3b6e0663019"}

[ERROR] 2019-09-10 15:03:24.015 [[main]<jdbc] javapipeline - A plugin had an unrecoverable error. Will restart this plugin.

Error: Java::ComMicrosoftSqlserverJdbc::SQLServerException: Login failed for user 'user1'. ClientConnectionId:5f31bdf1-6be8-4b74-aa56-7f4858a7c62d
Exception: Sequel::DatabaseConnectionError

But I am able to login to database using microsoft sql server management studio. though this user account is AD account.

3

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