Errors in ODBC connection

[Prabhu292]

Hi rupal,

I have downloaded the SQL ODBC driver from this Link : https://opendistro.github.io/for-elasticsearch/downloads.html

I am getting the error as mentioned in the screenshot.

Please help me to fix this issue.

Originally posted by @Prabhu292 in #686 (comment)

[rupal-bq]

Hi @Prabhu292,

Are you able to send request curl -XGET http://localhost:9200 successfully? Please share logs as well.

The error indicates some connection issue

• Server might be down
• Auth might fail
• maybe SSL issue

[Prabhu292]

Hi rupal,

Thanks a lot for your response.

Yes i am able to send the request

I tried enabling the SSL and disabling the SSL still getting the same error.

Please do let me know where to find the logs?

Also Pasted the taco files in Tableau repository below is the screenshot

Also pasted the JDBC jar file in the Tableau driver path

[rupal-bq]

@Prabhu292 I am unable to reproduce this issue with 7.9.1. Is there a specific reason for using 7.4.0?

Try sending the request curl -XGET http://localhost:9200/_cat/plugins?format=json and share response.

Also, for getting logs, click on Logging Options in the DSN window and set the log level to LOG_ALL. Set Log Path and make sure you have access to this path. Then reopen the odbc data source administrator and reproduce the issue.

[Prabhu292]

No any specific reason for 7.4.0. Actually all our data which is currently being store here. Again if we re install the new version it will be a problem.

If i type curl -XGET http://localhost:9200/_cat/plugins?format=json in the command prompt. nothing is happening. Below is the screenshot.

Below is the log file. AFter testing the connection. I have disable SSL from Advance options and below is the output

mylog.c[logs_on_off]234: mylog_on=7 qlog_on=7
start_logging:Global.debug&commlog=7&7
mylog.c[InitializeLogging]533: Log Output Dir: C:\logs
es_odbc.c[DllMain]111: exe name=odbcad32
dlg_specif[CC_conninfo_init]452: entering opt=2
setup.c[ParseAttributes]584: aszKey='DSN', value='ODFE SQL ODBC DSN'
dlg_specif[copyConnAttributes]145: key='DSN' value='ODFE SQL ODBC DSN'
dlg_specif[CC_conninfo_init]452: entering opt=3
dlg_specif[getDSNinfo]224: drivername=elasticsearchodbc
setup.c[ParseAttributes]584: aszKey='DSN', value='ODFE SQL ODBC DSN'
dlg_specif[copyConnAttributes]145: key='DSN' value='ODFE SQL ODBC DSN'
es_odbc.c[DllMain]118: DETACHING odfesqlodbc.dll

[Prabhu292]

Forgot to post the error.

[rupal-bq]

@Prabhu292 SQL Plugin is essential for using ODBC driver. And it looks like you don't have an SQL plugin on the server. The only solution I can think of is upgrading to the version which has the SQL plugin support.

[Prabhu292]

Do you mean upgrading the version of elastic search 7.9.0?
Which version I need to install it to get the sql plug in?

[rupal-bq]

Yes, You can find all supported versions of elasticsearch here.

Otherwise, you can just use the latest Elasticsearch version from downloads.

[Prabhu292]

Thanks a lot for your patience. Much appreciated.

Surely I will upgrade the version as suggested.

Once it is working immediately I will close this issue.

Thank you once again.

[rupal-bq]

No worries. Let me know in case of any concerns.

[Prabhu292]

Hi rupal,

Sorry to bother you again.

As requested, I have updated the Elasticsearch version 7.9.1. but still getting the same error.

Below is the log file

