03-21-2018 11:15 AM - edited 07-22-2021 11:17 AM
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:
In order to set up a database connection lets review how the scheduler and server work.
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.
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.
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;
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.
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?
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)
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.
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)
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.
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.
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).
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!
@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.
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:
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"
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
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
@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.
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.