JDBC connection not working

Elastic Stack Elasticsearch
1

Hi all,

I have a database on my MSSQL 2012 server and i am trying to get my database to elasticsearch with logstash.

I did
I installed jdk 8
I installed sqljdbc 4.2
I Added the java and jdbc to Environment path
Iam using ELK 7.3

and my config file like this :

  input {
    jdbc {
 jdbc_connection_string => 
"jdbc:sqlserver://localhost;database=DATABASE;user=USER;password=PASSWORD"
     jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    # The path to our downloaded jdbc driver
   jdbc_driver_library => "C:\Users\user\Desktop\sqljdbc_4.2\enu\jre8\sqljdbc.jar"
   jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
   jdbc_user => "USER"
   jdbc_password =>"PASSWORD"
  statement => "select * from TABLE"		
  }
}

this giving me that error :

'Error: com.microsoft.sqlserver.jdbc.SQLServerDriver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?'

any helps ? :slight_smile:

2

Sorry i add

jdbc_driver_library => "C:\Users\user\Desktop\sqljdbc_4.2\enu\jre8\sqljdbc42.jar"

and worked. :slight_smile:

BTW if you have windows authentication on your MSSQL add
integratedSecurity=false;
to your connection string for those who will look this page. :slight_smile:

3

Mind if I ask where are you putting all these code, hopefully I can replicate your success in my environment. Thanks

4

If you want to copy MS SQL data to elasticsearch index you should write these codes to config file of logstash

What I did? :

1-first create new config file (because we do not want to change our default config file for 1 operation)
2- Write codes below
3- Give a name for config file, I gave jdbc.config

code :

input {
   jdbc {
    jdbc_connection_string =>"jdbc:sqlserver://LOCALHOST;databaseName=DATABASE;user=user_id;password=user_password;integratedSecurity=false;"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_driver_library => "C:\Users\user\Desktop\sqljdbc_4.2\enu\jre8\sqljdbc42.jar"
jdbc_user => "user_id"
jdbc_password =>"user_password"
statement => "SELECT * FROM YOUR_DATABASE"
 }
}


output {
elasticsearch {
hosts => ["192.xx.xx:9200"] YOUR ELASTIC HOST YOU CAN CHANGE WITH LOCALHOST
user => "YOUR ELASTIC USERNAME IF YOU HAVE "  
password => "YOUR ELASTIC PASSWORD IF YOU HAVE"
index => "sql_extended"
 }
}

You should download jdk 8 and sqljdbc4.2 and copy their paths to environment path (if you dont know how to do it u can search on google u will find easily)

i hope this help if you have question, u can ask me.

BTW : You should run logstash with your new config file , do not forget.

5

I had some problem with sqlserver.

my elk cluster is on linux and sqlserver on windows.
do I have to install any driver on sqlserver?

I am using open jdk12 and
/sqljdbc_7.4/enu/mssql-jdbc-7.4.1.jre12.jar

6

actully i did not tried with jdk12 so i do not know if it works but

i think it will not be a problem for linux because u will write your own elasticsearch host to output (i assume your logstash is working on windows)

where is your logstash working linux or windows? maybe it will be a problem.

7

Logstash is working on linux.

I have a post for my problem

https://discuss.elastic.co/t/jdbc-for-sqlserver/198058

8

it cannot find your jdbc
did you add your jdk and your sqljdbc to system environment path ?

this may be a version problem i mean i do not know elasticsearch is supporting jre12

btw if you write

jdbc_validate_connection => true

on your connectionstring sql might try a windows authentication + sql authentication so it should be a problem after fix your version problem

9

well I am running elk 7.3.1 and it does supports openjdk12 per metrix

https://www.elastic.co/support/matrix#matrix_jvm

10

it is better to use java jdk8 and jdbc 4.2 at least i tried :slight_smile:

BTW : if you fix your version problem u should check your jvm.config before run logstash because your

-Xms1g
-Xmx1g

settings will use default value. these means that minimum memory and maximum memory it is better to use with same setting. If you have big data u should increase these settings like

-Xms4g
-Xmx4g

(it is better to use half of your memory) these means that use minimum 4 gb ram and maximum 4 gb ram. If you use with 1g you might get java heap size error when you copy your database. I hope this will help you :slight_smile:

11

yes I am using about 15gig for jvm because I have 132gig ram on server.

Alright tested jdk8 and jdbc4.2 and gives me client connection error.

looks like something needs to be done on sqlserver host.