Unable to connect via ODBC

1

I have installed the Apache Arrow Flight SQL ODBC driver.
In the ODBC manager I entered the IP address of the server and used the proper credentials. All other settings were left as default.

I have added the following to my dremio.conf file:
flight: {
enabled: true,
port: 32010,
auth.mode: “legacy.arrow.flight.auth”
} and restarted Dremio.

I am getting to following error:

Flight returned unavailable error, with message: failed to connect to all addresses. Please ensure your encryption settings match the server.

We are using Community Edition, Build: 21.2.0-202205262146080444-038d6d1b

2

Have you tried with version 22?

https://docs.dremio.com/software/drivers/arrow-flight-sql-odbc-driver/
Starting with Dremio v22.0, you can use the ODBC driver for Arrow Flight SQL to connect to Dremio from ODBC client applications.

Download the open source ODBC Driver for Apache Arrow Flight SQL to connect to Dremio version 22.0+ or any other database that exposes an Arrow Flight SQL endpoint.

3

So I upgraded to version 22 from version 21 and ran dremio-admin upgrade. I get the following output:
KVStore version is 22.0.0-202206221430090603-1fa4049f

Upgrade Tasks Status before upgrade

Task: ID (0f5c698a-55c9-45e7-84fe-c5f737e30432), Name (DeleteSysMaterializationsMetadata)
Status: COMPLETED StartTime : 1654801051756(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051802(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (149b8d09-9099-4eba-8902-0edf103a441c), Name (DeleteHistoryOfRenamedDatasets)
Status: OUTDATED StartTime : 1654801051754(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051754(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (17bbcdda-68a1-40ee-8451-4db5958f5317), Name (SetExportType)
Status: COMPLETED StartTime : 1654801051806(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051807(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (1a9ab6a1-c919-4c23-b6af-2119aa0a00b4), Name (MigrateIcebergMetadataPointer)
Status: COMPLETED StartTime : 1654801051809(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051892(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (40dcb921-8f34-48f4-a686-0c77fd3006d6), Name (MigrateToNessieAdapter)
Status: COMPLETED StartTime : 1654801051893(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051959(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (5522c3bc-195f-41ba-8bfd-a33f91b1219a), Name (ReIndexAllStores)
Status: OUTDATED StartTime : 1654801051728(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051728(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (67780ff3-80e1-4d40-88f1-cb7139b5c3de), Name (SetTableauDefaults)
Status: COMPLETED StartTime : 1654801051803(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051806(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (7512f256-fb80-4517-9a50-55a126fd93d5), Name (UpdateS3CredentialType)
Status: OUTDATED StartTime : 1654801051803(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051803(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (79312f25-49d6-40e7-8096-7e132e1b64c4), Name (UpdateExternalReflectionHash)
Status: OUTDATED StartTime : 1654801051756(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051756(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (a5d23112-f354-42fe-bdeb-b024d8d5fb1b), Name (DeleteHive121BasedInputSplits)
Status: OUTDATED StartTime : 1654801051755(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051755(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (c33400d9-fa65-47e2-b99a-5c3db12d8f84), Name (MinimizeJobResultsMetadata)
Status: OUTDATED StartTime : 1654801051755(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051755(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (d7cb2438-bc97-4c76-8a7a-ff5493e48e5e), Name (UpdateDatasetSplitIdTask)
Status: OUTDATED StartTime : 1654801051754(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051754(Thu Jun 09 18:57:31 UTC 2022)

Task: ‘Re index all stores’ up to Version; 2.1.0) completed. Skipping

Task: ‘Fix dataset split ids with invalid id’ up to Version; 3.0.0) completed. Skipping

Task: ‘Delete history of renamed datasets’ up to Version; 2.1.0) completed. Skipping

Task: ‘Deleting Hive 1.2.1 based InputSplits’ up to Version; 2.0.10) completed. Skipping

Task: ‘Delete schema stored in arrow footers of job results in KV Store’ up to Version; 2.1.2) completed. Skipping

Task: ‘Update S3 credential type’ up to Version; 2.1.7) completed. Skipping

Task: ‘Update External Reflections’ up to Version; 2.1.0) completed. Skipping

Task: ‘Deleting System table metadata’ completed. Skipping

Task: ‘Set Tableau client defaults’ completed. Skipping

Task: ‘Set Tableau export defaults’ completed. Skipping

Task: ‘Migrate ICEBERG_METADATA_POINTER in Nessie Data to current format’ completed. Skipping

Task: ‘Migrate Nessie Data from KVStore to database adapter’ completed. Skipping

Upgrade Tasks Status after upgrade

Task: ID (0f5c698a-55c9-45e7-84fe-c5f737e30432), Name (DeleteSysMaterializationsMetadata)
Status: COMPLETED StartTime : 1654801051756(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051802(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (149b8d09-9099-4eba-8902-0edf103a441c), Name (DeleteHistoryOfRenamedDatasets)
Status: OUTDATED StartTime : 1654801051754(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051754(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (17bbcdda-68a1-40ee-8451-4db5958f5317), Name (SetExportType)
Status: COMPLETED StartTime : 1654801051806(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051807(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (1a9ab6a1-c919-4c23-b6af-2119aa0a00b4), Name (MigrateIcebergMetadataPointer)
Status: COMPLETED StartTime : 1654801051809(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051892(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (40dcb921-8f34-48f4-a686-0c77fd3006d6), Name (MigrateToNessieAdapter)
Status: COMPLETED StartTime : 1654801051893(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051959(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (5522c3bc-195f-41ba-8bfd-a33f91b1219a), Name (ReIndexAllStores)
Status: OUTDATED StartTime : 1654801051728(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051728(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (67780ff3-80e1-4d40-88f1-cb7139b5c3de), Name (SetTableauDefaults)
Status: COMPLETED StartTime : 1654801051803(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051806(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (7512f256-fb80-4517-9a50-55a126fd93d5), Name (UpdateS3CredentialType)
Status: OUTDATED StartTime : 1654801051803(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051803(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (79312f25-49d6-40e7-8096-7e132e1b64c4), Name (UpdateExternalReflectionHash)
Status: OUTDATED StartTime : 1654801051756(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051756(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (a5d23112-f354-42fe-bdeb-b024d8d5fb1b), Name (DeleteHive121BasedInputSplits)
Status: OUTDATED StartTime : 1654801051755(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051755(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (c33400d9-fa65-47e2-b99a-5c3db12d8f84), Name (MinimizeJobResultsMetadata)
Status: OUTDATED StartTime : 1654801051755(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051755(Thu Jun 09 18:57:31 UTC 2022)
Task: ID (d7cb2438-bc97-4c76-8a7a-ff5493e48e5e), Name (UpdateDatasetSplitIdTask)
Status: OUTDATED StartTime : 1654801051754(Thu Jun 09 18:57:31 UTC 2022) EndTime : 1654801051754(Thu Jun 09 18:57:31 UTC 2022)

and now when I restart dremio it didn’t retain any of the user data or connections or reflections.

I still have the /data folder from version 21 archived. How do I upgrade and keep the previous data?

4

We have upgraded to Community Edition version 22 and are still unable to connect via ODBC with the Arrow Flight driver.

“Flight returned unavailable error, with message: failed to connect to all addresses. Please ensure your encryption settings match the server.”

5

@summersmd , are you trying to connect to the Arrow Flight service (default port 32010). One thing to watch out for is that the driver now assumes you are using encryption by default. To turn it off, set the UseEncryption property to false.

You should not use the legacy auth mode as well.

1 Like
6

I have authentication turned off in the ODBC manager. What do you mean by legacy auth mode?

7

The “auth.mode” option in your dremio.conf. Delete this line or set it to “arrow.flight.auth2”.

1 Like
8

I tried changing the line to “arrow.flight.auth2” and got the same error message. Then I deleted that line and restarted dremio and still got the same error.

9

Do you have any other suggestion?

10

You mentioned that you have authentication turned off in the ODBC manager. Do you mean encryption?
Can you share what’s in your ODBC INI file (or a screenshot of the ODBC manager)/

And what application are you using to connect?

11

This solution helped the issue I was having with ODBC arrowflight connection.
Thanks!