December 6, 2006 at 9:50 am
Hi,
I am trying to execute a few stored procedures that read keys from the registry. However, when I run them in SQL 2005 they are giving me the following error:
Msg 22001, Level 16, State 1, Line 0
xp_regread() returned error 5, 'Access is denied.'
I've had these working on SQL 2000. I've checked permissions to the Extendable Stored Procedures and the user I am accessing them as has access to execute.
Any suggestion on the what the issue could be?
Thanks,
J.
December 7, 2006 at 8:36 am
its possible that you don't have permissions to read the registry key itself. I'm not sure how to set permissioins up, but there are permissions in the registry similar to the permissions to the file system. your sql agent account would have to have permissions to read the key(s) you are after.
December 7, 2006 at 12:45 pm
I've found the following article on Microsoft's web site, but it is only for SQL 2000 and not SQL 2005:
http://support.microsoft.com/kb/887165
However, I couldn't find the path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\ExtendedProcedures
---------
I verified that the Accounts used for the SQL/SQLSA accounts is in fact a admin account. I also was able to verify that the administrator account has full access to the registry.
June 18, 2007 at 1:40 pm
I'm having the same problem.
My calls to xp_regread work fine when I'm connected as myself, but my application is using an application role to provide access to all of the tables and stored procedures. When I set the application role (using sp_setapprole), my call fails. I call a stored procedure in my database, which, in turn, calls master.dbo.xp_regread.
The call works as is in SQL2000, but isn't working in 2005.
SQL Server is running as Local System.
I don't know what registry permissions I do or don't need in order to get this to work.
I'm getting "xp_regread() returned error 5, 'Access is denied.'" back as an error. Given this isn't regarding EXECUTE permissions on the procedure, I assume that it is a registry permission issue, not an issue with running the SP.
Please advise.
Matt
June 18, 2007 at 2:10 pm
oh, and i'm running Vista, if it makes a difference.
edit: the issue is also present when running under WinXP, so OS doesn't appear to make a difference.
I'll also throw this in: I'm creating the Application Role in a SQL2000 compliant manner (EXEC sp_addapprole N'MattAppRole', N'Password123'), and activating it in a similar manner (exec sp_setapprole N'MattAppRole', {ENCRYPT N'Password123'}, 'odbc'
June 19, 2007 at 3:56 am
With SQL 2005 the registry path has changed. There in no path MSSQLServer and all details pertaining to the server and instance are located in Microsoft SQL Server itself and separated by thier folders. But when you get the error Access denied, check if the account you use have the permission to read that particular registry key.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 19, 2007 at 4:53 am
As with any undocumented feature, MS has changed it.
XP_reg... cannot be used to read outside of SQL Server's registry. You can read values that fall under SQL root but not your own...
I read it in a KB some time ago but cannot find it now. It was sort of changed with SQL 2000 SP4 but did not fully restrict.
Sorry - you cannot use it (That I know of)
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 19, 2007 at 7:13 am
Crispin -
That's more confusing now. If I just connect to SQL server (say, with Management Studio) using Windows Authentication (or, even, SQL Authentication) and run my stored procedure, I can read the registry settings that I need to. It's only when I activate the application role that the call fails.
Sugesh -
What account should I check for permission on the registry keys for. Again, since it works without an application role (including with a SQL Authentication connection), I would assume that the permission set for SQL Server itself is correct. Therefore, who am I checking for?
Thanks for the help!
Matt
June 19, 2007 at 7:25 am
I'm working from a hazy memory here but as far as I remember, it was restricted but I seem to remember a way to override it. Have a scratch around on google for xp_regread and SQL 2005.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
January 27, 2010 at 12:14 am
Was a solution ever found for this problem? I am having the same issue.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply