ODBC Connection is not Working for MS Excel

Open Source Elasticsearch and Kibana
1

Hello Guys

I have Opendistro 1.13.2 Cluster I am trying to Connect ODBC connection for MS excel
Data Visualizations and analysis but I m not getting connect to my server and I am using this server in production and SSL is enable I don’t want to disable please follow the attach error and try to help me

Driver Installed
Driver Installed2500×3333 54.2 KB

Configuration
Configuration2500×3333 108 KB

Error
Error2500×3333 60.5 KB

Getting logs

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:
es_odbc.c[DllMain]111: exe name=odbcad32
dlg_specif[CC_conninfo_init]452: entering opt=2
setup.c[ParseAttributes]583: 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]583: aszKey=‘DSN’, value=‘ODFE SQL ODBC DSN’
dlg_specif[copyConnAttributes]145: key=‘DSN’ value=‘ODFE SQL ODBC DSN’
odbcapi30.[SQLAllocHandle]33: entering
environ.c[ESAPI_AllocEnv]44: entering
environ.c[ESAPI_AllocEnv]60: leaving phenv=00000216014905B0
odbcapi30.[SQLSetEnvAttr]431: entering att=200,3
odbcapi30.[SQLAllocHandle]33: entering
connection[ESAPI_AllocConnect]63: entering…
connection[ESAPI_AllocConnect]66: **** henv = 00000216014905B0, conn = 00000216014B2EF0
environ.c[EN_add_connection]502: entering self = 00000216014905B0, conn = 00000216014B2EF0
environ.c[EN_add_connection]531: added at 0, conn->henv = 00000216014905B0, conns[0]->henv = 00000216014905B0
odbcapiw.c[SQLDriverConnectW]122: entering
win_unicod[ucs2_to_utf8]126: 000000FBDB677800 ilen=-3 newlen=203 misc.c[make_string]115: malloc size=203
misc.c[make_string]117: str=00000216014887C0
dlg_specif[CC_conninfo_init]452: entering opt=2
drvconn.c[dconn_get_attributes]223: our_connect_string = ‘DRIVER=elasticsearchodbc;server=10.20.3.64;database=elasticsearch;port=9200;UID=admin;PWD=xxxxx;auth=NONE;region=;useSSL=0;hostnameVerification=0;logLevel=7;logOutput=C:;responseTimeout=10;fetchSize=-1;’
dlg_specif[getDSNinfo]224: drivername=elasticsearchodbc
drvconn.c[dconn_get_attributes]223: our_connect_string = ‘DRIVER=elasticsearchodbc;server=10.20.3.64;database=elasticsearch;port=9200;UID=admin;PWD=xxxxx;auth=NONE;region=;useSSL=0;hostnameVerification=0;logLevel=7;logOutput=C:;responseTimeout=10;fetchSize=-1;’
dlg_specif[copyConnAttributes]145: key=‘DRIVER’ value=‘elasticsearchodbc’
dlg_specif[copyConnAttributes]145: key=‘server’ value=‘10.20.3.64’
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=‘admin’
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:'
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:
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: Client error: ‘Encountered network error when sending http request’.
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=00000216014B2EF0
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=00000216014905B0, conn=00000216014B2EF0, status=0, num_stmts=16
connection[CC_log_error]649: esconn=0000000000000000, stmts=00000216014860E0, 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=00000216014905B0, conn=00000216014B2EF0, status=0, num_stmts=16
connection[CC_log_error]649: esconn=0000000000000000, stmts=00000216014860E0, 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=00000216014B2EF0 <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]243: ilen=134 bufcount=512 ocount=134
odbcapi30.[SQLFreeHandle]219: entering
connection[ESAPI_FreeConnect]207: entering…hdbc=00000216014B2EF0
connection[CC_Destructor]314: entering self=00000216014B2EF0
connection[CC_cleanup]413: entering self=00000216014B2EF0
connection[CC_set_error_statements]553: entering self=00000216014B2EF0
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=00000216014905B0, conn=00000216014B2EF0, status=0, num_stmts=16
connection[CC_log_error]649: esconn=0000000000000000, stmts=00000216014860E0, 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=00000216014905B0
environ.c[EN_Destructor]442: entering self=00000216014905B0
environ.c[EN_Destructor]465: clearing conns count=128
environ.c[EN_Destructor]477: leaving rv=1
environ.c[ESAPI_FreeEnv]72: ok
odbcapi30.[SQLAllocHandle]33: entering
environ.c[ESAPI_AllocEnv]44: entering
environ.c[ESAPI_AllocEnv]60: leaving phenv=00000216014EC220
odbcapi30.[SQLSetEnvAttr]431: entering att=200,3
odbcapi30.[SQLAllocHandle]33: entering
connection[ESAPI_AllocConnect]63: entering…
connection[ESAPI_AllocConnect]66: **** henv = 00000216014EC220, conn = 0000021601516740
environ.c[EN_add_connection]502: entering self = 00000216014EC220, conn = 0000021601516740
environ.c[EN_add_connection]531: added at 0, conn->henv = 00000216014EC220, conns[0]->henv = 00000216014EC220
odbcapiw.c[SQLDriverConnectW]122: entering
win_unicod[ucs2_to_utf8]126: 000000FBDB677800 ilen=-3 newlen=203 misc.c[make_string]115: malloc size=203
misc.c[make_string]117: str=00000216014C55B0
dlg_specif[CC_conninfo_init]452: entering opt=2
drvconn.c[dconn_get_attributes]223: our_connect_string = ‘DRIVER=elasticsearchodbc;server=10.20.3.64;database=elasticsearch;port=9200;UID=admin;PWD=xxxxx;auth=NONE;region=;useSSL=1;hostnameVerification=0;logLevel=7;logOutput=C:;responseTimeout=10;fetchSize=-1;’
dlg_specif[getDSNinfo]224: drivername=elasticsearchodbc
drvconn.c[dconn_get_attributes]223: our_connect_string = ‘DRIVER=elasticsearchodbc;server=10.20.3.64;database=elasticsearch;port=9200;UID=admin;PWD=xxxxx;auth=NONE;region=;useSSL=1;hostnameVerification=0;logLevel=7;logOutput=C:;responseTimeout=10;fetchSize=-1;’
dlg_specif[copyConnAttributes]145: key=‘DRIVER’ value=‘elasticsearchodbc’
dlg_specif[copyConnAttributes]145: key=‘server’ value=‘10.20.3.64’
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=‘admin’
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=‘1’
dlg_specif[copyConnAttributes]145: key=‘hostnameVerification’ value=‘0’
dlg_specif[copyConnAttributes]145: key=‘logLevel’ value=‘7’
dlg_specif[copyConnAttributes]145: key=‘logOutput’ value=‘C:'
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:
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: Client error: ‘Encountered network error when sending http request’.
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=0000021601516740
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=00000216014EC220, conn=0000021601516740, status=0, num_stmts=16
connection[CC_log_error]649: esconn=0000000000000000, stmts=00000216014869E0, 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=00000216014EC220, conn=0000021601516740, status=0, num_stmts=16
connection[CC_log_error]649: esconn=0000000000000000, stmts=00000216014869E0, 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=0000021601516740 <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]243: ilen=134 bufcount=512 ocount=134
odbcapi30.[SQLFreeHandle]219: entering
connection[ESAPI_FreeConnect]207: entering…hdbc=0000021601516740
connection[CC_Destructor]314: entering self=0000021601516740
connection[CC_cleanup]413: entering self=0000021601516740
connection[CC_set_error_statements]553: entering self=0000021601516740
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=00000216014EC220, conn=0000021601516740, status=0, num_stmts=16
connection[CC_log_error]649: esconn=0000000000000000, stmts=00000216014869E0, 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=00000216014EC220
environ.c[EN_Destructor]442: entering self=00000216014EC220
environ.c[EN_Destructor]465: clearing conns count=128
environ.c[EN_Destructor]477: leaving rv=1
environ.c[ESAPI_FreeEnv]72: ok
es_odbc.c[DllMain]118: DETACHING odfesqlodbc.dll

2

es_communi[ESCommunication::LogMsg]793: Client error: ‘Encountered network error when sending http request’.

1 - can you confirm the two machines are able to ping one another
2 - does this document help: sql/microsoft_excel_support.md at develop · opendistro-for-elasticsearch/sql · GitHub

3

Hello Sir

Thank you for Reply

Both Machine ping to each other and all basic configuration is done and I receive logs in ssl dumps
but whenever i am trying to connect it is showing connecting error .

4

Hey can you do this from your windows machine just to double check the output?

curl -XGET https://10.20.3.64:9200 -u 'admin:admin' --insecure

The 2 things here that concern me are:

  1. es_communi[ESCommunication::LogMsg]793: Client error: ‘Encountered network error when sending http request’.
  2. dlg_specif[copyConnAttributes]145: key=‘database’ value=‘elasticsearch’ not found

The first thing here could be leading to the second.

1 Like
5

Hi please check the reply and ,I was already did all the basic things and no sccsess

image