Logstash JDBC to MSSQL - this driver is not configured for integrated authentication

[Mattness]

Hello,

I am trying to connect logstash to my sql server 2019.

My .conf file:

input {
jdbc {
jdbc_driver_library => "C:/sqljdbc_8.2/enu/mssql-jdbc-8.2.0.jre13.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://SERVERNAME:1433;databasename=DBNAME;integratedSecurity=true"
jdbc_user => "MYDOMAIN\USER"
jdbc_password => "***"
#parameters => { "" }
schedule => " * * * *"
statement => "SELECT TOP (1000) [optname]
,[value]
,[major_version]
,[minor_version]
,[revision]
,[install_failures]
FROM [master].[dbo].[MSreplication_options]"
}
}

output { stdout {} }

I recevie this Error Message:

[2020-03-09T09:53:14,801][ERROR][logstash.inputs.jdbc ] Unable to connect to database. Tried 1 times {:error_message=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: This driver is not configured for integrated security"}

So far I have tried to add the mssql-jdbc_auth-8.2.0.x64.dll to the
java.library path, the system PATH variable, the Java/JDK/bin directory and the Java/JDK/library directory. This changed nothing for me.

I also tried many different connection strings and both modes for server authentication (windows-authentication & Sql Server AND windows-authentication)

That just resulted in slightly different ErrorLogs, namely:

Login failed for user 'MYDOMAIN\USER'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only.

AND

Login failed for user 'MYDOMAIN\USER'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: ******]

Can someone please help me out?

[ITIC (Jordi)]

HI

If you are getting login failed errors I'd say you have your plugin properly configured and it is reaching the server, and there's a problem with your credentials and/or connection string.

Try adding sql_log_level => "debug" to your jdbc{} configuration, and see if you get more detailed information.

If you are trying to use a local SQL user, have you tried with jdbc_user => "USER", without the domain? What did you get?

Have you tried without the integratedSecurity=trueparameter? What did you get?

Hope this helps.

[Mattness]

Hi,

in the end I succeeded with the mixed authentication mode.
I created a basic SQL user in the management studio & used "Trusted_Connection=True;" in the .conf file.
for jdbc.user & password I used the credentials I defined via management studio.

Thanks for you effort I will try your suggestions in addition to my solution.