Does logstash jdbc connection work with Windows Authentication?

Elastic Stack Logstash
1

How to use logstash JDBC connection with Windows Authentication. The server does not accepts username and password even if they are passed

2

Hello,

I am having the same problem. I am using SQL server installed on a particular windows server and Logstash/Elasticsearch installed on a linux machine.
I am unable to import data to Elasticsearch using logstash JDBC connection with Windows Authentication.

See error in screen shot below:

error_jdbc
error_jdbc.png859×629 51.5 KB

And I am using the following settings in the configuration of logstash (on my local pc):

input { 
	jdbc{
			
			
			jdbc_driver_library => "C:\Users\g.mootheeveeren\Desktop\sqljdbc_4.2\enu\jre8\sqljdbc42.jar"	
			jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"					
			jdbc_connection_string => "jdbc:sqlserver://MAEGWDDFZ:1433;integratedsecurity=true;databasename=BI_STG_PROD"
			jdbc_user => "g.mootheeveeren"
			jdbc_password => ""			
			schedule => "* * * * *"			
			statement => "select * from TABLE_X"
	}
}


output {
  elasticsearch { 
		  hosts => ["localhost:9201"] 		  
		  index => "index1"
		  document_type => "index1"
		  document_id => "%{[id]}_%{[compare_date]}" 
				}
		stdout {}
		}

My question would be: Is there a way to import data from the SQL server using the WINDOWS authentication mode?

Thanks in advance,
Ganessen.

3

I am searching solution for the same issue. But, It doesn't seem to work.

Python script to sync data can be other option that can be tried.

4

Hello,

I did find a solution. You have to copy the file sqljdbc_auth.dll found in your jdbc driver folder, to your JAVA path C:\Program Files\Java\jre_Version\bin.
Dont forget to add user you are using to your SQL server in the statement: jdbc_user => "xxx"

Ganessen.

5

Thanks Ganessen!!
It worked for me as well. Instead of copying sqljdbc_auth.dll to C:\Program Files\Java\jre_Version\bin. I just added the dll path in Env variable.

6

Adding the dll path in the environment variable did not work for me, thats why i copied it to the mentioned path.

Your are welcomed..

7

Have you tried restarting the console? I had to restart the console for it to work.

8

Could you please send a screen shot where you added the dll path in your env. variable.

Thanks Palash.

9

image
I have added "C:\sqljdbc_4.2\enu\auth\x64" to path

10

Hello,

I am having issue.

I am importing data from a SQL server database to an elasticsearch instance installed on a linux machine where logstash is installed on the logstash.

See below for the config used for logstash:

input { 
    
	
	jdbc{			
		#jdbc_driver_library => "/home/adminemea/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"	#previously used driver library
		#jdbc_driver_library => "/usr/share/java/sqljdbc42.jar"	 						#previously used driver library
		jdbc_driver_library => "/home/adminemea/jtds-1.3.1/jtds-1.3.1.jar"
		
		
		#jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"     		#previously used driver class
		jdbc_driver_class => "Java::net.sourceforge.jtds.jdbc.Driver"					
		jdbc_connection_string => "jdbc:jtds:sqlserver://IPADRESS:1433;instanceName=INSTANCENAME;databasename=DATABASENAME;integratedSecurity=true"
		
		jdbc_user => "g.mootheeveeren"		
		schedule => "* * * * *"			
		statement => "select * from TABLEX"
		
	}
}



output {




			elasticsearch { 
			hosts => ["localhost:9200"] 
			user => "elastic"
			password => "octochangeme"
			index => "synchonisation"
			document_type => "synchonisation"
			
			}
			stdout {}
	
		}

Error obtained in logstash log:

[2018-08-03T13:32:00,942][ERROR][logstash.inputs.jdbc ] Unable to connect to database. Tried 1 times {:error_message=>"Java::JavaSql::SQLException: I/O Error: GSS Failed: Invalid name provided (Mechanism level: KrbException: Cannot locate default realm)"}

I have the following questions:

  1. Which JDBC driver library should i use?
    I should point out that I am using a linux machine.
    Driver used:
    1st attempt:
    https://www.microsoft.com/en-us/download/details.aspx?id=56615

image
image862×363 8.97 KB

2nd attempt:

  1. Why jdbc driver should i use?
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver" --> This corresponds to a windows OS.
    or
    jdbc_driver_class => "Java::net.sourceforge.jtds.jdbc.Driver" --> This corresponds to a linux OS.

  2. Is the jdbc connection being correctly used?
    jdbc_connection_string => "jdbc:jtds:sqlserver://IPADRESS:1433;instanceName=INSTANCENAME;databasename=DATABASENAME;integratedSecurity=true"

  3. I used the following command export CLASSPATH=/home/adminemea/sqljdbc_6.0/enu/jre8/sqljdbc42.jar to set path for Java.
    Should i re-execute the command if I'm using the soma another jar file.

  4. What should i do to resolve my problem?

Kind regards,
Ganessen.

11

Hello,

Can someone please help me?

Regards,
Ganessen.