[BUG] OpenSearch ODBC client will not connect to OpenSearch cluster

[dredwilliams]

Describe the bug
OpenSearch ODBC client will not connect to OpenSearch cluster.

Using the 'Test' button in the ODBC Data Sources Configuration dialog, I get the "Connection error: SQL plugin is not available, please install the SQL plugin to use this driver" error message. Using curl with the same parameters in a WSL window on the same system works as expected.

Tracing through the code, this message is generated in the CheckSQLPluginAvailability() method in opensearch_communication.cpp. Based on what I can see in the log on the Windows side (see below), it is being triggered by an exception thrown in the ParseErrorResponse() method -- which probably means it is getting a non-JSON response to parse. The problem is, I can't see what that response is -- I can only sometimes get the OpenSearch logs to show a connection (though communication is confirmed using tcpdump on the server side), much less tell me what it is sending back. The root log level of the node I'm trying to connect to is set to DEBUG ... I've tried to set to TRACE, but couldn't sift any additional useful information out -- but if y'all can tell me what to search for, I'll set it to TRACE again and send the results.

When using curl to test, I can see messages from o.o.s.l.p.RestSqlAction showing the query received, but not when I test the ODBC driver -- which makes me wonder if the driver is sending the appropriate API call at all? Again, I can't see enough in the logs to answer this question.

I see similar issues raised in several different threads in the forums, with the frequent cause being SSL configuration, possible certificate issues, but (as you see below) I've worked through those issues to make sure I've got those bases covered. I installed the cluster's Root CA in the windows trusted authorities keystore, and verified using a browser that the cluster's cert is accepted.

To Reproduce
Steps to reproduce the behavior:

