Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Knowledge Base

Definitive answers from Server experts.

Managing Data Connections Between Server/Scheduler and Local Machine

JessicaS
Alteryx Alumni (Retired)
Created

In developing workflows our users need to push workflows to server or scheduler that contain database connections. This is a common source of errors if not configured properly.

Sample Errors:

  • Unable to Translate Alias
  • Data source name not found and no default driver specified
  • Permissions Errors

In order to set up a database connection lets review how the scheduler and server work.

Which user will the workflow run under when I publish or schedule it and how do I set up my connections to accommodatethis?

When you run a workflow locally in Alteryx Designer it runs as the user who is currently logged in. When you the schedule or push that workflow to the server it will run as the local system account (not logged in user). If you set up your database to use windows authentication you could get errors since the workflow will essentiallyrun as a different user on the server and thus not be able to authenticate.

To combat this you can ask your DBA to permission the local system account for the database or you can set a Run As user in the system settings of Alteryx. Setting a Run As User will allow the workflow to run under different credentials. Note that the run as credentials are an all or nothing solution and when set all workflows will run as the specified user. Read more about it here andhere. With newer versions of Alteryx Server, you can also have the server admin check a setting to allow users to set the run as on a per workflow basis when running on gallery. Read more about that optionhere.

How do I set up my drivers?

In order to connect to a database in Alteryx you need to have a supported driver installed. To find the supported driver for your particular database visit our tech specs pageand click on your database. Note you should also have a supported version of the database.

The driver needs to be installed on your local machine as well as the server and it needs to be the same driver. For example, if you connect to SQL Server using SQL Server Native Client 11 on your local machine, the Server must also have SQL Server Native Client 11. Note also that if your Server has multiple workers each must have the driver.

How do I set up my Connections?

DSN Connections

There are two ways of setting up a connection string in Alteryx. The most common and simple way is to create one that references a DSN. This is the 'default' way to connect in Alteryx. You can tell if your string references a DSN gecause it will generally have the word 'DSN' in it.

For example: "odbc: DSN=MYSQL_PROD_DB"

To check your connection string, look at the Input Data tool after you connect to a DB or go to options> advanced options> manage data connections to view your saved connections.

If you want to use a DSN connection on server or scheduler you need to make sure that a DSN with the exact same name exists on the server.

Additionally, you want to be careful about setting up a user DSN unless you are logged in as the 'run as' user. Either you need to set up a user DSN for all users including the run as account or you can create a system dsn connection in Alteryx that is good for all users. If you want to create a saved system connection in Alteryx through the manage data connections interface you need to run Alteryx as an administrator in order for the option to show up.

DSN-Less Connection Strings

If you dont want to set up DSNs you can write a DSN-Less connection string. In order to do so, you will need to have some proficiency with connection strings or enlist the help of your DBA. A great reference for writing the strings is connectionstrings.com. Whenwriting these strings, the only difference between how you need to configure in Alteryx and what the site shows is that you need to add odbc:(odbc) or odb: (oledb) to the beginning of the string.

Below is a sample DSN-Less Connection string for SQL Server (assume you are using SQL Server Native Client 11 driver)


Standard security example

odbc: Driver={SQL Server Native Client 11.0};Server=myServerAddress;
Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Trusted Connection example

odbc: Driver={SQL Server Native Client 11.0};Server=myServerAddress;
Database=myDataBase;Trusted_Connection=yes;

What about Gallery Data Connections that are shared down to Users?

In general, DSN-Less strings are the easiest approach for gallery data connections if you are able to write one as when you share them down to user they only need to have the correct driver installed (no DSN needs to be set up). If you use a gallery connection refernecing a DSN make sure to communicate to your users that they need to set up a DSN of the same name.

Also dont forget to share the gallery data connection with the run as user on the server if you are using windows authentication for the database.

Lastly, make sure that the server machines are 'signed in' to the gallery so they can receive the shared connections.

Comments
jpelski1
5 - Atom

Related:   I get a successful connection to an Oracle database and click save to save the connection.  However, it does not show up in the data connections list?   What might that be all about?

JessicaS
Alteryx Alumni (Retired)

Hi @jpelski1,

 

 

Are you asking about a gallery connection that you created in the browser on your gallery or in the local machine?

 

If it is a gallery connection, check to make sure that in designer you have the gallery added and you are 'signed in' as the correct user. 

 

If you are referencing a local connection, if you click the dropdown on input tool it will create a connection but not add it to your 'saved' connections. You should be able to see it in recent files if this is the case (see below screenshot)

2018-06-14_15-45-38.png

 

