Does logstash jdbc connection work with Windows Authentication?

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

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:

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.

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.

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.

1 Like

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.

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..

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

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

Thanks Palash.

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

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

2nd attempt:
https://sourceforge.net/projects/jtds/files/

  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.

2 Likes

Hello,

Can someone please help me?

Regards,
Ganessen.

1 Like

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