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:
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.
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.
This worked like a charm for me.