1. Download ODBC driver (see issue Publish download links for ODBC and JDBC drivers sql#244 )
2. Install driver and open ODBC Data Sources (64 bit) tool
3. Enter parameters (see screenshot below) on main screen, selecting 'SSL' from advanced options sub-dialog (hostname validation is not set, but appears to make no difference)
4. Click the 'test' button
5. Error dialog pops up after timeout period (see below)
6. Logs from the Windows side are also below

Expected behavior
The ODBC driver should connect with the OpenSearch cluster

Plugins
These are the plugins currently installed:

opensearch-alerting
opensearch-anomaly-detection
opensearch-asynchronous-search
opensearch-cross-cluster-replication
opensearch-index-management
opensearch-job-scheduler
opensearch-knn
opensearch-notebooks
opensearch-performance-analyzer
opensearch-reports-scheduler
opensearch-security
opensearch-sql

Screenshots
Configuration Dialog:

Error Dialog:

Windows Log:

opensearch[OpenSearchCommunication::LogMsg]832: Verifying connection options.
opensearch[OpenSearchCommunication::LogMsg]832: Required connection option are valid.
opensearch[OpenSearchCommunication::LogMsg]832: Starting DB connection.
opensearch[OpenSearchCommunication::LogMsg]832: Attempting to establish DB connection.
opensearch[OpenSearchCommunication::LogMsg]832: 
opensearch[OpenSearchCommunication::LogMsg]832: Checking for SQL plugin status.
opensearch[OpenSearchCommunication::LogMsg]832: Parsing error response (with schema validation)
opensearch[OpenSearchCommunication::LogMsg]832: Unexpected exception thrown from the server, the SQL plugin is not installed or in unhealthy status.
opensearch[OpenSearchCommunication::LogMsg]832: SQL plugin is not available, please install the SQL plugin to use this driver.
opensearch[OpenSearchCommunication::LogMsg]832: Dropping DB connection.
connection[CC_set_error_statements]564: entering self=0000025EB7BD36E0
connection[CC_log_error]652: CONN ERROR: func=LIBOPENSEARCH_connect, desc='', errnum=202, errmsg='Connection error: [OpenSearch][SQL ODBC Driver][SQL Plugin] Connection error: SQL plugin is not available, please install the SQL plugin to use this driver.'
connection[CC_log_error]655:             ------------------------------------------------------------
connection[CC_log_error]658:             henv=0000025EB7BAD060, conn=0000025EB7BD36E0, status=0, num_stmts=16
connection[CC_log_error]660:             opensearchconn=0000000000000000, stmts=0000025EB7BA2140, lobj_type=-999
connection[CC_log_error]652: CONN ERROR: func=OPENSEARCHAPI_DriverConnect, desc='Error from CC_Connect', errnum=202, errmsg='Connection error: [OpenSearch][SQL ODBC Driver][SQL Plugin] Connection error: SQL plugin is not available, please install the SQL plugin to use this driver.'
connection[CC_log_error]655:             ------------------------------------------------------------
connection[CC_log_error]658:             henv=0000025EB7BAD060, conn=0000025EB7BD36E0, status=0, num_stmts=16
connection[CC_log_error]660:             opensearchconn=0000000000000000, stmts=0000025EB7BA2140, lobj_type=-999
odbcapi30w[SQLGetDiagRecW]208: entering
opensearch[OPENSEARCHAPI_GetDiagRec]50: entering type=2 rec=1
 environ.c[OPENSEARCHAPI_ConnectError]239: entering hdbc=0000025EB7BD36E0 <512>
connection[CC_get_error]629: entering
connection[CC_get_error]641: leaving
 environ.c[OPENSEARCHAPI_ConnectError]256: CC_get_error: status = 202, msg = #Connection error: [OpenSearch][SQL ODBC Driver][SQL Plugin] Connection error: SQL plugin is not available, please install the SQL plugin to use this driver.#
 environ.c[OPENSEARCHAPI_ConnectError]343: 	     szSqlState = '08001',len=156, szError='Connection error: [OpenSearch][SQL ODBC Driver][SQL Plugin] Connection error: SQL plugin is not available, please install the SQL plugin to use this driver.'
opensearch[OPENSEARCHAPI_GetDiagRec]71: leaving 0
win_unicod[utf8_to_ucs2_lf]254: ilen=156 bufcount=512 ocount=156
odbcapi30.[SQLFreeHandle]230: entering
connection[OPENSEARCHAPI_FreeConnect]218: entering...hdbc=0000025EB7BD36E0
connection[CC_Destructor]325: entering self=0000025EB7BD36E0
connection[CC_cleanup]424: entering self=0000025EB7BD36E0
connection[CC_set_error_statements]564: entering self=0000025EB7BD36E0
connection[CC_log_error]652: CONN ERROR: func=CC_cleanup, desc='', errnum=1, errmsg='Connection not open'
connection[CC_log_error]655:             ------------------------------------------------------------
connection[CC_log_error]658:             henv=0000025EB7BAD060, conn=0000025EB7BD36E0, status=0, num_stmts=16
connection[CC_log_error]660:             opensearchconn=0000000000000000, stmts=0000025EB7BA2140, lobj_type=-999
connection[CC_cleanup]439: after LIBOPENSEARCH_disconnect
dlg_specif[CC_conninfo_init]463: entering opt=1
connection[CC_cleanup]505: leaving
connection[CC_Destructor]332: after CC_Cleanup
connection[CC_Destructor]343: after free statement holders
connection[CC_Destructor]354: leaving
connection[OPENSEARCHAPI_FreeConnect]236: leaving...
odbcapi30.[SQLFreeHandle]230: entering
 environ.c[OPENSEARCHAPI_FreeEnv]80: entering env=0000025EB7BAD060
 environ.c[EN_Destructor]453: entering self=0000025EB7BAD060
 environ.c[EN_Destructor]476: clearing conns count=128
 environ.c[EN_Destructor]488: leaving rv=1
 environ.c[OPENSEARCHAPI_FreeEnv]83:    ok

Host/Environment (please complete the following information):
Client - Windows 10 (latest updates)
ODBC Driver - tested with versions 1.1.0.1 and 1.2 - same behavior
Cluster - OpenSearch 1.1.0 (tarball) running on Debian 11 & Fedora 34 hosts (the host parameter was pointed to a Debian host)

Additional context
I'm curious if the 299 Deprecation warning is interfering with the exchange between the ODBC driver and the cluster, but I cannot find a way to shut it off to validate that hypothesis.

[chloe-zh]

We currently are working on the release of ODBC and JDBC drivers for OpenSearch. The release should be working fine with your opensearch cluster. Another workaround is to get the latest installer directly from the source code / GitHub actions cicd workflows, which is not a stable and officially released one though.

[dredwilliams]

@chloe-zh I'm glad it's being worked, but I pulled the installer that was created at 3pm EST yesterday (commit 65f449b) in the "OpenSearch ODBC Driver" workflow -- same result. The config dialog had a version 1.2.0.0. The logs on the Windows side show nothing different.

[chloe-zh]

Could you check the error log from opensearch side, that would be helpful! Thanks!

[dredwilliams]

I turned logging up to TRACE

rootLogger.level = trace

in log42j.properties ... and got these two messages in the cluster.log:

[2021-11-11T17:25:51,995][TRACE][o.o.h.AbstractHttpServerTransport] [poggin] Http channel accepted: Netty4HttpChannel{localAddress=/10.0.0.66:9200, remoteAddress=/10.0.0.7:62739}

[2021-11-11T17:25:52,035][TRACE][o.o.h.AbstractHttpServerTransport] [poggin] Http channel accepted: Netty4HttpChannel{localAddress=/10.0.0.66:9200, remoteAddress=/10.0.0.7:62740}

I can find no other trace of the attempt ... but again, I verified that there was data exchanged between the two systems using tcpdump.

Are there any other logging settings I should adjust, or additional places to look?

[dredwilliams]

UPDATE: I spun up a new cluster for testing purposes, and disabled SSL on the public API (port 9200) -- the ODBC driver connected just fine -- so it is an SSL issue. I turn on SSL and it behaves as described before.

I have loaded (and verified) the CA for the server certificate into the Windows trusted authorities keystore:

• I can pull up the cluster in a browser with no warnings about insecure certificates
• I can use 'curl' in a WSL window on the same system with no warnings

So -- it appears that the ODBC driver SSL routines are not respecting/using the system CA store? Am I missing something?

[adcalsie2]

I'm having the same problems in an open distro cluster. Waiting for solution

[diego2glez]

@chloe-zh

I'm been experiencing this SSL connection problem since to many OpenDistro versions before.
Seems that this never worked?

[dredwilliams]

@chloe-zh - Is there any more information I can provide that will help narrow this down?

[davidcui1225]

We have a release for the SQL Drivers including the ODBC client that is launching this week- that is expected to resolve this bug

[dredwilliams]

@davidcui1225 - if you want me to test, let me know when there is something I can download -- I'd love to get this issue fixed!

[dredwilliams]

@davidcui1225 - How far down the queue is this to work on? I just downloaded the latest artifact and get the same errors.

[joshuali925]

The new ODBC driver links are available on https://opensearch.org/artifacts, you can download it here:

• macos_x64: https://artifacts.opensearch.org/opensearch-clients/odbc/signed_opensearch-sql-odbc-mac-1.1.0.1.zip
• win32: https://artifacts.opensearch.org/opensearch-clients/odbc/signed_opensearch-sql-odbc-win32-1.1.0.1.msi
• win64: https://artifacts.opensearch.org/opensearch-clients/odbc/signed_opensearch-sql-odbc-win64-1.1.0.1.msi

The SSL issue is separate and possibly related to #19

[dredwilliams]

Unfortunately, I'm not seeing any action on resolving either of these issues (this one or #19) -- is there anything I can do to assist?

[anonyiv]

It is a bug related to ssl, you can fix it with the following configuration in opensearch.yml:
plugins.security.ssl.http.clientauth_mode: NONE

[dredwilliams]

Not totally sure what this does, but it seems that it would either disable SSL for the client connection or disable authentication (or both) -- neither of which is acceptable in my environment. Is this truly the only solution?

[anirudha]

we are prioritizing this for an immediate fix and release

[penghuo]

Fix in opensearch-project/sql#449

[dredwilliams]

@penghuo - I don't see anything in opensearch-project/sql#449 that would have impacted this issue. First, I'm not even to the point where I can use Power BI -- and the issue isn't whether I can validate with a certificate, it's whether SSL is even used for the connection.

Am I missing something?

[Yury-Fridlyand]

opensearch-project/sql#449 has a fix for the Power BI connector.
The fix for the driver would be published soon.

[dredwilliams]

What is the expected release date for a new ODBC driver? I still see the 1.1.0.1 version available on the downloads page.

[joshuali925]

linking release issue: opensearch-project/opensearch-build#1872
@mengweieric is working on this release

[joshuali925]

the new drivers (1.4.0.0) are released under bottom of https://opensearch.org/artifacts, let us know if they still have issues

[dharminfadia]

the new drivers (1.4.0.0) are released under bottom of https://opensearch.org/artifacts, let us know if they still have issues

Hello @joshuali925

latest Driver is install still having same issue

I am using Opensearch version is 1.2.4 but still facing issue .

[forestmvey]

I tested odbc driver versions 1.3.0.0 and 1.4.0.0 and both still ignore the UseSSL flag. When setting
opendistro_security.ssl.http.enabled false on my active node I was able to make a valid connection with both versions when the UseSSL flag has been set.

[anonyiv]

@forestmvey
@dharminfadia

works correctly
the endpoint must be https enable the advanced option "Enable SSL"

[acarbonetto]

@atarhel did you have the config opendistro_security.ssl.http.enabled false set on your node? Is there any other custom configuration on your OS service?
I'm curious to know why it works for you

[anonyiv]

@atarhel did you have the config opendistro_security.ssl.http.enabled false set on your node? Is there any other custom configuration on your OS service?

I'm curious to know why it works for you

No, I have also tested it with the AWS opensearch service in the cloud, it works without problems.
can you put more information to help you?
check your opensearch log, the odbc driver allows you to enable debug.

[forestmvey]

Further testing on the ODBC driver version 1.4.0.0 has highlighted some UI issues around the UseSSL flag. When using the prefix https:// a user can connect to a SSL enabled node regardless of the UseSSL flag. If the user does not specify a protocol then the connection will always default to not using SSL. Some sort of error message or UI change should notify a user when the UseSSL flag status does not match a specified host connection protocol. I have outlined the functionality in these truth tables:

Driver connecting to SSL enabled node with UseSSL flag set:

Driver connecting to SSL enabled node with UseSSL flag not set:

[acarbonetto]

Thanks @forestmvey @atarhel seems like a UI issue, not a config or setup issue.
Sounds like we should update the checkbox somehow. I'd propose one of a couple of options:

1. Remove the checkbox completely and expect the protocol to be included in the URL (or default to http://)
2. Disable the checkbox when a protocol is specified in the URL
3. Keep the checkbox, but report an error when the checkbox does not correspond to the URL protocol (error when Enable SSL & http:// is included, or when Not Enable SSL & https:// is included).

[MaxKsyunz]

My preferences is option 2 -- it's the most user-friendly.

While we are there, HostVerification should be disabled unless HTTPS is used.

[joshuali925]

Thanks @forestmvey that's helpful. So basically "UseSSL" is ignored right now, as a workaround user just have to use consistent protocol in host and node set up

I agree with option 2 as well, since UseSSL seems redundant if user already put https

[ChristineChetty11]

Having same issue with current setup:
AWS Managed Opensearch, V1.2

What I want to point out is that I could not use the sql plugin from my lambda function since the path was actually '_opendistro/_sql' not '_plugins/_sql' as it should be.. Maybe this is related somehow?

[acarbonetto]

It should be checking _opendistro/_sql when using opendistro. It falls back to the alternate path when the first one fails.

[forestmvey]

@joshuali925 I have created a demo for the fix on this issue with PR-653

odbc_ssl_compliance_fix_demo.mp4

[lifayt]

I'm having this problem with AWS Managed OpenSearch, both on OpenDistro clusters as well as OpenSearch clusters (7.10 and 1.3 respectively).

Connection error: [OpenSearch][SQL ODBC Driver][SQL Plugin] Connection error: SQL plugin is not available, please install the SQL plugin to use this driver.

The above error shows up regardless of whether or not I configure the connection to use ssl or not. I can access the endpoint via HTTP just fine, but the ODBC connection always fails. Is there a workaround or alternate odbc driver I can use?

[dlang14]

Im having issues while using Amazon Opensearch Service 1.3 and opensearch-sql-odbc-driver 1.5. Ive tried everything from using Domain endpoint (VPC) name, custom url, using ports 443/9200/none, using http/https/none, enabling/disabling SSL, host verification on/off and no matter what I get:
Connection error: [Opensearch][SQL ODBC Driver][SQL Plugin] Connection error: Failed to establish connection to DB
I've proven connection to the opensearch cluster by using the browser on the EC2 with no issues. Any help/input would be much appreciated.

[andreaskulicke]

The solution described above worked for me: using "https://" and UseSSL flag. But only for V1.4.0.0. It didn't work for me with V1.5.0.0.

[apilip]

There is still some issue with version 1.5.0 as described in here:
https://forum.opensearch.org/t/error-when-trying-to-connect-odbc-ssl/18924/2