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.