There have been a number of times over the years when I have had to create a Proxy user in SQL Server in order to provide needed access to connections and locations being used in an SSIS package. Sometimes the SQL Server Agent login account simply doesn’t and shouldn’t have the required permissions.
Before using a Proxy account, do check to see if the permissions issue isn’t just that the SQL Server Agent login account has been set up as ‘Local System’. If that is the case, see if you can change it to a domain account specifically created for SQL Server Agent purposes. Check Administrative Tools -> Services on the server where the SSIS SQL Server resides to see what login account the SQL Server Agent is mapped to.
The user mapped to the SQL Server Agent Service Account will need read/write permissions. If you do need to create a new domain login for the SQL Server agent, in SSMS go to Server-> Security (not database security) -> Logins -> left click New Login -> Search -> Locations button -> Entire Directory -> select main domain ->OK -> Sql Agent username-> Check Names button-> OK-> Server Roles-> sysadmin-> OK..
If it turns out that you need to create an SSIS proxy user, edit this script to use the correct username and password and run it to create the proxy user.
USE master GO -- Create a proxy credential for xp_cmdshell. EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\username', 'password';--SELECT * FROM [master].[sys].[credentials] -- Grant execute permission on xp_cmdshell to the SQL Server login account. GRANT exec ON sys.xp_cmdshell TO [DOMAIN\username] GO -- Create a credential containing the domain account PowerDomain\PowerUser and its password CREATE CREDENTIAL MyCredential WITH IDENTITY = N'DOMAIN\username', SECRET = N'password' GO USE [msdb] GO -- Create a new proxy called SSISProxy and assign the PowerUser credentail to it EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MyProxy',@credential_name=N'MyCredential',@enabled=1 -- Grant SSISProxy access to the "SSIS package execution" subsystem EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy', @subsystem_id=11 -- Grant the login testUser the permissions to use SSISProxy EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'DOMAIN\username', @proxy_name=N'MyProxy' GO
You will be able to see the proxy user in SSMS under SQL Server Agent. Is is in the SSIS PAckage Execution section because we added it to the SSIS subsystem in our code.
Now when you create your SQL Server Agent Job you can choose to run the SSIS package as your proxy user with the required permissions,rather than the SQL Server Agent account.