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.

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

12

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