SQL Server Error - Driver's SQLSetConnectAttr failed

[danrh]

Hey all, need a little expert advice.  I haven't used SQL Server all that much, so I'm hoping this is a simple fix. I've built a workflow that copies data from some Access DB tables and loads it into a SQL Server that is located on the same machine.  I'm using the scheduler, also on the same local machine, to run this overnight, but I keep getting this error:

"Error SQLDriverConnect: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "EOLUS" requested by the login. The login failed.[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed."

The workflow runs fine when I do it manually. Ideas?

[KevinP · Accepted solution]

@danrh Based on the error message provided and the behavior you describe my guess is that you SQL Server connection string is using SSPI (Windows Authentication) and when running via scheduler the workflow isn't able to authenticate with your database server. Scheduler by default will run the workflow using the computers 'Local System' account. In most cases this account will not have access to any network resources that require windows credentials. This includes file shares and database servers. To work around this you can setup the 'Run As' credentials in the Alteryx System Settings. This will allow the scheduled job to impersonate the provided user when running the workflow instead of running it as the Local System account. As long as the Run As user credentials have access to the database this should resolve your issue. You can find information on setting up the Run As user in the following KB article.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Run-As-Settings/ta-p/17830

[danrh]

Took a fair amount of trouble shooting, but we finally got it working.  We ended up moving it to the server and running it from there with user credentials. Thanks for pointing me in the right direction!