JDBC integrated security

Elastic Stack Logstash
1

Hello.

I'm currently in a POC stage in a project and have to collect data from a MS SQL Server.

I am using jdbc-input-plugin to connect to the database.

dbtest.conf
`input {
jdbc {
jdbc_driver_library => "C:\jdbc\jdbc_driver\sqljdbc_6.0\enu\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://localhost:1433;Data Source=;Initial Catalog=;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
jdbc_user => ""
schedule => "* * * * *"
statement => "SELECT * FROM dbo.tblActivityLog"
}
}

output {
stdout {
codec => 'rubydebug'
}
}`

jdbc-user is a required field in the jdbc plugin, but seems to stop me from using integrated security. I have tried different versions, including an empty jdbc-user, the windows user (with and without the password), and other users that should have access.

I get "Login failed for user for all attempts.
Is there a workaround for using Integrated security, or maybe another software I can use for forwarding to logstash (similar to winlogbeat for eventlogs)?

Let me know if more information is needed.

Edit: I see there is a github issue for this from february github issue
The Issue does not look like a priority for the devs, since the other issues have priorityflags.

2

I have found a temporary solution. By creating an extra login credential on the server with sql server authorization I were able to get access without using Integrated Security.

This might not be a possible solution for our project. If anyone have a better solution I would love to hear about it!

3

It took me a while to figure out how I could query SQL Server. (since i'm not a Windows/Sqlserver expert :wink:
This is what I did (I'musing Logstash 2.3.2)

First add a login to SQLServer (e.g. username=logstash, password=logstash):
https://www.youtube.com/watch?v=3Lz9rFVRRfU

Then enable TCP in SQL Server Configuration Manager
Look under SQL Server Network Configuration > set TCP on enabled and add port 1433 as in this video:

try to: telnet localhost 1433
If this is right configure Logstash as follows:

input {
jdbc {
jdbc_driver_library => "C:\progs\sqljdbc_4.2\enu\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://HOSTNAME\INSTANCENAME:1433;databaseName=DBNAME"
jdbc_user => "logstash"
jdbc_password => "logstash"
statement => "SELECT * from TABLE"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
}
}

output {
stdout { codec => json_lines }
}

4

could we encrypt the password string? if can, what encryption that used?