[BUG] OpenSearch ODBC client will not connect to OpenSearch cluster about sql-odbc HOT 37 OPEN

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

from sql-odbc.

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?

from sql-odbc.

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

from sql-odbc.

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.

from sql-odbc.

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.

from sql-odbc.

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.

from sql-odbc.

@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.

from sql-odbc.

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

from sql-odbc.

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?

from sql-odbc.

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

from sql-odbc.

@chloe-zh

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

from sql-odbc.

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

from sql-odbc.

@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!

from sql-odbc.

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

from sql-odbc.

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

from sql-odbc.

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?

from sql-odbc.

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

from sql-odbc.

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?

from sql-odbc.

we are prioritizing this for an immediate fix and release

from sql-odbc.

Fix in opensearch-project/sql#449

from sql-odbc.

@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?

from sql-odbc.

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

from sql-odbc.

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.

from sql-odbc.

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

from sql-odbc.

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

from sql-odbc.

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 .

from sql-odbc.

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.

from sql-odbc.

@forestmvey
@dharminfadia

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

from sql-odbc.

@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

from sql-odbc.

@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.

from sql-odbc.

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:

from sql-odbc.

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).

from sql-odbc.

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

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

from sql-odbc.

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?

from sql-odbc.

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

from sql-odbc.

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

from sql-odbc.

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?

from sql-odbc.