Unable to connect to azure sql database

Elastic Stack Logstash
1

Hi i'm new to logstash and trying to import data from azure sql db. However i'm getting an error when trying to connect with the database:
ELK stack : 7.4.2

Conf file:

input {  
jdbc {

jdbc_connection_string => "jdbc:sqlserver://****.database.windows.net:1433;database=****;encrypt=true;hostNameInCertificate=*.database.windows.net"

# The user you want to execute your statement as
jdbc_user => "xxx"
# The path to your downloaded jdbc driver
jdbc_driver_library => "C:\Elastic_Stack\logstash-7.4.2\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 rel"
}
}
output {  
elasticsearch {
 hosts => ["localhost:9200"]
 index =>  "test_rel"
 }
stdout { codec => rubydebug }
   }

Error:

[2019-11-19T19:41:56,452][INFO ][logstash.javapipeline    ][main] Pipeline started {"pipeline.id"=>"main"}
[2019-11-19T19:41:56,512][INFO ][logstash.agent           ] Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}
[2019-11-19T19:41:56,853][INFO ][logstash.agent           ] Successfully started Logstash API endpoint {:port=>9600}
[2019-11-19T19:41:57,679][ERROR][logstash.inputs.jdbc     ][main] Unable to connect to database. Tried 1 times {:error_message=>"Java::JavaLang::NoClassDefFoundError: javax/xml/bind/DatatypeConverter"}
[2019-11-19T19:41:57,698][ERROR][logstash.javapipeline    ][main] A plugin had an unrecoverable error. Will restart this plugin.

Can someone explain me where my problem is, and what i can do with it. Much appreciated.

2

My guess, never having used Azure or even a jdbc input, is that you have a column which is of type xml. I suggest you try a couple of things

Instead of "SELECT *" just select a single column that is simple text, or an integer, or a timestamp. Something we would expect the input to handle. Verify that you get a clean startup and some events.

If you know have an xml column, do a SELECT of just that column and verify the error occurs. Then modify the SELECT to CAST the type of the column to VARCHAR or something more text like and see if the error goes away.

Let us know what happens.

3

Your guess was correct apparently there was a xml column in my table
just selecting id, name solved this.

thx

4

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