Connecting to SQL from Ubuntu VMPlayer


(Hassan Shaitou) #1

I am trying to create a conf file for logstash on my Ubuntu hosted on VMWare Player, to be able to connect to my remote SQL Server with this IP:192.168.237.1,49170.

1- I tried to ping the IP, and it's ok..
2- I tried the connection on windows it's ok...
3- I downloaded: sqljdbc4-4.0.jar on ubntu...
4- logstash version 6.0.0...
5- I disabled firewall completly...
6- my conf file:

input {
  jdbc {
    jdbc_driver_library => "/home/hassan/Desktop/sqljdbc4-4.0.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://192.168.237.1,49170;databaseName=Rubix;"
    jdbc_user => "DESKTOP-1U9CFID\Hassan Shaitou"
    statement => "SELECT * FROM Users;"
  }
}

output {
  stdout { codec => rubydebug }
}

after running bin/logstash -f db.conf, I get this error:

  Pipeline_id:main
  Plugin: <LogStash::Inputs::Jdbc jdbc_driver_library=>"/home/hassan/Desktop/sqljdbc4-4.0.jar", jdbc_driver_class=>"com.microsoft.sqlserver.jdbc.SQLServerDriver", jdbc_connection_string=>"jdbc:sqlserver://192.168.237.1,49170;databaseName=Rubix;", jdbc_user=>"DESKTOP-1U9CFID\\Hassan Shaitou", statement=>"SELECT * FROM Users;", id=>"b782c0b3f36e2a49b852de35d444455abc23bf80399d1e0049b75a1e7bbb051d", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_52400059-501e-4be1-b830-7ef0f983d7d7", 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"=>2018-01-30 17:23:00 UTC}, last_run_metadata_path=>"/home/hassan/.logstash_jdbc_last_run", use_column_value=>false, tracking_column_type=>"numeric", clean_run=>false, record_last_run=>true, lowercase_column_names=>true>
  Error: Java::ComMicrosoftSqlserverJdbc::SQLServerException: The TCP/IP connection to the host 192.168.237.1,49170, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
  Exception: Sequel::DatabaseConnectionError
  Stack: com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(com/microsoft/sqlserver/jdbc/SQLServerException.java:190)
com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(com/microsoft/sqlserver/jdbc/SQLServerException.java:241)
com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(com/microsoft/sqlserver/jdbc/IOBuffer.java:2243)
com.microsoft.sqlserver.jdbc.TDSChannel.open(com/microsoft/sqlserver/jdbc/IOBuffer.java:491)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:1309)
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:991)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:827)
com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(com/microsoft/sqlserver/jdbc/SQLServerDriver.java:1012)
java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)
org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:468)
org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:326)
RUBY.connect(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.2.0/lib/sequel/adapters/jdbc.rb:203)
RUBY.make_new(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.2.0/lib/sequel/connection_pool.rb:121)
RUBY.assign_connection(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.2.0/lib/sequel/connection_pool/threaded.rb:192)
RUBY.acquire(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.2.0/lib/sequel/connection_pool/threaded.rb:133)
RUBY.hold(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.2.0/lib/sequel/connection_pool/threaded.rb:90)
RUBY.synchronize(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.2.0/lib/sequel/database/connecting.rb:269)
RUBY.test_connection(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.2.0/lib/sequel/database/connecting.rb:279)
RUBY.connect(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.2.0/lib/sequel/database/connecting.rb:58)
RUBY.connect(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.2.0/lib/sequel/core.rb:116)
RUBY.block in jdbc_connect(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.0/lib/logstash/plugin_mixins/jdbc.rb:110)
org.jruby.RubyKernel.loop(org/jruby/RubyKernel.java:1292)
org.jruby.RubyKernel$INVOKER$s$0$0$loop.call(org/jruby/RubyKernel$INVOKER$s$0$0$loop.gen)
RUBY.jdbc_connect(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.0/lib/logstash/plugin_mixins/jdbc.rb:107)
RUBY.open_jdbc_connection(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.0/lib/logstash/plugin_mixins/jdbc.rb:159)
RUBY.execute_statement(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.0/lib/logstash/plugin_mixins/jdbc.rb:218)
RUBY.execute_query(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.0/lib/logstash/inputs/jdbc.rb:272)
RUBY.run(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.0/lib/logstash/inputs/jdbc.rb:256)
RUBY.inputworker(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/logstash-core/lib/logstash/pipeline.rb:574)
RUBY.block in start_input(/home/hassan/Desktop/elk/logstash/logstash-6.0.0/logstash-core/lib/logstash/pipeline.rb:567)
org.jruby.RubyProc.call(org/jruby/RubyProc.java:289)
org.jruby.RubyProc.call(org/jruby/RubyProc.java:246)
java.lang.Thread.run(java/lang/Thread.java:748)

(Hassan Shaitou) #2

It worked for me, If anyone has any problems regarding this issue I can help ...


(Erik W) #3

Well what was the solution? Did you not have TCP enabled as a protocol within SQL Server?


(Hassan Shaitou) #4

yes that is one + firewall permission for the port


(system) #5

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