Connection sqlserver to elasticsearch

Khadija_BOUDINAR1 · June 13, 2022, 1:45pm

hi All,
to connect sqlserver to Elasticsearch i have configure the file :sql.conf as following :


input {
    jdbc {
        # SqlServer jdbc connection string to our database, employeedb
        #  "jdbc:sqlserver://HostName\instanceName;database=DBName;user=UserName;password=Password" 
        jdbc_connection_string => "jdbc:sqlserver://localhost\DESKTOP-72D32P6\SQLEXPRESS;database=sales;user=;password="
        # The user we want to execute our statement as
        jdbc_user => nil
        # The path to our downloaded jdbc driver
        jdbc_driver_library => "C:\Users\Lenovo\Desktop\ELK\logstash-8.1.0\logstash-core\lib\jars\sqljdbc42.jar"
        # The name of the driver class for SqlServer
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        # Query for testing purpose
        statement => "SELECT * from janvier"
    }
}
output {
    stdout { codec => json_lines }
}

when i tried cmd logstash :logstash -f sql.conf
i detected this error:

bcddb56c15]
com.microsoft.sqlserver.jdbc.SQLServerException: ├ëchec de la connexion ├á l'h├┤te localhost, instance nomm├®e sqlexpress. Erreur┬á: ┬½┬ájava.net.SocketTimeoutException: Receive timed out┬á┬╗. V├®rifiez le nom du serveur et celui de l'instance et assurez-vous qu'aucun pare-feu ne bloque le trafic UDP vers le port┬á1434. Pour SQL Server 2005 ou version ult├®rieure, v├®rifiez que le service SQL Server Browser est en cours d'ex├®cution sur l'h├┤te.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(com/microsoft/sqlserver/jdbc/SQLServerException.java:234) ~[mssql-jdbc-7.4.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:5963) ~[mssql-jdbc-7.4.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.primaryPermissionCheck(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:2383) ~[mssql-jdbc-7.4.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:2126) ~[mssql-jdbc-7.4.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:1993) ~[mssql-jdbc-7.4.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:1164) ~[mssql-jdbc-7.4.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(com/microsoft/sqlserver/jdbc/SQLServerDriver.java:760) ~[mssql-jdbc-7.4.1.jre8.jar:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(jdk/internal/reflect/NativeMethodAccessorImpl.java:62) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(jdk/internal/reflect/DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:566) ~[?:?]
        at org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:426) ~[jruby.jar:?]
        at org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:293) ~[jruby.jar:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.adapters.jdbc.connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/adapters/jdbc.rb:237) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.connection_pool.make_new(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/connection_pool.rb:122) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.connection_pool.threaded.assign_connection(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/connection_pool/threaded.rb:209) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.connection_pool.threaded.acquire(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/connection_pool/threaded.rb:139) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.connection_pool.threaded.hold(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/connection_pool/threaded.rb:91) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.database.connecting.synchronize(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/database/connecting.rb:269) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.database.connecting.test_connection(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/database/connecting.rb:278) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.database.misc.initialize(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/database/misc.rb:175) ~[?:?]
        at org.jruby.RubyClass.newInstance(org/jruby/RubyClass.java:939) ~[jruby.jar:?]
        at org.jruby.RubyClass$INVOKER$i$newInstance.call(org/jruby/RubyClass$INVOKER$i$newInstance.gen) ~[jruby.jar:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.database.connecting.connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/database/connecting.rb:57) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.core.connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/core.rb:124) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.plugin_mixins.jdbc.jdbc.jdbc_connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/jdbc.rb:117) ~[?:?]
        at org.jruby.RubyKernel.loop(org/jruby/RubyKernel.java:1442) ~[jruby.jar:?]
        at org.jruby.RubyKernel$INVOKER$s$0$0$loop.call(org/jruby/RubyKernel$INVOKER$s$0$0$loop.gen) ~[jruby.jar:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.plugin_mixins.jdbc.jdbc.jdbc_connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/jdbc.rb:114) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.plugin_mixins.jdbc.jdbc.open_jdbc_connection(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/jdbc.rb:157) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.plugin_mixins.jdbc.jdbc.execute_statement(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/jdbc.rb:214) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.inputs.jdbc.execute_query(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/inputs/jdbc.rb:339) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.inputs.jdbc.run(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/inputs/jdbc.rb:307) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.logstash_minus_core.lib.logstash.java_pipeline.inputworker(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/logstash-core/lib/logstash/java_pipeline.rb:409) ~[?:?]
        at C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.logstash_minus_core.lib.logstash.java_pipeline.start_input(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/logstash-core/lib/logstash/java_pipeline.rb:400) ~[?:?]
        at org.jruby.RubyProc.call(org/jruby/RubyProc.java:318) ~[jruby.jar:?]
        at java.lang.Thread.run(java/lang/Thread.java:829) [?:?]
