Elasticsearch ODBC driver with SQL Linked Server

Hello,

I am having trouble getting the ODBC driver to work with MS SQL Linked Server. I have added a System DSN under the ODBC Control Panel with the Elastic 7.6 ODBC driver connecting to an active trial of Elastic 7.6 (also tried previous release with same result). I then used the System DSN to create a Linked server. When I open the linked server I do not see the tables that represent my indexes. I have been successful using the same driver with Excel and also using the JDBC driver with DB Visualizer with the same Elastic trial. Below is what I see in MS SQL Management Studio:
I am running Windows Server 2012 R2 and SQL Server 2016. I have also had the same result with Windows 10 and SQL Server 2019

Screen Shot 2020-02-26 at 10.51.21 PM

Here are my config screens in ODBC and Linked Server

These are the logs produced when after I test the connection and refresh the view and click around inside of the linked server:

// Wed Feb 26 22:48:39 2020 - [WARN] getinfo_data_source()@info.c:404 [DBC@0x0000009287D78550] transactions not supported.
Wed Feb 26 22:48:39 2020 - [WARN] getinfo_driver()@info.c:271 [DBC@0x0000009287D78550] no static cursor support.
Wed Feb 26 22:48:39 2020 - [WARN] getinfo_driver()@info.c:271 [DBC@0x0000009287D78550] no static cursor support.
Wed Feb 26 22:48:39 2020 - [ERROR] EsSQLSetConnectAttrW()@connect.c:3248 [DBC@0x0000009287D78550] unknown Attribute: 1.
Wed Feb 26 22:48:39 2020 - [ERROR] EsSQLSetConnectAttrW()@connect.c:3248 [DBC@0x0000009287D78550] unknown Attribute: 0.
Wed Feb 26 22:48:39 2020 - [WARN] getinfo_data_source()@info.c:365 [DBC@0x0000009287D78550] no support for transactions available.
Wed Feb 26 22:48:56 2020 - [ERROR] EsSQLSetConnectAttrW()@connect.c:3225 [DBC@0x0000009287D78550] setting catalog name not supported.

Hi @hwtearsdigi,
Thanks for the detailed report.
The first two errors suggest a deprecated 2.x API behaviour - and the driver should log a warning - but they might not be fatal to the application.
As the last error indicates, catalog support is currently inexistent in our SQL implementation, but we can - and actually should - simulate the support of that API call in one single case. To be sure thought: could you paste the log INFO message proceeding the ERROR one? It should indicate the value the application tries to set the catalog to and should start with setting current catalog to:.
Thanks.

Here is the whole log now with INFO log level turned on in case there is more useful info

