Friday, December 28, 2012

#scorch: Access denied when using Query Database activity

Here’s a little funny I ran into this morning and thought I would share the quicky solution.

Using the Query Database activity worked like a charm using the Runbook tester, but when checking in the runbook, the activity failed with an access denied error:

screenshot1

This is despite granting the runbook service account (the account in the error) the appropriate permissions in SQL.

On the SQL side, we received the following error message:

Login failed for user Username Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

Google tells us that we need to ensure that the user is a member of sysusers in SQL, that UAC is switched off and several other things that are simply not relevant here.

Fix:

Open the activity, and go to the Security tab and enter the account credentials to use, even if this is the same credentials that run the service account.

screenshot2

This worked like a charm for me.

No comments:

Related Posts with Thumbnails