mylog.c[logs_on_off]234: mylog_on=7 qlog_on=7
start_logging:Global.debug&commlog=7&7
mylog.c[InitializeLogging]533: Log Output Dir: C:\logs
es_odbc.c[DllMain]111: exe name=odbcad32
dlg_specif[CC_conninfo_init]452: entering opt=2
setup.c[ParseAttributes]584: aszKey='DSN', value='ODFE SQL ODBC DSN'
dlg_specif[copyConnAttributes]145: key='DSN' value='ODFE SQL ODBC DSN'
dlg_specif[CC_conninfo_init]452: entering opt=3
dlg_specif[getDSNinfo]224: drivername=elasticsearchodbc
setup.c[ParseAttributes]584: aszKey='DSN', value='ODFE SQL ODBC DSN'
dlg_specif[copyConnAttributes]145: key='DSN' value='ODFE SQL ODBC DSN'
setup.c[test_connection]440: conn_string=DRIVER=elasticsearchodbc;server=localhost;database=elasticsearch;port=9200;UID=;PWD=;auth=NONE;region=;useSSL=0;hostnameVerification=0;logLevel=7;logOutput=C:\logs;responseTimeout=10;fetchSize=-1;
odbcapi30.[SQLAllocHandle]33: entering
environ.c[ESAPI_AllocEnv]44: entering
environ.c[ESAPI_AllocEnv]60: leaving phenv=000002D5061EF250
odbcapi30.[SQLSetEnvAttr]431: entering att=200,3
odbcapi30.[SQLAllocHandle]33: entering
connection[ESAPI_AllocConnect]63: entering...
connection[ESAPI_AllocConnect]66: **** henv = 000002D5061EF250, conn = 000002D506213FC0
environ.c[EN_add_connection]502: entering self = 000002D5061EF250, conn = 000002D506213FC0
environ.c[EN_add_connection]531: added at 0, conn->henv = 000002D5061EF250, conns[0]->henv = 000002D5061EF250
odbcapiw.c[SQLDriverConnectW]122: entering
win_unicod[ucs2_to_utf8]126: 000000E7C7679E20 ilen=-3 newlen=196 olen=196 utf8str=DRIVER=elasticsearchodbc;server=localhost;database=elasticsearch;port=9200;UID=;PWD=;auth=NONE;region=;useSSL=0;hostnameVerification=0;logLevel=7;logOutput=C:\logs;responseTimeout=10;fetchSize=-1;
misc.c[make_string]115: malloc size=196
misc.c[make_string]117: str=000002D50619C8E0
dlg_specif[CC_conninfo_init]452: entering opt=2
drvconn.c[dconn_get_attributes]223: our_connect_string = 'DRIVER=elasticsearchodbc;server=localhost;database=elasticsearch;port=9200;UID=;PWD=;auth=NONE;region=;useSSL=0;hostnameVerification=0;logLevel=7;logOutput=C:\logs;responseTimeout=10;fetchSize=-1;'
dlg_specif[getDSNinfo]224: drivername=elasticsearchodbc
drvconn.c[dconn_get_attributes]223: our_connect_string = 'DRIVER=elasticsearchodbc;server=localhost;database=elasticsearch;port=9200;UID=;PWD=;auth=NONE;region=;useSSL=0;hostnameVerification=0;logLevel=7;logOutput=C:\logs;responseTimeout=10;fetchSize=-1;'
dlg_specif[copyConnAttributes]145: key='DRIVER' value='elasticsearchodbc'
dlg_specif[copyConnAttributes]145: key='server' value='localhost'
dlg_specif[copyConnAttributes]145: key='database' value='elasticsearch' not found
dlg_specif[copyConnAttributes]145: key='port' value='9200'
dlg_specif[copyConnAttributes]145: key='UID' value=''
dlg_specif[copyConnAttributes]121: key='PWD' value='xxxxxxxx'
dlg_specif[copyConnAttributes]145: key='auth' value='NONE'
dlg_specif[copyConnAttributes]145: key='region' value=''
dlg_specif[copyConnAttributes]145: key='useSSL' value='0'
dlg_specif[copyConnAttributes]145: key='hostnameVerification' value='0'
dlg_specif[copyConnAttributes]145: key='logLevel' value='7'
dlg_specif[copyConnAttributes]145: key='logOutput' value='C:\logs'
dlg_specif[copyConnAttributes]145: key='responseTimeout' value='10'
dlg_specif[copyConnAttributes]145: key='fetchSize' value='-1'
mylog.c[logs_on_off]234: mylog_on=7 qlog_on=7
mylog.c[logs_on_off]234: mylog_on=7 qlog_on=7
start_logging:Global.debug&commlog=7&7
mylog.c[InitializeLogging]533: Log Output Dir: C:\logs
es_communi[ESCommunication::LogMsg]793: Initializing Aws API.
es_communi[ESCommunication::LogMsg]793: Verifying connection options.
es_communi[ESCommunication::LogMsg]793: Required connection option are valid.
es_communi[ESCommunication::LogMsg]793: Starting DB connection.
es_communi[ESCommunication::LogMsg]793: Attempting to establish DB connection.
es_communi[ESCommunication::LogMsg]793: Checking for SQL plugin
es_communi[ESCommunication::LogMsg]793:
es_communi[ESCommunication::LogMsg]793: The SQL plugin must be installed in order to use this driver. Response body: '[]'
es_communi[ESCommunication::LogMsg]793: Failed to establish connection to DB.
es_communi[ESCommunication::LogMsg]793: Dropping DB connection.
connection[CC_set_error_statements]553: entering self=000002D506213FC0
connection[CC_log_error]641: CONN ERROR: func=LIBES_connect, desc='', errnum=202, errmsg='Connection error: [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Connection error: Failed to establish connection to DB.'
connection[CC_log_error]644: ------------------------------------------------------------
connection[CC_log_error]647: henv=000002D5061EF250, conn=000002D506213FC0, status=0, num_stmts=16
connection[CC_log_error]649: esconn=0000000000000000, stmts=000002D5061A5730, lobj_type=-999
es_communi[ESCommunication::LogMsg]793: Shutting down Aws API.
connection[CC_log_error]641: CONN ERROR: func=ESAPI_DriverConnect, desc='Error from CC_Connect', errnum=202, errmsg='Connection error: [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Connection error: Failed to establish connection to DB.'
connection[CC_log_error]644: ------------------------------------------------------------
connection[CC_log_error]647: henv=000002D5061EF250, conn=000002D506213FC0, status=0, num_stmts=16
connection[CC_log_error]649: esconn=0000000000000000, stmts=000002D5061A5730, lobj_type=-999
odbcapi30w[SQLGetDiagRecW]197: entering
es_api30.c[ESAPI_GetDiagRec]39: entering type=2 rec=1
environ.c[ESAPI_ConnectError]228: entering hdbc=000002D506213FC0 <512>
connection[CC_get_error]618: entering
connection[CC_get_error]630: leaving
environ.c[ESAPI_ConnectError]245: CC_get_error: status = 202, msg = #Connection error: [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Connection error: Failed to establish connection to DB.#
environ.c[ESAPI_ConnectError]332: szSqlState = '08001',len=134, szError='Connection error: [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Connection error: Failed to establish connection to DB.'
es_api30.c[ESAPI_GetDiagRec]60: leaving 0
win_unicod[utf8_to_ucs2_lf]244: ilen=134 bufcount=512 string=Connection error: [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Connection error: Failed to establish connection to DB. ocount=134
odbcapi30.[SQLFreeHandle]219: entering
connection[ESAPI_FreeConnect]207: entering...hdbc=000002D506213FC0
connection[CC_Destructor]314: entering self=000002D506213FC0
connection[CC_cleanup]413: entering self=000002D506213FC0
connection[CC_set_error_statements]553: entering self=000002D506213FC0
connection[CC_log_error]641: CONN ERROR: func=CC_cleanup, desc='', errnum=1, errmsg='Connection not open'
connection[CC_log_error]644: ------------------------------------------------------------
connection[CC_log_error]647: henv=000002D5061EF250, conn=000002D506213FC0, status=0, num_stmts=16
connection[CC_log_error]649: esconn=0000000000000000, stmts=000002D5061A5730, lobj_type=-999
connection[CC_cleanup]428: after LIBES_disconnect
dlg_specif[CC_conninfo_init]452: entering opt=1
connection[CC_cleanup]494: leaving
connection[CC_Destructor]321: after CC_Cleanup
connection[CC_Destructor]332: after free statement holders
connection[CC_Destructor]343: leaving
connection[ESAPI_FreeConnect]225: leaving...
odbcapi30.[SQLFreeHandle]219: entering
environ.c[ESAPI_FreeEnv]69: entering env=000002D5061EF250
environ.c[EN_Destructor]442: entering self=000002D5061EF250
environ.c[EN_Destructor]465: clearing conns count=128
environ.c[EN_Destructor]477: leaving rv=1
environ.c[ESAPI_FreeEnv]72: ok

Below is the screenshot

[Prabhu292]

Hi Rupal,

I tried Downgrading the ODFC SQL driver to 1.7

But this time I am getting the different error.

Please help me to fix either this issue or my previous error.