Error “insufficient privileges for using connection”

First of all, if you’d like to know how to setup a connection from scratch, please check out this post I wrote on setting up an SFTP connection in Exasol.

However, if you’ve just got the error “insufficient privileges for using connection”, chances are you forgot to grant permissions to some other user, who has tried to use this connection.

GRANT CONNECTION MY_SFTP_CONNECTION to ANOTHER_EXASOL_USER;

This will allow the user to use the connection in conjunction with any other database object they have permissions to use.

HOW TO: Create an SFTP connection in Exasol

So you want to import or export files from an SFTP server? You need a connection.

The code is pretty simple:

ALTER CONNECTION MY_SFTP_CONNECTION
TO 'sftp://sftp.mysftphost.co.uk'
USER 'My_SFTP_User'
IDENTIFIED BY '********';

The user and identified by parts, are the user and password credentials that you would use to log into your SFTP.

However, to use the connection, perhaps from a script, executed by another Exasol user, you need to grant that Exasol user permission to use the connection, that you just created.

GRANT CONNECTION MY_SFTP_CONNECTION to ANOTHER_EXASOL_USER;

HOW TO: Connecting Alteryx to EXASOL

Connecting Alteryx to EXASOL has three parts, downloading the relevant EXASOL driver,  Creating an ODBC Data source for EXASOL and finally configuring that in Alteryx.

Downloading the EXASOL Driver

With the Alteryx free download there is currently no EXASOL driver by default.

Download Download ODBC Driver from the EXASOL website

Install the driver.

Open up the ODBC Data Source Administrator, and go to the Drivers tab. You will see that the EXASolution Driver is now installed.

datasources_admin_drivers

Create an EXASOL Data Source

Still in the ODBC Data Source Administrator, go to the System DSN tab. Select Add. Then, in the EXASolution ODBC configuration window, enter in the details for your EXASOL instance. You can also provide the default schema, or leave it blank to be flexible.exasol_odbc_config

This is what your EXASOL data source should look like.exasol_ds

Configuring EXASOL as a Database Connection in Alteryx

Now we have the EXASOL data source configured we can head to Alteryx. In this case we are going to use an Output with EXASOL.

With your ETL process already setup, drag on Output Data block.

In the Write to File or Database dropdown, select Database Connection, then New ODBC Connection.

alteryx_add_odbc

Next, select the Data Source Name you just created. If it’s not in the dropdown, use ODBC Admin to select it. If you did not specify your credentials in the data source, enter them in here.

alteryx_set_odbc

Alteryx will then ask you to specify a table name. IMPORTANT: list the EXASOL schema first.

alteryx_output_table

Then, your EXASOL destination is ready to go.alteryx_exasol_output