Elastic Search JDBC River Plugin SQL Server Integrated Security

Question

So I've been working on implementing elastic search using the JDBC River plugin to get data from our SQL Server DB into elastic search.

I've got it working fine using the SQL Server credentials, but trying to use integrated security doesn't work. It will create the index, but it doesn't have data in it. The parameters I've been using are:

  PUT /_river/test_river/_meta 
    {  
       "type":"jdbc",
       "jdbc":
             {
                "driver":"com.microsoft.sqlserver.jdbc.SQLServerDriver", 
                "url":"jdbc:sqlserver://testServer:1433;databaseName=TestDb;
                integratedSecurity=true;",
                "user":"",
                "password":"",
                "sql": "select * from users",
                "poll":"30s",
                "index":"testindex",
                "type":"testusers"
             }
     }

I've tried quite a few things, including removing the user and password fields completely, removing integratedSecurity=true, but it gave the same result. I've checked on their github for the river plugin and it says this issue was fixed back in January, but it still doesn't seem to be working.

Also I'm using elastic search version: 1.5.1 and jdbc river plugin version : 1.4.0.10

Any help would be much appreciated

score 3 · Accepted Answer · answered Apr 26 '15 at 01:17

Get rid of the user and password options. You're not gonna need them.

Check the console when running elasticserch.bat, you should see an error message when it tries to update the river. I'm going to go out on a limb and assume you're probably seeing an error stating that the file sqljdbc_auth.dll can't be found. If this is the case, you can download this file from here and copy the x64 version of sqljdbc_auth.dll to your java lib folder. For me, this folder is C:\ProgramData\Oracle\Java\javapath but you can type echo %path% in a console window to find yours.

Once you have followed these steps, restart elasticsearch.bat, and it should start processing your river. If not, post back with the output you're seeing when running elasticsearch.bat.

Elastic Search JDBC River Plugin SQL Server Integrated Security

1 Answers1