Alteryx Designer Knowledge Base

Guide to In-DB Connection Files Introduction In-DB connection files can be used as an alternative way for users to connect to a database. These files are stored as .indbc files and can, in many cases, be the preferred route for connecting to a database. This guide will walk through reasons for setting up a file connection, suggestions on storing the files on a network location, and steps to set this up. There are many reasons to use an In-DB connection file: Centralize data connection(s) in one location so that they can be used across multiple workflows and/or multiple users Have a single location where data connections can be modified, or credentials updated Allow access to data connections from multiple systems or server nodes Avoid having to set up ODBC DSNs on multiple Designer machines or server nodes Avoid Designer users needing to know credential information Things to know before you start Nothing prevents users who have access to file share location where File-based data connections are stored from downloading and sharing connection files with others. The passwords will not be visible because they are encrypted, but the connections will work on other Alteryx machines. Permissions will need to be controlled via the standard Windows permissions. These will only work with data sources that support In-DB through Alteryx: SeeSupported Data Sources and File Formats How to create the In-DB file connection(s) on a file share Whoever is going to create the data connection file will need to be on a machine that has Alteryx Designer installed. 1. Install the data source driver (if it is not already installed). You will eventually need to install the driver on all the machines thatmay connect to this data source. See the above Supported Data Sources link to see tested drivers and versions. 2. Create a DSN-less data connection. DSN-less connections provide ultimate portability as the connection string specifies the driver, host and authentication parameters and avoids needing to set up an ODBC DSN on all machines that will be connecting. Example connection formats (notice the driver is part of the connection string): odbc:DRIVER={SQL Server Native Client 11.0};UID=xxx;PWD=xxx;DATABASE=MyDatabase;SERVER=myserver.somedomain.com; odbc:DRIVER={Simba Hive ODBC Driver};Host=myserver.somedomain.com;Port=10000;UID=hdfs odbc:DRIVER={SnowflakeDSIIDriver};Server=myserver.snowflakecomputing.com;Warehouse=mywarehouse;Database=mydatabase;Schema=PUBLIC;Role=;Uid=xxx;Pwd=xxx; *For more information on connection strings, please see ConnectionStrings.com or contact your DBA who can get this for you. 3.Create the connection in Designer. Go to Options > Advanced Options > Manage In-DB Connections 1. Choose your Data Source type 2. Choose Connection Type to be “File” 3. Browse to the file share and name the file. It should be saved to a fully-qualified UNC path i.e.: \\fileshare.domain.com\fileconnections\oracle.indbc 4. Set Password Encryption to “Hide” 5. Paste your DSN-less connection string in the Connection String area for Read. 6. Paste your DSN-less connection string in the Connection String area for Write. (Note that Read and Write need to be both specified and can be the same string, or different if need be) 7. Click Ok to save the connection file. 4. Build a workflow that uses In-DB tools to connect to your data source using the saved file to test the connection 5. Set the file or directory permissions for the connection file so they are restricted to only allow read access to users that should have access. This would include the Run-As user (if specified in the Alteryx System Settings) or users utilizing Workflow Credentials.More info on this can be found in the following articles: How Workflow Credentials Work on a Private Gallery Set Required Run As User Permissions 6. Publish the workflow to the server.Make sure the In-DB connection file is not included as an asset. Note: Credit for this article goes to the Alteryx Solutions Architects team!

Error - There were errors with LOADING please check your ERROR TABLES, when Output to Teradata with French Character data.

While trying to connect to Snowflake from Alteryx Designer, using Input Data tool, the following error is thrown after running the workflow: Error: Input Data (1): Error SQLExecute: No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

Error: "Failed to connect to okta. Error code=401" when connecting to Snowflake using Okta SSO and MFA.

Numeric values show "[Null]" instead of a blank cell using the Input Data tool.

Unable to find the dll: "OCI.dll" Within Alteryx this error will be prompted due to a mismatch between 32/64 bit drivers and the Alteryx bit version. This error may also appear if you do not have the drivers installed. One way to troubleshoot this is to find out what drivers you have installed! You can use an input tool in the Alteryx Designer, go to Other Databases and select either ODBC/OleDB (64-bit) or 32-Bit Database Connections -> ODBC/OleDB. In the next pop out window it will populate with the available drivers for those connections. If you have no drivers in this pop out window this means you do not havethe correct drivers for 32 bit or 64 bit connection dependent upon which one you chose. Links to drivers are available from within individual data source pages here. This will take you away from the Alteryx website and onto the driver provider’s pages. Please consult with your IT to find the correct drivers for your environment.

Workflow processing can take longer when a large amount of data is streamed in and out of a database. In-database processing can be used to speed up a workflow.

Error: "SQL30082N Security processing failed with reason "15" ('PROCESSING FAILURE')" when connecting to DB2 using the Input Data tool.

There is a comprehensive list of Data Sources Alteryx Designer / Server can connect to. While only these are supported you still might be able to build your own connector. Additionally a product feature can be requested.

How To: Create an In-Database Connection

Importing Thai data from Oracle Database using Oracle ODBC driver is displaying the Thai characters as garbled characters in Alteryx. This can be addressed by setting a parameter in the driver to unconditionally change the data type to support Unicode.

Error: "ORA-21500: internal error code, arguments: [%s], [%s], [%s]" when pulling Spatial data from Oracle using Input data tool.

Error: "HTTP Error 403: Forbidden", when using Output Data tool to Hive HDFS in an encrypted zone.

User is attempting to connect to Hadoop Hive with Active Directory Kerberos (Kerberos SSPI) in Alteryx and getting an error: Failed to get username: Routine Error: Unspecified GSS failure. Minor code may provide more information. The test button in the ODBC DSN is successful.

Where do you start when you see "An Unhandled Exception occurred"?

This article contains a list of common causes for being unable to run a workflow on a Gallery and solutions for each cause.

Error: "You have found a bug. Replicate, then let us know. We shall fix it soon." when pulling data from Snowflake.

The Run Command tool can be used to connect to SQL Server and run complex SQL statements as well as multiple stored procedures.

Connecting to an Oracle APPS schema causes Designer to get stuck in "Not Responding".

This is a workaround for connecting to an Oracle database that uses LDAP authentication.

Error: "CURLerror (curl_easy_perform() failed) - code=35 msg='SSL connect error'." when connecting to Snowflake.