Alteryx Server Reading/Writing to Database (Works manually, not scheduled)

[scottr1]

Hi all,

We are attempting to use Alteryx Server (2018.3.4.51585) write/read from a database on a schedule. We are using a service account (that I don't have access to, currently) for all database connections from our Alteryx Server. So I uploaded a test workflow (make sure to use a server database connection) and include it in my workflow.  Manually run the workflow and it works fine:

Set a quick one time schedule for the exact same workflow and get the following:

below are the workflow results and both appear to be using the local user:

  Any idea what might be going on here?

Thanks,

Scott 

[Paulteryx]

Do you have the database connection set up on the Server under Options-->Advanced Options-->Manage Data Connections? If you do, can you check and make sure it is set up as a System DSN? This is the most common issue. Make sure you have the database connection set up on the local machine that built the workflow using a particular alias. Then go to the server or system that will run this as a schedule, and make sure the same alias and DB connection is set up in the Manage Data Connections as a System DSN. Once that's done give it a try.

[scottr1]

Thanks Paulteryx, the connection is set up as a Gallery connection. When I manually run it via the Gallery it is successful. When I try and schedule it, the process fails (doesn't recognize the connection). Does what you suggest mean that I need to set up a System Connection and DSN on the server as well as a gallery connection?  That seems to be a bit kluge.

[Paulteryx]

I think I understand the issue a little better now. You may want to check on the server and make sure you have Run As credentials set. Go to your Alteryx System Settings, and click Next until you get to the Run As section. Make sure it has credentials that have access everything you need for this workflow to run. See if this helps.

[DataInvestigator007]

Is your server AD integrated when it comes to authentication

If so There could be 2 problems here.  

1. When you are running workflow you may be logging into your private studio with your credentials.  Server would use your credentials to access Database.  At the same time when you schedule it, server uses RUN AS credentials to access database and your database may not recognize the ID coming from server. 

2. The error shows unable to recognize alias database.  Have you been using connections created on server via Data connections tab and shared with RUN AS ID for workflow to recognize ? 

Please check these 2 options.  

[scottr1 · Accepted solution]

So this was a pretty deep pull.  The issue was that the server gallery (unknowingly) had become corrupt.  Users were not showing in the gallery. We are using AD so it was a case where everyone could log in and access their subscriptions. The subscriptions could be given access to the connectors but never actually have their user given access to the connector. So when run manually, it would worked fine (even using the run as system account we use), but when scheduled it would fail.  Not only because the user wasn't assigned to the connection but the user didn't even exist.  

I worked with Alteryx Support (big shout out to Dave!) and rebuilt the gallery with the a tool they provided, assigned the connector to a user (not just subscriptions) and voila!

Thanks to all who responded!