SSIS job SQL login failed when using proxy and credentials

Question

Problem: I cannot get the SSIS proxy and SQL credentials configured correctly to use a restricted access domain account (Domain\UserName) for a SQL Agent job step that runs an SSIS package. The Domain\UserName account has all the required permissions and privileges and the SSIS package is also designed correctly. I have read much material on this subject and would like some help in case I missed something.

More Details: I have an SSIS package which simply reads data from one SQL Server database table and pushes that data to another database table on a different server. I have got a special identity (Domain\UserName) created to perform this operation because this needs to be run on a daily basis.

Now everything runs perfectly fine when I run visual studio with that Domain\UserName and manually run that SSIS package. But it is not running when I deploy that to SSISDB. I have created SQL Server Security Credentials with same user account and created an SSIS Package Executing Proxy using that credential and configured the SQL Agent Job step to Run as that proxy account.

But It always gives the following error when I try to execute that SQL Agent Job Step:

Message
Executed as user: Domain\UserName. Microsoft (R) SQL Server Execute Package
 Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft Corporation.
All rights reserved.    Started:  10:00:26 AM  Failed to execute IS server package
because of error 0x80131904. Server: XXXXXXXXXXXX, Package path: XXXXXXXXXX,
 Environment reference Id: 36.  Description: Login failed for user 'Domain\UserName'.
Source: .Net SqlClient Data Provider  Started:  10:00:26 AM  Finished: 10:00:26 AM
Elapsed:  0.578 seconds.  The package execution failed.  The step failed.

Rakesh Kumar Rakesh Kumar · Accepted Answer · 2017-10-12T16:55:55

I found the issue. I missed one step to grant permission for Domain\UserName to use the proxy.

Explanation You need to grant permissions to logins/roles who will be using the created proxy account using sp_grant_login_to_proxy system stored procedure. You can use sp_enum_login_for_proxy system stored procedure to view all the logins/roles who have access to the proxies.

Script - Granting proxy access to security principals :

USE msdb
GO 
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy 
EXEC msdb.dbo.sp_grant_login_to_proxy 
@proxy_name=N'SSISProxyDemo' 
,@login_name=N'Domain\UserName' 
--,@fixed_server_role=N'' 
--,@msdb_role=N'' 
GO 
--View logins provided access to proxies 
EXEC dbo.sp_enum_login_for_proxy 
GO

SSIS job SQL login failed when using proxy and credentials

1 Answers