community
cancel
Showing results for 
Search instead for 
Did you mean: 

alteryx server Knowledge Base

Definitive answers from Server experts.

Managing Data Connections Between Server/Scheduler and Local Machine

Moderator
Moderator
Created on

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 accommodate this?

 

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 essentially run 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 and  here.  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 option here

 

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 page and 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.  When writing 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
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?

Moderator
Moderator

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)

 

 

Alteryx
Alteryx

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.

Asteroid

@LisaL 

 

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.