The issue fixed by http://support.microsoft.com/kb/2720171/en-us (which does not require the latest CU) has a specific cause: "This issue occurs because of the way Integration Services executes packages stored in SSISDB in SQL Server 2012 environments." which means:
{
Microsoft failed to ensure the "Executed as user: DOMAIN|USER" was given permission to run XP_REGREAD.
}
That doesn't mean every user must be given permission to run XP_REGREAD. That means every user, even if they were supposed to have permission to run XP_REGREAD, was unable to run XP_REGREAD (hence the bug, hence the fix).
So, what permissions are needed for XP_REGREAD? Determining permissions is a bit of a challenge, because XP_REGREAD is not documented (it is designed for internal tool uses, such as SSIS, SSMS, SSCM, etc). Luckily, Microsoft did document the permissions for this undocumented XP back in SQL Server 2000 days - http://support.microsoft.com/kb/887165/en-us. Because these xprocs are not documented, Microsoft thought it would be OK to change their behavior (in SP4) without offering the community a warning. Problem was (and still is) that everyone loves to use undocumented code. Still, rather than immediately modifying the registry, what is important about that reference is that it reveals:
{
This error message has nothing to do with the permissions of the SQL Server user account in the context of which one of these registry extended stored procedures is called. The error message occurs even if the user has Administrator permissions.
}
The above is not sufficiently accurate: It is the permission of the SQL Server startup account that matters. Even after the registry is modified, if the SQL Server startup account has been denied permission to read the specific registry, then Access Denied will still be seen. But, the most important information about that KB article is:
{
When a query calls a registry extended stored procedure, the registry extended stored procedure examines the requested registry subkey against a control list of registry hives.
}
While the article doe not identify the contents of that control list, it is a fairly safe bet that its content is restricted to the keys SQL Server (and the clients that ship with it) need to use. In other words, *not necessarily* the keys that a package *developer* (who had learned about this undocumented command for site such as this:)) might ask XP_REGREAD to read....
Next we need to consider scheduled job permissions. One example, from http://msdn.microsoft.com/en-us/library/ms178031.aspx:
{
If you change job ownership to a user who is not a member of the sysadmin fixed server role, and the job is executing job steps that require proxy accounts (for example, SSIS package execution), make sure that the user has access to that proxy account or else the job will fail.
}
Luckily, the user in question is identified in your error message as "Executed as user: xxxx"
And of course, the SQL Server agent startup account must be a member of the sysadmin role (and thus able to execute under the security context of the SQL Server startup account).
Which leads to the following:
0. Do all packages fail or is it that just this package fails? The former implies a bug similar to http://support.microsoft.com/kb/2720171/en-us, but I would expect everyone using SQL Server 2014 SSIS to be screaming, by now (see 4, and consider whether one user or multiple users are failing). The latter implies a big similar to http://support.microsoft.com/kb/2720171/en-us is not applicable.
1. Search the package's T-SQL steps (if any) for the presence of "xp_regread". If found, modify the registry as revealed by http://support.microsoft.com/kb/887165/en-us
2. Logon as user: xxxx (or SETUSER) and try to run xp_regread.
2a. Can the package be run manually (outside of a scheduled job)?
2b. Review the permissions and role membership of user: xxxx in SQL Server 2012 (if still available) or temporarily make the user a sysadmin (for testing purposes).
3. If a SQL Server login, ensure the sys.database_principals for "user: xxxx" maps to the correct sys.server_principals by SID (and use sp_change_users_login if it doesn't).
4. Ensure the SQL Server Agent is a member of SQL Server's sysadmin role.