Proxy account has failed while executing SSIS through Agent

1
votes

Recently when I created a SQL Server Agent(2008) job to execute a SSIS package with proxy account, it failed with the below error message. What is this exception about? What causes it and how do I resolve it?

Error Message Executed as user: blaw. The process could not be created for step 1 of job 0xD5A5 (reason: A required privilege is not held by the client). The step failed.

Note:-It is working fine with Agent Service account.

Thanks

3
sqlsql-server-2008ssissql-server-agent

3 Answers

0
votes

You've not mentioned exactly how you're authenticating, but regardless, here's a script for creating a login, credential and proxy and granting permissions to SSIS packages:

CREATE LOGIN [MyLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

GRANT CONNECT TO [MyLogin]
go

CREATE ROLE MyRole
GO

EXEC sp_addrolemember @membername = N'MyLogin', @rolename = N'MyRole'
GO

CREATE CREDENTIAL MyCredential WITH IDENTITY = 'MyLogin', SECRET = 'MyPassword';

GO

USE [msdb]
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MyProxy',@credential_name=N'MyCredential', 
        @enabled=1
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy', @subsystem_id=11
GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'MyProxy', @login_name=N'MyLogin'
GO


CREATE ROLE MyRole
GO

EXEC sp_addrolemember @membername = N'MyRole', @rolename = N'db_ssisadmin'
GO

EXEC sp_addrolemember @membername = N'MyRole', @rolename = N'db_ssisoperator'
GO

EXEC sp_addrolemember @membername = N'MyLogin', @rolename = N'MyRole'
GO
0
votes

Just worked through this issue and came to a different resolution. A global security policy was getting in the way. It turns out the development server that was presenting this issue accidentally had a much more restrictive policy being applied than the production counterpart that was working fine. Not exactly sure which overridden permission under the policy was causing the issue, but a less restrictive policy resolved the issue nonetheless. Basically, check with your Active Directory admin if the Local Security Policy is locked down on the server presenting the issue.