Guide to setting up In-DB Connection Files on a Network Drive

[SophiaF]

Guide to setting up In-DB Connection Files on a Network Drive

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: See Supported 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 that may 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.

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.