Thu Feb 27 16:23:49 2020 - [INFO] filelog_new()@log.c:269 driver version: 7.6.0(v7.2.0-37-g9d9eb98+,u,r).

Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1184 [DBC@0x0000009287D46F20] connection security level: 1.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1195 [DBC@0x0000009287D46F20] CA path: ``.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1225 [DBC@0x0000009287D46F20] connection SQL cusor closing URL: https://1fbec0430cfc49768fccebe620804231.us-east-1.aws.found.io:9243/_sql/close.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1236 [DBC@0x0000009287D46F20] connection SQL query URL: https://1fbec0430cfc49768fccebe620804231.us-east-1.aws.found.io:9243/_sql.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1261 [DBC@0x0000009287D46F20] connection root URL: https://1fbec0430cfc49768fccebe620804231.us-east-1.aws.found.io:9243/.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1287 [DBC@0x0000009287D46F20] follow: true.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1305 [DBC@0x0000009287D46F20] timeout: 0.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1330 [DBC@0x0000009287D46F20] max body size: 104857600.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1363 [DBC@0x0000009287D46F20] fetch_size: 1000.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1380 [DBC@0x0000009287D46F20] pack JSON: true.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1392 [DBC@0x0000009287D46F20] compression: 2 (auto).
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1396 [DBC@0x0000009287D46F20] apply TZ: false.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1439 [DBC@0x0000009287D46F20] multifield lenient: true.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1443 [DBC@0x0000009287D46F20] index include frozen: false.
Thu Feb 27 16:23:49 2020 - [INFO] config_dbc()@connect.c:1446 [DBC@0x0000009287D46F20] auto escape PVA: true.
Thu Feb 27 16:23:50 2020 - [INFO] dbc_curl_perform()@connect.c:681 [DBC@0x0000009287D46F20] libcurl: request answered, received code 200 and 563 bytes of type 'application/json; charset=UTF-8' back; times(ms): start: 110.000, total: 110.000).
Thu Feb 27 16:23:50 2020 - [INFO] check_server_version()@connect.c:1748 [DBC@0x0000009287D46F20] server and driver versions aligned to: 7.6.0.
Thu Feb 27 16:23:50 2020 - [INFO] attach_sql()@queries.c:1085 [STMT@0x0000009297975750] attaching SQL [11] SYS TYPES 0.
Thu Feb 27 16:23:50 2020 - [INFO] dbc_curl_perform()@connect.c:681 [DBC@0x0000009287D46F20] libcurl: request answered, received code 200 and 2221 bytes of type 'application/cbor' back; times(ms): start: 31.000, total: 31.000).
Thu Feb 27 16:23:50 2020 - [INFO] attach_columns_cbor()@queries.c:608 [STMT@0x0000009297975750] columns received: 19.
Thu Feb 27 16:23:50 2020 - [INFO] clear_resultset()@queries.c:156 [STMT@0x0000009297975750] clearing result set #1, visited rows in set: 36.
Thu Feb 27 16:23:50 2020 - [INFO] EsSQLGetDiagRecW()@info.c:1140 [DBC@0x0000009287D46F20] no diagnostic record available for handle type 2.
Thu Feb 27 16:23:50 2020 - [INFO] write_wstr()@util.c:546 [DBC@0x0000009287D46F20] NULL required-space-buffer provided.
Thu Feb 27 16:23:50 2020 - [WARN] getinfo_data_source()@info.c:404 [DBC@0x0000009287D46F20] transactions not supported.
Thu Feb 27 16:23:50 2020 - [WARN] getinfo_driver()@info.c:271 [DBC@0x0000009287D46F20] no static cursor support.
Thu Feb 27 16:23:50 2020 - [WARN] getinfo_driver()@info.c:271 [DBC@0x0000009287D46F20] no static cursor support.
Thu Feb 27 16:23:50 2020 - [INFO] write_wstr()@util.c:546 [DBC@0x0000009287D46F20] NULL required-space-buffer provided.
Thu Feb 27 16:23:50 2020 - [INFO] attach_sql()@queries.c:1085 [STMT@0x0000009297975750] attaching SQL [17] SELECT database().
Thu Feb 27 16:23:50 2020 - [INFO] dbc_curl_perform()@connect.c:681 [DBC@0x0000009287D46F20] libcurl: request answered, received code 200 and 102 bytes of type 'application/cbor' back; times(ms): start: 31.000, total: 31.000).
Thu Feb 27 16:23:50 2020 - [INFO] attach_columns_cbor()@queries.c:608 [STMT@0x0000009297975750] columns received: 1.
Thu Feb 27 16:23:50 2020 - [INFO] write_wstr()@util.c:575 [DBC@0x0000009287D46F20] NULL out buff.
Thu Feb 27 16:23:50 2020 - [INFO] clear_resultset()@queries.c:156 [STMT@0x0000009297975750] clearing result set #1, visited rows in set: 1.
Thu Feb 27 16:23:50 2020 - [ERROR] EsSQLSetConnectAttrW()@connect.c:3248 [DBC@0x0000009287D46F20] unknown Attribute: 1.
Thu Feb 27 16:23:50 2020 - [ERROR] EsSQLSetConnectAttrW()@connect.c:3248 [DBC@0x0000009287D46F20] unknown Attribute: 0.
Thu Feb 27 16:23:50 2020 - [INFO] write_wstr()@util.c:546 [DBC@0x0000009287D46F20] NULL required-space-buffer provided.
Thu Feb 27 16:23:50 2020 - [INFO] write_wstr()@util.c:546 [DBC@0x0000009287D46F20] NULL required-space-buffer provided.
Thu Feb 27 16:23:50 2020 - [INFO] write_wstr()@util.c:546 [DBC@0x0000009287D46F20] NULL required-space-buffer provided.
Thu Feb 27 16:23:50 2020 - [INFO] write_wstr()@util.c:546 [DBC@0x0000009287D46F20] NULL required-space-buffer provided.
Thu Feb 27 16:23:50 2020 - [INFO] attach_sql()@queries.c:1085 [STMT@0x0000009297975750] attaching SQL [17] SELECT database().
Thu Feb 27 16:23:50 2020 - [INFO] dbc_curl_perform()@connect.c:681 [DBC@0x0000009287D46F20] libcurl: request answered, received code 200 and 102 bytes of type 'application/cbor' back; times(ms): start: 31.000, total: 31.000).
Thu Feb 27 16:23:50 2020 - [INFO] attach_columns_cbor()@queries.c:608 [STMT@0x0000009297975750] columns received: 1.
Thu Feb 27 16:23:50 2020 - [INFO] clear_resultset()@queries.c:156 [STMT@0x0000009297975750] clearing result set #1, visited rows in set: 1.
Thu Feb 27 16:23:50 2020 - [WARN] getinfo_data_source()@info.c:365 [DBC@0x0000009287D46F20] no support for transactions available.
Thu Feb 27 16:23:50 2020 - [INFO] attach_sql()@queries.c:1085 [STMT@0x0000009297975750] attaching SQL [57] SYS TABLES CATALOG LIKE '%' ESCAPE '\' LIKE '' ESCAPE '\'.
Thu Feb 27 16:23:50 2020 - [INFO] dbc_curl_perform()@connect.c:681 [DBC@0x0000009287D46F20] libcurl: request answered, received code 200 and 548 bytes of type 'application/cbor' back; times(ms): start: 32.000, total: 32.000).
Thu Feb 27 16:23:50 2020 - [INFO] attach_columns_cbor()@queries.c:608 [STMT@0x0000009297975750] columns received: 10.
Thu Feb 27 16:23:50 2020 - [INFO] EsSQLFetch()@queries.c:1898 [STMT@0x0000009297975750] no data left to return.
Thu Feb 27 16:23:50 2020 - [INFO] clear_resultset()@queries.c:156 [STMT@0x0000009297975750] clearing result set #1, visited rows in set: 1.
Thu Feb 27 16:23:53 2020 - [INFO] EsSQLSetConnectAttrW()@connect.c:3224 [DBC@0x0000009287D46F20] setting current catalog to: 1fbec0430cfc49768fccebe620804231.
Thu Feb 27 16:23:53 2020 - [ERROR] EsSQLSetConnectAttrW()@connect.c:3225 [DBC@0x0000009287D46F20] setting catalog name not supported.