[2022-06-13T10:59:37,570][ERROR][logstash.inputs.jdbc     ][main][3d693d32a401325100048664ffdd9a126d8095e98dffe58e2489e8bcddb56c15] Unable to connect to database. Tried 1 times {:message=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: ├ëchec de la connexion ├á l'h├┤te localhost, instance nomm├®e sqlexpress. Erreur┬á: ┬½┬ájava.net.SocketTimeoutException: Receive timed out┬á┬╗. V├®rifiez le nom du serveur et celui de l'instance et assurez-vous qu'aucun pare-feu ne bloque le trafic UDP vers le port┬á1434. Pour SQL Server 2005 ou version ult├®rieure, v├®rifiez que le service SQL Server Browser est en cours d'ex├®cution sur l'h├┤te.", :exception=>Sequel::DatabaseConnectionError, :cause=>com.microsoft.sqlserver.jdbc.SQLServerException: ├ëchec de la connexion ├á l'h├┤te localhost, instance nomm├®e sqlexpress. Erreur┬á: ┬½┬ájava.net.SocketTimeoutException: Receive timed out┬á┬╗. V├®rifiez le nom du serveur et celui de l'instance et assurez-vous qu'aucun pare-feu ne bloque le trafic UDP vers le port┬á1434. Pour SQL Server 2005 ou version ult├®rieure, v├®rifiez que le service SQL Server Browser est en cours d'ex├®cution sur l'h├┤te., :backtrace=>["com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(com/microsoft/sqlserver/jdbc/SQLServerException.java:234)", "com.microsoft.sqlserver.jdbc.SQLServerConnection.getInstancePort(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:5963)", "com.microsoft.sqlserver.jdbc.SQLServerConnection.primaryPermissionCheck(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:2383)", "com.microsoft.sqlserver.jdbc.SQLServerConnection.login(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:2126)", "com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:1993)", "com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(com/microsoft/sqlserver/jdbc/SQLServerConnection.java:1164)", "com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(com/microsoft/sqlserver/jdbc/SQLServerDriver.java:760)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke(jdk/internal/reflect/NativeMethodAccessorImpl.java:62)", "jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(jdk/internal/reflect/DelegatingMethodAccessorImpl.java:43)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:566)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:426)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:293)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.adapters.jdbc.connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/adapters/jdbc.rb:237)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.connection_pool.make_new(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/connection_pool.rb:122)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.connection_pool.threaded.assign_connection(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/connection_pool/threaded.rb:209)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.connection_pool.threaded.acquire(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/connection_pool/threaded.rb:139)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.connection_pool.threaded.hold(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/connection_pool/threaded.rb:91)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.database.connecting.synchronize(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/database/connecting.rb:269)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.database.connecting.test_connection(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/database/connecting.rb:278)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.database.misc.initialize(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/database/misc.rb:175)", "org.jruby.RubyClass.newInstance(org/jruby/RubyClass.java:939)", "org.jruby.RubyClass$INVOKER$i$newInstance.call(org/jruby/RubyClass$INVOKER$i$newInstance.gen)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.database.connecting.connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/database/connecting.rb:57)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_53_dot_0.lib.sequel.core.connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/sequel-5.53.0/lib/sequel/core.rb:124)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.plugin_mixins.jdbc.jdbc.jdbc_connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/jdbc.rb:117)", "org.jruby.RubyKernel.loop(org/jruby/RubyKernel.java:1442)", "org.jruby.RubyKernel$INVOKER$s$0$0$loop.call(org/jruby/RubyKernel$INVOKER$s$0$0$loop.gen)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.plugin_mixins.jdbc.jdbc.jdbc_connect(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/jdbc.rb:114)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.plugin_mixins.jdbc.jdbc.open_jdbc_connection(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/jdbc.rb:157)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.plugin_mixins.jdbc.jdbc.execute_statement(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/plugin_mixins/jdbc/jdbc.rb:214)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.inputs.jdbc.execute_query(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/inputs/jdbc.rb:339)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.vendor.bundle.jruby.$2_dot_5_dot_0.gems.logstash_minus_integration_minus_jdbc_minus_5_dot_2_dot_2.lib.logstash.inputs.jdbc.run(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.2.2/lib/logstash/inputs/jdbc.rb:307)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.logstash_minus_core.lib.logstash.java_pipeline.inputworker(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/logstash-core/lib/logstash/java_pipeline.rb:409)", "C_3a_.Users.Lenovo.Desktop.ELK.logstash_minus_8_dot_1_dot_0.logstash_minus_core.lib.logstash.java_pipeline.start_input(C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/logstash-core/lib/logstash/java_pipeline.rb:400)", "org.jruby.RubyProc.call(org/jruby/RubyProc.java:318)", "java.lang.Thread.run(java/lang/Thread.java:829)"]}
[2022-06-13T10:59:37,589][WARN ][logstash.inputs.jdbc     ][main][3d693d32a401325100048664ffdd9a126d8095e98dffe58e2489e8bcddb56c15] Exception when executing JDBC query {:exception=>Sequel::DatabaseConnectionError, :message=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: ├ëchec de la connexion ├á l'h├┤te localhost, instance nomm├®e sqlexpress. Erreur┬á: ┬½┬ájava.net.SocketTimeoutException: Receive timed out┬á┬╗. V├®rifiez le nom du serveur et celui de l'instance et assurez-vous qu'aucun pare-feu ne bloque le trafic UDP vers le port┬á1434. Pour SQL Server 2005 ou version ult├®rieure, v├®rifiez que le service SQL Server Browser est en cours d'ex├®cution sur l'h├┤te.", :cause=>"com.microsoft.sqlserver.jdbc.SQLServerException: ├ëchec de la connexion ├á l'h├┤te localhost, instance nomm├®e sqlexpress. Erreur┬á: ┬½┬ájava.net.SocketTimeoutException: Receive timed out┬á┬╗. V├®rifiez le nom du serveur et celui de l'instance et assurez-vous qu'aucun pare-feu ne bloque le trafic UDP vers le port┬á1434. Pour SQL Server 2005 ou version ult├®rieure, v├®rifiez que le service SQL Server Browser est en cours d'ex├®cution sur l'h├┤te."}
[2022-06-13T10:59:37,857][INFO ][logstash.javapipeline    ][main] Pipeline terminated {"pipeline.id"=>"main"}
[2022-06-13T10:59:37,978][INFO ][logstash.pipelinesregistry] Removed pipeline from registry successfully {:pipeline_id=>:main}
[2022-06-13T10:59:38,019][INFO ][logstash.runner          ] Logstash shut down.

C:\Users\Lenovo\Desktop\ELK\logstash-8.1.0\bin>logstash -f sql.conf
"Using bundled JDK: ."
OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
Sending Logstash logs to C:/Users/Lenovo/Desktop/ELK/logstash-8.1.0/logs which is now configured via log4j2.properties
[2022-06-13T11:01:53,430][INFO ][logstash.runner          ] Log4j configuration path used is: C:\Users\Lenovo\Desktop\ELK\logstash-8.1.0\config\log4j2.properties
[2022-06-13T11:01:53,466][INFO ][logstash.runner          ] Starting Logstash {"logstash.version"=>"8.1.0", "jruby.version"=>"jruby 9.2.20.1 (2.5.8) 2021-11-30 2a2962fbd1 OpenJDK 64-Bit Server VM 11.0.13+8 on 11.0.13+8 +indy +jit [mswin32-x86_64]"}
[2022-06-13T11:01:53,472][INFO ][logstash.runner          ] JVM bootstrap flags: [-Xms1g, -Xmx1g, -XX:+UseConcMarkSweepGC, -XX:CMSInitiatingOccupancyFraction=75, -

grumo35 · June 13, 2022, 4:17pm

HI,

With this command we should be able to check if your instance of sqlserver is actually listening to 1434.

netstat -a -b

You should also note that you need the SQL Server Browser service running can you check that aswell ?

Khadija_BOUDINAR1 · June 13, 2022, 5:35pm

Now i have this error
com.microsoft.sqlserver.jdbc.SQLServerException: Connection failed to host DESKTOP-72D32P6, instance named sqlexpress. Error┬á: ┬½┬ájava.net.SocketTimeoutException: Receive timed out┬á┬╗. Check the server name and instance name and make sure no firewall is blocking UDP traffic to port┬á1434. For SQL Server 2005 or later, verify that the SQL Server Browser service is running on the host.

Khadija_BOUDINAR1 · June 14, 2022, 9:14am

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING 3704
TCP [::]:1433 [::]:0 LISTENING 3704

grumo35 · June 14, 2022, 9:49am

I dont think your SQL Server setup is configured to accept jdbc requests take a look at the documentation of Microsoft SQL Server or find a tutorial with logstash & mssql.

You might need to install a jdbc driver.

Khadija_BOUDINAR1 · June 14, 2022, 11:51am

j'ai déjà installé un jdbc mais il existe une erreur SSL
voici le nouveau code


input {
    jdbc {
        
        jdbc_connection_string => "jdbc:sqlserver://.\SQLEXPRESS;databaseName=sales;integratedSecurity=false;"
        jdbc_user => ".\Lenovo"
        jdbc_driver_library => "C:\Users\Lenovo\Desktop\ELK\logstash-8.1.0\logstash-core\lib\jars\mssql-jdbc-7.4.1.jre12.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        statement => "SELECT * FROM Janvier"
     
    }
}

output {
  elasticsearch {
  hosts => ["http://localhost:9200"]
    index => "cs_users"
    user => "c"
    password => "Ct"
    doc_as_upsert => true
    document_id => "%{Order ID}"
  }
}

encore une erreur

 Erreur┬á: ┬½┬áThe server selected protocol version TLS10 is not accepted by client preferences [TLS13, TLS12]┬á┬╗. ClientConnectionId:3ea52da0-756e-4f80-bec4-29be576af7a8", :cause=>"com.microsoft.sqlserver.jdbc.SQLServerException: Le pilote n'a pas pu ├®tablir de connexion s├®curis├®e au serveur SQL Server ├á l'aide du chiffrement SSL (Secure Sockets Layer). Erreur┬á: ┬½┬áThe server selected protocol version TLS10 is not accepted by client preferences [TLS13, TLS12]┬á┬╗. ClientConnectionId:3ea52da0-756e-4f80-bec4-29be576af7a8"}

grumo35 · June 14, 2022, 12:27pm

Re,

Tu dois configurer la version de TLS pour ta base de données mssql

Tâche de suivre cette KB de microsoft KB3135244 - TLS 1.2 support for Microsoft SQL Server

Il faut que tu fasses en sorte que TLS 1 soit supporté par ton serveur mssql ( au vu du message d'erreur )

Bon courage !

Khadija_BOUDINAR1 · June 15, 2022, 4:37am

j'ai essayé de modifier mssql jdbc version comme ceci:

input {

jdbc {

jdbc_driver_library => "C:\Users\Lenovo\Desktop\ELK\logstash-8.1.0\logstash-core\lib\jars\mssql-jdbc-7.4.1.jre11.jar"

#Placed my mssql-jdbc-7.4.1.jre8 JRE file in logstash ..\logstash-core\lib\jars\

jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"

jdbc_connection_string => "jdbc:sqlserver://localhost:1433;databaseName=sales;integratedSecurity=false;"

jdbc_user => "DESKTOP-72D32P6\Lenovo"

statement => "SELECT * FROM Janvier
}

}

output {
  stdout  { }
}

Capture d’écran 2022-06-15 053307

L'erreur peut être du a cause de ma version express de MSSQL ?

grumo35 · June 15, 2022, 11:45am

Hi,

Avez vous essayez d'installer le patch KB3135244 - TLS 1.2 support for Microsoft SQL Server ?

Votre serveur MSSQL doit être configurer pour supporter une version supérieur a TLS1.0

Khadija_BOUDINAR1 · June 15, 2022, 12:00pm

doit je réinstaller MSSQL? j'arrive pas a trouver le le patch [KB3135244]

Khadija_BOUDINAR1 · June 15, 2022, 2:47pm

j'ai trouvé enfin la solution
effacer la ligne existante du fichier java.security sous jdk de logstash:

jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, \
    DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL, \
    include jdk.disabled.namedCurves

Khadija_BOUDINAR1 · June 15, 2022, 2:53pm

mais il y arrive pas a lire la BD

Voici mon sql.conf
input {

jdbc

jdbc_driver_library =>"C:\Users\Lenovo\Desktop\ELK\logstash-8.1.0\logstash-core\lib\jars\msi-mssql-jdbc-2.0.3.jar"
#Placed my mssql-jdbc-7.4.1.jre8 JRE file in logstash ..\logstash-core\lib\jars\

jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"

jdbc_connection_string => "jdbc:sqlserver://localhost:1433;databaseName=sales;integratedSecurity=false;"

jdbc_user => "DESKTOP-72D32P6\Lenovo"

statement => "SELECT * FROM Janvier"
}

}

output {
stdout { }
}

system · July 13, 2022, 2:54pm

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

Topic		Replies	Views
Cannot able to get the SQLSERVER data into elasticsearch Logstash	2	288	August 9, 2019
SQL Server data to Elasticsearch using LogStash Logstash	4	393	October 14, 2021
JDBC connection not working Elasticsearch	11	1924	October 3, 2019
ERROR IN CONNECTING SQL SERVER TO ELASTIC SEARCH Logstash	2	452	June 21, 2020
SQL Server data to Elasticsearch using LogStash Logstash	31	6076	September 17, 2020

Connection sqlserver to elasticsearch

Related Topics