For the connection to show up in saved connections it has to be either created in the gallery and shared down or created from the below interface ( you can get to the same menu from options> advanced options> manage data connections or manage indb connections.

 

2018-06-14_15-48-14.png

If neither of the above is applicable, it may be an issue with your saved data connections XML files.  When a connection gets created in Alteryx it updates an XML file with the connection info.  You can check the timestamps on the applicable below file to see if it is getting updated after you save (note paths are assuming default install locations). 

 

C:\ProgramData\Alteryx\Engine\SystemAlias.xml  (system connection created for input tool connection)

C:\ProgramData\Alteryx\Engine\SystemConnections.xml (indb system connection)

C:\Users\user\AppData\Roaming\Alteryx\Engine\UserConnections.xml (INDB user connection) 

C:\Users\user\AppData\Roaming\Alteryx\Engine\UserAlias.xml (user connection created for input tool connection)

 

 

lepome
Alteryx Alumni (Retired)

Gallery Administrators need to share Data Connections with Artisans who author Gallery Workflows,.  This becomes important if the workflow is ever scheduled:  Data Connections shared with a Studio will not necessarily be translated by the Scheduler.  Data Connections shared with the User who uploaded the workflow will be correctly translated.  Just as the other XML files are maintained on users' machines, Gallery Data Connections are stored in C:\Users\user\AppData\Roaming\Alteryx\Engine\GalleryAlias.xml.

Greg_Murray
12 - Quasar

@lepome 

 

Can you change the name of a data connection after it is published and in use? We are trying to clean up our naming conventions and are curious if editing the data connection name via the gallery admin pages will cause issues with the existing workflows using those connections.

 

 

lepome
Alteryx Alumni (Retired)

@Greg_Murray 

 

Changing the name of the data connection will probably cause problems.  You can add a new connection with a new name, even if it's the same as another Data Connection.  You can encourage all Artisans to use that new connection (name) in all new workflows.  If you need to update a password for an existing connection, the Gallery Admin can do that successfully.  

 

But, from a functional perspective, the data connection, once used in a workflow is a fixed reference to what's in the Gallery.  If you remove the connection that has already been used, the workflow's author will need to edit the workflow to use the (now) current Data Connection (name).

Greg_Murray
12 - Quasar

@lepome 

 

We did end up changing the name of the data connection without issues (of course we tested it with a new data connection first). We were standardizing our naming conventions and probably won't need to do rename a connection in the future. Thanks for the follow up!

lepome
Alteryx Alumni (Retired)

@Greg_Murray   Thanks for letting me know.  If I'm not absolutely sure of something, I generally advise the more conservative approach, but I love it when users teach me new things like this.

lepome
Alteryx Alumni (Retired)

To reiterate the content of the above post, when publishing a workflow to the Gallery, if it connects to a database, the following actions must be taken on the server:

  1. The server must have the appropriate driver installed.
  2. If the database connection uses a DSN, it must be created on the server as a System DSN and have exactly the same name as the DSN mentioned in the workflow.  (Assuming that you make the Gallery the standard then this is equivalent to saying that the workflow must conform to whatever is set up on the server.)
  3. If the database connection is configured in Manage Data Connections and has a name (aka "alias") then the server must also have the same Data Connection configured as a System Data Connection.  (Run Designer on the Server using Run as administrator to be able to configure Data Connections of type System.)
  4. If the Data Connection is set up in the Gallery, it must be shared with the specific User, and the user must use the gallery connection when creating the workflow.  (The user should restart his/her local Designer after the Data Connection is shared from the Gallery so that he/she can view it.)  As I noted above, this is particularly important if the published workflow is ever scheduled.

Make sure driver is installed on serverMake sure driver is installed on serverIf DSN is used make sure it has the same name and is a System DSNIf DSN is used make sure it has the same name and is a System DSNData connection type System (or Gallery)Data connection type System (or Gallery)Create System Data Connection on ServerCreate System Data Connection on ServerOR create Data Connection in GalleryOR create Data Connection in Gallery

 

When creating new workflow select from Gallery data connectionsWhen creating new workflow select from Gallery data connections

Robert_Blackey
7 - Meteor

This is great documentation - thanks.

 

What are the differences when using In-DB tools on jobs scheduled to the server? We have a job that uses In-DB tools and which runs successfully locally but receives an error when scheduled on the server.

 

Connect in-DB: Unable to find connection "some connection name"

 

 

 

DanM
Alteryx Community Team
Alteryx Community Team

@Robert_Blackey,

 

The error you are getting is indicating that you do not have the same connection setup on your Server. The same connection and access must be available on the Server. Your Run As user may not have access to the database you are attempting to hit. Also that connection and driver must be setup on the server. Think of the Server as another user that must have all the same connections and access that you do.

 

DanM

Robert_Blackey
7 - Meteor

Thanks @DanM . I would expand the article to include In-DB connections.

 

The solution we decided on is having users save the in-db connection as an indbc file in a network file share which is accessible to both all users and the Alteryx Server.  That way all scheduled workflows run correctly and avoid the "Unable to find connection" error. (We don't utilize the gallery)

 

Cheers,
Bob

 

 

davidhenington
10 - Fireball

@Robert_Blackey Hey Bob, also, if you create an in-db file connection on designer and name the DSN identically to the connection established on the Server (that normal connections would use) it will translate the creds and connect successfully. 

Mahadeva
8 - Asteroid

Hi All,

 

I'm looking for the DB connection string to perform AWS Redshift Bulk load via Secret/Access keys since i want to establish connection without the DSN.

 

Appreciate if anybody can help on this.