Unable to Start Execution – Login Doesn’t have Permissions to use the Proxy
Proxy accounts for PowerShell, SSIS, and Operating System (CmdExec) job steps are instrumental when performing certain actions in SQL Server Agent. Once you’ve set up a proxy, and configured the job-step to use that proxy, SQL Server uses impersonation to execute that job step as the credential specified in the proxy. Of course, permissions to use the proxy are critical for correct operation. If permissions are not setup correctly, you may see the following error when attempting to run a job using a proxy:
Unable to start execution of step 1 (reason: JobOwner DOMAIN\Account doesn’t have permissions to use proxy 1 for subsystem SSIS). The step failed.
The Fruit and Vegetable Seller by Louise Moillon
The error looks like the job owner doesn’t have access to the proxy. There is a simple method for granting the correct rights to the account:
|
1 2 |
EXEC dbo.sp_grant_login_to_proxy @login_name = N'DOMAIN\Account' , @proxy_name = 'ProxyName'; |
Once you execute the above code, you may see the following error:
‘DOMAIN\Account’ is a member of sysadmin server role and cannot be granted to or revoked from the proxy. Members of sysadmin server role are allowed to use any proxy.
So, what gives? The account doesn’t have access to the proxy, yet the account is a member of the sysadmin role, which has unrestricted access to everything? The following query proxy configuration details. It also shows which credential the proxy uses.
|
1 2 3 4 5 6 7 |
SELECT ProxyName = sp.name , ProxyEnabled = sp.enabled , CredentialName = c.name , CredentialIdentity = c.credential_identity FROM dbo.sysproxies sp INNER JOIN sys.credentials c ON sp.credential_id = c.credential_id ORDER BY sp.name; |
╔══════════════════╦══════════════╦════════════════╦════════════════════╗ ║ ProxyName ║ ProxyEnabled ║ CredentialName ║ CredentialIdentity ║ ╠══════════════════╬══════════════╬════════════════╬════════════════════╣ ║ ProxyName ║ 0 ║ MyCredential ║ DOMAIN\Account ║ ╚══════════════════╩══════════════╩════════════════╩════════════════════╝
As you can see in the output above, the proxy is disabled. Use the following code to enable the proxy:
|
1 2 |
EXEC dbo.sp_update_proxy @proxy_name = N'ProxyName' , @enabled = 1; |
See the rest of our posts on SQL Server Agent and troubleshooting.
See documentation for configuring proxies in SQL Server Agent here.