Thanks again for the logs.
Didn't try it against Linked servers yet, but hopefully this PR addresses the issue.

Awesome!
with this PR will there be a new installer created or how would could I reflect this change in the driver I have installed.

Thanks,

The patch will be released part of Elastic's stack release cycle; a new installer will then be made available. A new 7.6 patch release should happen quite soon.

I was able to get 7.6.1 installed that was released recently. However, I am seeing the same errors/warnings. Can you confirm that this change made it into 7.6.1?

It unfortunately is in 7.6.1.
Just to make sure would you mind also enabling the driver manager logging and passing both driver's and driver manager's logs? Thank you.

Driver Manager log

Driver log

Thanks for the logs!

As it turns out, the application fetches all tables (i.e. Elasticsearch indices) from the server, but won't list them because their type is not reported in a way expected by it (TABLE vs. BASE TABLE).
I've logged this into an issue that tracks the effort to better support apps exhibiting older ODBC API recommendations.

Until this gets addressed, one workaround could be defining aliases for those indices that are of interest and then accessing those from the application. And while this might not be ideal in all cases, oftentimes one refers to an alias anyways, such as when working with rolling indices.

I will give that a shot. In addition, is it possible to build the driver ourselves so we don't have to wait for a release?

So creating an alias made a view show up in linked server, but when I try to query it I get this:
select * from [ELASTIC].[a4eb75dedd5c4a05aba113779d7f97b5].[dbo].[michael1];

OLE DB provider "MSDASQL" for linked server "ELASTIC" returned message "The parameter is incorrect.".
OLE DB provider "MSDASQL" for linked server "ELASTIC" returned message "[Elastic][EsODBC 7.6.1(v7.6.0-4-g2459d3f+,u,r) Driver]schema filtering not supported".
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "MSDASQL" for linked server "ELASTIC" reported an error. One or more arguments were reported invalid by the provider.
Msg 7311, Level 16, State 2, Line 3
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "ELASTIC". The provider supports the interface, but returns a failure code when it is used.

Driver Manager Log

ODBC Driver Log

There might be another issue here. I'll try to soon look into it.

There are indeed a couple of issues, but the outstanding one is the incompatibility of Elasticsearch/SQL's string types with Linked Servers/MSSQL's (namely, the lengths), even when using the query pass-through (OPENQUERY).
We've decided to add support for this application, but it requires dedicated work, so it'll unfortunately have to wait for one of the following non-patch releases.
This is btw an issue that Linked Servers/MSSQL has with other DBs/drivers out there (search phrase Requested conversion is not supported.)