Hello,
I'm trying to index data from a SQL Server to Elasticsearch via the plugin Logstash JDBC. I'm working on a Windows Environment with Elastic version 6.4.2
Here you can find the configuration file for Logstash:
input {
jdbc {
jdbc_driver_library => "C:\Users\ltssyma01\Downloads\JDBC\sqljdbc_4.2\fra\jre8\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://ltssqltgsetest:1433;databaseName=tgse;integratedSecurity=false;"
jdbc_user => "LTSW2K\ltssyma01"
jdbc_password => "******"
statement => "SELECT * FROM datalab.legview"
}
}
output {
elasticsearch {
hosts => ["http://ltssuela18:9200/"]
index => "tgse_legview"
document_type => "tgse"
}
}
I succeed in indexing with a SQL account in parameters jdbc_user and jdbc_password, but I want to do it with Windows Authentification. And when I put my Windows Authentification in parameters jdbc_user and jdbc_password, I have the following error:
C:\Users\ltsaie1\Downloads\logstash-6.4.2>bin\logstash -f TGSELegView.conf
Sending Logstash logs to C:/Users/ltsaie1/Downloads/logstash-6.4.2/logs which is now configured via log4j2.properties
[2019-07-24T08:47:04,821][INFO ][logstash.runner ] Starting Logstash {"logstash.version"=>"6.4.2"}
[2019-07-24T08:47:06,412][WARN ][logstash.outputs.elasticsearch] You are using a deprecated config setting "document_type" set in elasticsearch. Deprecated settings will continue to work, but are scheduled for removal from logstash in the future. Document types are being deprecated in Elasticsearch 6.0, and removed entirely in 7.0. You should avoid this feature If you have any questions about this, please visit the #logstash channel on freenode irc. {:name=>"document_type", :plugin=><LogStash::Outputs::ElasticSearch index=>"tgse_legview", id=>"765301ea34079c4742c1668a382e654b2a0c9d8dc61fb04f2951777d7be8b9f2", hosts=>[http://ltssuela18:9200/], document_type=>"tgse", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_d7ea3500-e804-4970-b439-69b3d7803660", enable_metric=>true, charset=>"UTF-8">, workers=>1, manage_template=>true, template_name=>"logstash", template_overwrite=>false, doc_as_upsert=>false, script_type=>"inline", script_lang=>"painless", script_var_name=>"event", scripted_upsert=>false, retry_initial_interval=>2, retry_max_interval=>64, retry_on_conflict=>1, action=>"index", ssl_certificate_verification=>true, sniffing=>false, sniffing_delay=>5, timeout=>60, pool_max=>1000, pool_max_per_route=>100, resurrect_delay=>5, validate_after_inactivity=>10000, http_compression=>false>}
[2019-07-24T08:47:06,459][INFO ][logstash.pipeline ] Starting pipeline {:pipeline_id=>"main", "pipeline.workers"=>8, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>50}
[2019-07-24T08:47:06,958][INFO ][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://ltssuela18:9200/]}}
[2019-07-24T08:47:06,958][INFO ][logstash.outputs.elasticsearch] Running healthcheck to see if an Elasticsearch connection is working {:healthcheck_url=>http://ltssuela18:9200/, :path=>"/"}
[2019-07-24T08:47:07,130][WARN ][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>"http://ltssuela18:9200/"}
[2019-07-24T08:47:07,177][INFO ][logstash.outputs.elasticsearch] ES Output version determined {:es_version=>6}
[2019-07-24T08:47:07,208][INFO ][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["http://ltssuela18:9200/"]}
[2019-07-24T08:47:07,224][INFO ][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2019-07-24T08:47:07,239][INFO ][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-*", "version"=>60001,"settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"_default_"=>{"dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date"}, "@version"=>{"type"=>"keyword"}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}}
[2019-07-24T08:47:08,238][INFO ][logstash.pipeline ] Pipeline started successfully {:pipeline_id=>"main", :thread=>"#<Thread:0x6c7c16a1 sleep>"}
[2019-07-24T08:47:08,269][INFO ][logstash.agent ] Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}
[2019-07-24T08:47:08,549][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}
[2019-07-24T08:47:10,343][ERROR][logstash.inputs.jdbc ] Unable to connect to database. Tried 1 times {:error_message=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: Login failed for user 'LTSW2K\\ltssyma01'. ClientConnectionId:0d43108e-2b32-4779-b104-c150a0ab3bc5"}
[2019-07-24T08:47:10,359][ERROR][logstash.pipeline ] A plugin had an unrecoverable error. Will restart this plugin. Pipeline_id:main Plugin: <LogStash::Inputs::Jdbc jdbc_user=>"LTSW2K\\ltssyma01", jdbc_password=><password>, statement=>"SELECT * FROM datalab.legview", jdbc_driver_library=>"C:\\Users\\ltsaie1\\Downloads\\JDBC\\sqljdbc_4.2\\fra\\jre8\\sqljdbc42.jar", jdbc_connection_string=>"jdbc:sqlserver://ltssqltgsetest:1433;databaseName=tgse;integratedSecurity=false;", id=>"c075be8047deebc4018a44ce2f8ec97e6e2ab1ce8ee087247d0e85e68fb1450f", jdbc_driver_class=>"com.microsoft.sqlserver.jdbc.SQLServerDriver", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_1ff009fa-8ffe-4667-9822-de5adf2b6ee3", 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"=>2019-07-15 17:36:04 +0200}, last_run_metadata_path=>"C:\\Users\\ltsaie1/.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: Login failed for user 'LTSW2K\ltssyma01'. ClientConnectionId:0d43108e-2b32-4779-b104-c150a0ab3bc5 Exception: Sequel::DatabaseConnectionError ...
Is there any tricks to do to be able to use JDBC plugin with Windows Authentification ? Can you help me ?
Thanks