August 10, 2014 at 9:55 am
Hi Experts,
We have recently upgraded to SQL 2014 from SQL 2012 and deployed our SSIS packages into SSIS Catalog. While running the SQL job, we are getting the below error:
Executed as user: xxxx. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2370.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 8:05:00 AM Failed to execute IS server package because of error 0x80131904. Server: xxxx, Package path: \SSISDB\xxxx.dtsx, Environment reference Id: NULL. Description: The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'. Source: .Net SqlClient Data Provider Started: 8:05:00 AM Finished: 8:05:00 AM Elapsed: 0.109 seconds. The package execution failed. The step failed.
For SQL 2012, it is suggested to have latest CU installed and looks like 12.0.2370.0 is the latest CU for SQL 2014 yet we are getting the same error.
Could you please help me on this regard. Any help would be greatly appreciated!
Thank You!
Srinivas
August 10, 2014 at 1:15 pm
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.
March 5, 2015 at 9:20 am
Hello Dear,
Please I need a help, I restore am integration services catalog, all the packages are deployed successfully, but when I try to execute one of the packages, I got the error: the execute permission was denied on the object 'xp_regread'. I know it will works if I grant execute permission on 'xp_regread' to public, but this option is totally forbidden and it is a rule in our company to not grant execute permission on 'xp_regread', please I need to know if there is a way to work around this issue.
Thank you
Nicolas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply