Publish download links for ODBC and JDBC drivers

[Yury-Fridlyand]

Is your feature request related to a problem? Please describe.
I tried to download ODBC driver and the official website redirects me to this repository. Actually, it contains the source code; the installation package link is still nowhere published.
The same situation with JDBC driver download link.

Describe the solution you'd like
The download links for JDBC and ODBC (x64 and x86) both should be published on the website and on the repo.

Describe alternatives you've considered
It is possible to download installer from GitHub Actions artifacts. This way is extremely unclear and not obvious. It is possible to refer it as a temporary solution only.

Additional context
Once a newer version of drivers would be released, the download links should be updated automatically.

[stockholmux]

Closed by opensearch-project/project-website#635

ODBC/JDBC are now published 🎉

[nicotiendamia]

Hello @stockholmux Kyle, could you please point me to the installation link? I've followed the build instructions and the .msi is nowhere to be found to complete the installation. Please help!!

[forestmvey]

Hello @stockholmux Kyle, could you please point me to the installation link? I've followed the build instructions and the .msi is nowhere to be found to complete the installation. Please help!!

ODBC and JDBC artifacts can be found here: opensearch.org/artifacts

[nicotiendamia]

@forestmvey Kyle thanks for your response! Yeah, i actually navigated some more through the issues and found the link, i downloaded this opensearch-sql-odbc-win64-1.4.0.0.msi.
Now am getting an issue where it says the SQL plugin could not be found and i'm not quite getting how to install it, could you help me with that?

[forestmvey]

@nicotiendamia
What are your DSN configuration settings. Note there is currently a bug that if you need an SSL connection you need to use https:// prefix in your host parameter.

Related: github_issue-280

[nicotiendamia]

@forestmvey
Yes, i saw that issue and indeed we need SSL, so i prefixed like you say. Even doing that i got the issue when testing the connection.
My use case is to connect via Tableau and the ODBC, to AWS. But where can i find this SQL plugin, being that im not using OpenSearch locally i just want to be able to use the ODBC to connect to AWS via Tableau, to generate Vizes. Could you shed some light into this plugin issue? Thanks for your help, its much appreciated!

[forestmvey]

@nicotiendamia
For Tableau you will need to install the .tdc file from the installed ODBC directory. Follow installation instructions for using tdc file in tableau here: odbc_customize
With the tdc file installed you should be able to make a connection to AWS via ODBC driver by going to
To a Server -> More... -> Other Databases(ODBC)
That is how to connect on mac so I would think windows would be similar.

[nicotiendamia]

@forestmvey Yes i did that too, i've been with this issues since Monday, so i followed every doc i found, but still geting this issue about the SQL Plugin. WHere can i find this? Or you would say that isn't really the issue and the error message is confusing me?

[joshuali925]

@nicotiendamia docs are in this directory: https://github.com/opensearch-project/sql/tree/main/sql-odbc/docs/user

ODBC driver says SQL plugin is not available if SQL plugin is not installed, or if connection is not successful (which can be confusing). Are you able to connect to your SQL endpoint using curl?

e.g. curl -k -X POST "https://user:pass@ip:port/_plugins/_sql" -H 'Content-Type: application/json' -d'{"query": "show tables like %"}'

[nicotiendamia]

@joshuali925 sorry for the late response, i've been trying different things. I'm able yes to get a response through curl, so what would be the problem? Thankss!!

[joshuali925]

@nicotiendamia so issue is probably in ODBC config.

1. Are you connecting to an AWS managed endpoint or did you set up OpenSearch on your own?
2. Which version of OpenSearch?
3. How did you authenticate if any? e.g. aws credentials, username password, ..

you can also check logs from odbc driver and opensearch to see what the issue is

[nicotiendamia]

@joshuali925 answering your points:

1. Indeed, i'm connecting to one of my companies endpoints in AWS
2. Checking in AWS i see 1.2 (latest)
3. I didn't authenticate. In the curl that you guys suggested i also didn't authenticate, but there i correctly got results

[joshuali925]

@nicotiendamia have you tried prefixing host with https:// or http://? I tried on my side just now, it works with only Host: https://xxx.com/, do you have any special setup? logs will also help.

And probably not likely but if you see a default profile in <your-home-dir>/.aws/credentials, also copy it to a new profile named opensearchodbc.

[nicotiendamia]

@joshuali925 Yes, i tried with and without prefix. Regarding setup i have a VPN, which allows me to connect to AWS (that allowed the earlier curl to be successful), but other than that no.

In have my logging set to DEBUG, here i attach the most recent one. One thing that i noticed is that it specifies 'OpenSearch' as the value for the ConnAtributes 'database' key and mentions it not to be found. i dont know if this is related, i didn't specify a database in the DSN since there isn't an option.

Here are the logs. PS: i erased my host value, which is the endpoint in AWS and replaced it with the word 'edited':
mylog.c[logs_on_off]245: mylog_on=5 qlog_on=5 start_logging:Global.debug&commlog=5&5 mylog.c[InitializeLogging]544: Log Output Dir: C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs opensearch[DllMain]123: exe name=odbcad32 setup.c[ParseAttributes]594: aszKey='DSN', value='OpenSearch SQL ODBC DSN' dlg_specif[copyConnAttributes]156: key='DSN' value='OpenSearch SQL ODBC DSN' dlg_specif[getDSNinfo]235: drivername=opensearchodbc setup.c[ParseAttributes]594: aszKey='DSN', value='OpenSearch SQL ODBC DSN' dlg_specif[copyConnAttributes]156: key='DSN' value='OpenSearch SQL ODBC DSN' opensearch[DllMain]130: DETACHING sqlodbc.dll mylog.c[logs_on_off]245: mylog_on=5 qlog_on=5 start_logging:Global.debug&commlog=5&5 mylog.c[InitializeLogging]544: Log Output Dir: C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs opensearch[DllMain]123: exe name=odbcad32 setup.c[ParseAttributes]594: aszKey='DSN', value='OpenSearch SQL ODBC DSN' dlg_specif[copyConnAttributes]156: key='DSN' value='OpenSearch SQL ODBC DSN' dlg_specif[getDSNinfo]235: drivername=opensearchodbc setup.c[ParseAttributes]594: aszKey='DSN', value='OpenSearch SQL ODBC DSN' dlg_specif[copyConnAttributes]156: key='DSN' value='OpenSearch SQL ODBC DSN' connection[OPENSEARCHAPI_AllocConnect]77: **** henv = 00000159D5A03F80, conn = 00000159D5A28890 environ.c[EN_add_connection]542: added at 0, conn->henv = 00000159D5A03F80, conns[0]->henv = 00000159D5A03F80 win_unicod[ucs2_to_utf8]137: 000000517FBC9930 ilen=-3 newlen=256 misc.c[make_string]126: malloc size=256 misc.c[make_string]128: str=00000159D5A22980 drvconn.c[dconn_get_attributes]234: our_connect_string = 'DRIVER=opensearchodbc;server=https://edited;database=OpenSearch;port=443;UID=;PWD=;auth=NONE;region=;useSSL=1;hostnameVerification=0;logLevel=5;logOutput=C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs;responseTimeout=100;fetchSize=-1;' dlg_specif[getDSNinfo]235: drivername=opensearchodbc drvconn.c[dconn_get_attributes]234: our_connect_string = 'DRIVER=opensearchodbc;server=https://edited;database=OpenSearch;port=443;UID=;PWD=;auth=NONE;region=;useSSL=1;hostnameVerification=0;logLevel=5;logOutput=C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs;responseTimeout=100;fetchSize=-1;' dlg_specif[copyConnAttributes]156: key='DRIVER' value='opensearchodbc' dlg_specif[copyConnAttributes]156: key='server' value='https://edited' dlg_specif[copyConnAttributes]156: key='database' value='OpenSearch' not found dlg_specif[copyConnAttributes]156: key='port' value='443' dlg_specif[copyConnAttributes]156: key='UID' value='' dlg_specif[copyConnAttributes]132: key='PWD' value='xxxxxxxx' dlg_specif[copyConnAttributes]156: key='auth' value='NONE' dlg_specif[copyConnAttributes]156: key='region' value='' dlg_specif[copyConnAttributes]156: key='useSSL' value='1' dlg_specif[copyConnAttributes]156: key='hostnameVerification' value='0' dlg_specif[copyConnAttributes]156: key='logLevel' value='5' dlg_specif[copyConnAttributes]156: key='logOutput' value='C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs' dlg_specif[copyConnAttributes]156: key='responseTimeout' value='100' dlg_specif[copyConnAttributes]156: key='fetchSize' value='-1' mylog.c[logs_on_off]245: mylog_on=5 qlog_on=5 mylog.c[logs_on_off]245: mylog_on=5 qlog_on=5 start_logging:Global.debug&commlog=5&5 mylog.c[InitializeLogging]544: Log Output Dir: C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs opensearch[OpenSearchCommunication::LogMsg]832: Required connection option are valid. opensearch[OpenSearchCommunication::LogMsg]832: 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. 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=00000159D5A03F80, conn=00000159D5A28890, status=0, num_stmts=16 connection[CC_log_error]660: opensearchconn=0000000000000000, stmts=00000159D59D3770, 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=00000159D5A03F80, conn=00000159D5A28890, status=0, num_stmts=16 connection[CC_log_error]660: opensearchconn=0000000000000000, stmts=00000159D59D3770, lobj_type=-999 environ.c[OPENSEARCHAPI_ConnectError]239: entering hdbc=00000159D5A28890 <512> 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.' win_unicod[utf8_to_ucs2_lf]254: ilen=156 bufcount=512connection[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=00000159D5A03F80, conn=00000159D5A28890, status=0, num_stmts=16 connection[CC_log_error]660: opensearchconn=0000000000000000, stmts=00000159D59D3770, lobj_type=-999 connection[CC_cleanup]439: after LIBOPENSEARCH_disconnect connection[CC_Destructor]332: after CC_Cleanup connection[CC_Destructor]343: after free statement holders environ.c[EN_Destructor]476: clearing conns count=128 environ.c[OPENSEARCHAPI_FreeEnv]83: ok opensearch[DllMain]130: DETACHING sqlodbc.dll mylog.c[logs_on_off]245: mylog_on=5 qlog_on=5 start_logging:Global.debug&commlog=5&5 mylog.c[InitializeLogging]544: Log Output Dir: C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs opensearch[DllMain]123: exe name=odbcad32 setup.c[ParseAttributes]594: aszKey='DSN', value='OpenSearch SQL ODBC DSN' dlg_specif[copyConnAttributes]156: key='DSN' value='OpenSearch SQL ODBC DSN' dlg_specif[getDSNinfo]235: drivername=opensearchodbc setup.c[ParseAttributes]594: aszKey='DSN', value='OpenSearch SQL ODBC DSN' dlg_specif[copyConnAttributes]156: key='DSN' value='OpenSearch SQL ODBC DSN' connection[OPENSEARCHAPI_AllocConnect]77: **** henv = 00000159D5A38590, conn = 00000159D5A1FCB0 environ.c[EN_add_connection]542: added at 0, conn->henv = 00000159D5A38590, conns[0]->henv = 00000159D5A38590 win_unicod[ucs2_to_utf8]137: 000000517FBC9930 ilen=-3 newlen=256 misc.c[make_string]126: malloc size=256 misc.c[make_string]128: str=00000159D5A9B670 drvconn.c[dconn_get_attributes]234: our_connect_string = 'DRIVER=opensearchodbc;server=https://edited;database=OpenSearch;port=443;UID=;PWD=;auth=NONE;region=;useSSL=1;hostnameVerification=0;logLevel=5;logOutput=C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs;responseTimeout=100;fetchSize=-1;' dlg_specif[getDSNinfo]235: drivername=opensearchodbc drvconn.c[dconn_get_attributes]234: our_connect_string = 'DRIVER=opensearchodbc;server=https://edited;database=OpenSearch;port=443;UID=;PWD=;auth=NONE;region=;useSSL=1;hostnameVerification=0;logLevel=5;logOutput=C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs;responseTimeout=100;fetchSize=-1;' dlg_specif[copyConnAttributes]156: key='DRIVER' value='opensearchodbc' dlg_specif[copyConnAttributes]156: key='server' value='edited' dlg_specif[copyConnAttributes]156: key='database' value='OpenSearch' not found dlg_specif[copyConnAttributes]156: key='port' value='443' dlg_specif[copyConnAttributes]156: key='UID' value='' dlg_specif[copyConnAttributes]132: key='PWD' value='xxxxxxxx' dlg_specif[copyConnAttributes]156: key='auth' value='NONE' dlg_specif[copyConnAttributes]156: key='region' value='' dlg_specif[copyConnAttributes]156: key='useSSL' value='1' dlg_specif[copyConnAttributes]156: key='hostnameVerification' value='0' dlg_specif[copyConnAttributes]156: key='logLevel' value='5' dlg_specif[copyConnAttributes]156: key='logOutput' value='C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs' dlg_specif[copyConnAttributes]156: key='responseTimeout' value='100' dlg_specif[copyConnAttributes]156: key='fetchSize' value='-1' mylog.c[logs_on_off]245: mylog_on=5 qlog_on=5 mylog.c[logs_on_off]245: mylog_on=5 qlog_on=5 start_logging:Global.debug&commlog=5&5 mylog.c[InitializeLogging]544: Log Output Dir: C:\Users\mario\Desktop\DSI\OpenSearch\ODBC Logs opensearch[OpenSearchCommunication::LogMsg]832: Required connection option are valid. opensearch[OpenSearchCommunication::LogMsg]832: 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. 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=00000159D5A38590, conn=00000159D5A1FCB0, status=0, num_stmts=16 connection[CC_log_error]660: opensearchconn=0000000000000000, stmts=00000159D5A03CB0, 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=00000159D5A38590, conn=00000159D5A1FCB0, status=0, num_stmts=16 connection[CC_log_error]660: opensearchconn=0000000000000000, stmts=00000159D5A03CB0, lobj_type=-999 environ.c[OPENSEARCHAPI_ConnectError]239: entering hdbc=00000159D5A1FCB0 <512> 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.' win_unicod[utf8_to_ucs2_lf]254: ilen=156 bufcount=512connection[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=00000159D5A38590, conn=00000159D5A1FCB0, status=0, num_stmts=16 connection[CC_log_error]660: opensearchconn=0000000000000000, stmts=00000159D5A03CB0, lobj_type=-999 connection[CC_cleanup]439: after LIBOPENSEARCH_disconnect connection[CC_Destructor]332: after CC_Cleanup connection[CC_Destructor]343: after free statement holders environ.c[EN_Destructor]476: clearing conns count=128 environ.c[OPENSEARCHAPI_FreeEnv]83: ok opensearch[DllMain]130: DETACHING sqlodbc.dll

[joshuali925]

@nicotiendamia the database not found should be ok, I also see it on a successful connection. Is your VPN globally enabled or only some softwares go through it?

https://github.com/opensearch-project/sql/blob/main/sql-odbc/src/sqlodbc/opensearch_communication.cpp#L509 this looks very general.. don't know if there's a way to see what the unknown exception is

[nicotiendamia]

@joshuali925 I know, its exactly what i thought when i went through the code of that file, that the Exception im experiencing is a generic one, for how it's coded there isn't a way to see the specific exception.

The Infrastructure department told me the VPN shouldn't be a problem to use with the driver. Also, this message is quite deciving right? Because with the test @forestmvey suggested i confirmed the driver was installed and working correctly on AWS

This is driving me crazy, i've been having issues with this driver since Monday and it's the only possibility i have to establish the connection i want :(

[MaxKsyunz]

@joshuali925 sorry for the late response, i've been trying different things. I'm able yes to get a response through curl, so what would be the problem? Thankss!!

@nicotiendamia what did you get from the server in this case?

Could you also run curl with -v flag and confirm that the response code is 200?

Since you did get a response from curl and the log indicates that it's in ParseErrorResponse function (Parsing error response (with schema validation) line) my theory is that it's trying to apply the ERROR_RESPONSE_SCHEMA to JSON with a list of tables. That fails with an exception, ParseErrorResponse generates a generic std::exception which then gets swallowed by CheckSQLPluginAvailability.

[MaxKsyunz]

@nicotiendamia a better option might be to use the RC version of the Tableau Connector.

You can find the connector here. This page explains how to use it. The two important bits are -- putting the TACO in the correct directory and starting Tableau without signature verification.

You will need to install the JDBC driver to use it. Instructions are here.

I also found the sql-jdbc readme very helpful in getting the connection string just right.

[nicotiendamia]

@MaxKsyunz Thanks, will try this things out!!

[ChristineChetty11]

@nicotiendamia Are you including Port number? If so, try removing that in your setup only provide the domain endpoint and also be sure to check Hostname validation in advanced settings. UI have been struggling with this for much of today and this worked for me, also make sure you use version 1.4.0 of the driver