Unable to connect to SQL Server 2008 R2 on Windows 7

Elastic Stack Logstash
1

I am running logstash 7.0 on Windows 7 with below configuration
SQL Server 2008 R2 (Remote)
jdbc verson 6.0.
I have also used jdbc 4.0 same error is received.

java version "1.8.0_211"
Java(TM) SE Runtime Environment (build 1.8.0_211-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.211-b12, mixed mode)

E:/Logging/logstash-7.0.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/jdbc.rb:221:in execute_statement' E:/Logging/logstash-7.0.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/inputs/jdbc.rb:277:in execute_query'
E:/Logging/logstash-7.0.0/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/inputs/jdbc.rb:263:in run' E:/Logging/logstash-7.0.0/logstash-core/lib/logstash/java_pipeline.rb:297:in inputworker'
E:/Logging/logstash-7.0.0/logstash-core/lib/logstash/java_pipeline.rb:290:in block in start_input' [2019-05-03T16:05:04,296][WARN ][logstash.runner ] SIGINT received. Shutting down. [2019-05-03T16:05:04,470][ERROR][logstash.inputs.jdbc ] Failed to load sqljdbc42.jar {:exception=>#<TypeError: failed to coerce jdk.internal.loader.ClassLoaders$AppClassLoader to java.net.URLClassLoader>} [2019-05-03T16:05:04,474][ERROR][logstash.javapipeline ] A plugin had an unrecoverable error. Will restart this plugin. Pipeline_id:main Plugin: <LogStash::Inputs::Jdbc jdbc_user=>"nil", statement=>"SELECT * FROM Table", jdbc_driver_library=>"sqljdbc42.jar", jdbc_connection_string=>"jdbc:sqlserver://server\\instance:1433;database=database;integratedSecurity=true", id=>"cfbecab99275f6f092994137c5b73e129c04590230b5f432bc56802110950dea", jdbc_driver_class=>"com.microsoft.sqlserver.jdbc.SQLServerDriver", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_20a20956-de89-44ac-b243-70063a80d6a0", enable_metric=>true, charset=>"UTF-8">, jdbc_paging_enabled=>false, jdbc_page_size=>100000, jdbc_validate_connection=>false, 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=>"C:\\Users\\username/.logstash_jdbc_last_run", use_column_value=>false, tracking_column_type=>"numeric", clean_run=>false, record_last_run=>true, lowercase_column_names=>true> **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: E:/Logging/logstash-7.0.0/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'

2

Did you double check that?

3

Yes. I did.

4

Apparently not. Could you at least include your config??

5 
input {
  jdbc {
    jdbc_driver_library => "sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_user => "userame"
    jdbc_connection_string => "jdbc:sqlserver://server\\instance:1433;database=database;integratedSecurity=true"
	jdbc_password => "xxx"
	statement => "SELECT * FROM table1"
  }
}

output {
  elasticsearch {
    hosts => ["localhost:9200"]
    index => "cs_users"
	#document_id => "%{Id}"
  }
}
6

Please read up on formatting code when posting. Lots of people will skip reading if its not formatted. And this is not all of your config. Where is the sqljdbc42.jar file located in the file system. Do you know that logstash finds it when it starts?

7

sqljdbc42.jar is located within logstash/bin directory. Even I have tried with explicit path and got same error.

8 
input {
jdbc {
              jdbc_driver_library => "sqljdbc42.jar"
              jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
              jdbc_user => "userame"
              dbc_connection_string=>
              "jdbc:sqlserver://server\instance:1433;
               database=database;integratedSecurity=true"
               jdbc_password => "xxx"
               statement => "SELECT * FROM table1"
}
}

output {
elasticsearch {
                       hosts => ["localhost:9200"]
                       index => "cs_users"
                       #document_id => "%{Id}"
}
}
9

Is there any way to debug this condition Logstash not able to find sqljdbc42.jar present in Logstash\bin.

10

try this,

input {

    jdbc {
    jdbc_driver_library => "C:/Users/xxxx/Downloads/sqljdbc4-2.0.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://localhost:1433;user=qqqq;password=vvvv@123;database=QWEQWE"
    jdbc_user => "qqqq"
    jdbc_password => "vvvv@123"
    statement => "SELECT TOP 1000 * FROM SAMPLE"
    jdbc_paging_enabled => "true"
    jdbc_page_size => "50000"
    }

    }
11

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