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.

 

 

Alteryx
Alteryx

@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).

Asteroid

@LisaL 

 

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!

Alteryx
Alteryx

@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.

Alteryx
Alteryx

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.pngMake sure driver is installed on serverIf DSN is used make sure it has the same name and is a System DSN.pngIf DSN is used make sure it has the same name and is a System DSNData connection type System (or Gallery).pngData connection type System (or Gallery)Create System Data Connection on Server.pngCreate System Data Connection on ServerOR create Data Connection in Gallery.pngOR create Data Connection in Gallery

 

When creating new workflow select from Gallery data connections.pngWhen creating new workflow select from Gallery data connections