# JDBC connection not working
**URL:** https://discuss.elastic.co/t/jdbc-connection-not-working/198175
**Category:** Elasticsearch
**Created:** [September 5, 2019, 6:54am UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175 "2019-09-05T06:54:37Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@zbawio](https://discuss.elastic.co/u/zbawio)
#### Post date: [September 5, 2019, 6:54am UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/1 "2019-09-05T06:54:37Z")
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 ? 🙂
---
### Author:  [@zbawio](https://discuss.elastic.co/u/zbawio)
#### Post date: [September 5, 2019, 7:42am UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/2 "2019-09-05T07:42:46Z")
Sorry i add
```
jdbc_driver_library => "C:\Users\user\Desktop\sqljdbc_4.2\enu\jre8\sqljdbc42.jar"
```
and worked. 🙂
BTW if you have windows authentication on your MSSQL add
`integratedSecurity=false;`
to your connection string for those who will look this page. 🙂
---
### Author:  [@beezit](https://discuss.elastic.co/u/beezit)
#### Post date: [September 5, 2019, 12:46pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/3 "2019-09-05T12:46:33Z")
Mind if I ask where are you putting all these code, hopefully I can replicate your success in my environment. Thanks
---
### Author:  [@zbawio](https://discuss.elastic.co/u/zbawio)
#### Post date: [September 5, 2019, 1:09pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/4 "2019-09-05T13:09:40Z")
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.
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [September 5, 2019, 1:13pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/5 "2019-09-05T13:13:44Z")
> [@zbawio](#):
>
> Give a name for config file, I gave jdbc.config
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
---
### Author:  [@zbawio](https://discuss.elastic.co/u/zbawio)
#### Post date: [September 5, 2019, 1:19pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/6 "2019-09-05T13:19:11Z")
> [@elasticforme](#):
>
> I am using open jdk12 and
> /sqljdbc\_7.4/enu/mssql-jdbc-7.4.1.jre12.jar
actully i did not tried with jdk12 so i do not know if it works but
> [@zbawio](#):
>
> 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" }
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.
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [September 5, 2019, 1:20pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/7 "2019-09-05T13:20:38Z")
> [@zbawio](#):
>
> king linux or window
Logstash is working on linux.
I have a post for my problem
[https://discuss.elastic.co/t/jdbc-for-sqlserver/198058](https://discuss.elastic.co/t/jdbc-for-sqlserver/198058)
---
### Author:  [@zbawio](https://discuss.elastic.co/u/zbawio)
#### Post date: [September 5, 2019, 1:27pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/8 "2019-09-05T13:27:07Z")
> [@Jdbc for sqlserver](https://discuss.elastic.co/t/jdbc-for-sqlserver/198058/1):
>
> Error: com.microsoft.sqlserver.jdbc.SQLServerDriver not loaded. Are you sure you've included the correct jdbc driver in :jdbc\_driver\_library?
it cannot find your jdbc
did you add your jdk and your sqljdbc to system environment path ?
> [@Jdbc for sqlserver](https://discuss.elastic.co/t/jdbc-for-sqlserver/198058/1):
>
> jdbc\_driver\_library =\> "/root/sqljdbc\_7.4/enu/mssql-jdbc-7.4.1.jre12.jar" jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"
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
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [September 5, 2019, 1:37pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/9 "2019-09-05T13:37:12Z")
well I am running elk 7.3.1 and it does supports openjdk12 per metrix
[https://www.elastic.co/support/matrix#matrix\_jvm](https://www.elastic.co/support/matrix#matrix_jvm)
---
### Author:  [@zbawio](https://discuss.elastic.co/u/zbawio)
#### Post date: [September 5, 2019, 2:10pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/10 "2019-09-05T14:10:48Z")
it is better to use java jdk8 and jdbc 4.2 at least i tried 🙂
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 🙂
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [September 5, 2019, 3:21pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/11 "2019-09-05T15:21:59Z")
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.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [October 3, 2019, 3:28pm UTC](https://discuss.elastic.co/t/jdbc-connection-not-working/198175/12 "2019-10-03T15:28:35Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.