read registry w/o sysadmin, is it possible?

Question

read registry w/o sysadmin, is it possible?

sporoy

Hall of Fame

Points: 3114
More actions
January 6, 2012 at 7:34 am

#254298

Hi, in my SQL Server 2008 EE SP1 environment, one of the application needs to use xp_instance_regread for reading registry values (values like TCPPort in registry), I couldn't find a documentation but by experimenting I saw that only sysadmin role can use this xp.
Is there any way to your knowledge, to grant read registry without making the login sysadmin? (something like using Certificate, credential..??) I just want to grant "read registry", is this possible?
Thanks..

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

sporoy (1/6/2012)
Hi, in my SQL Server 2008 EE SP1 environment, one of the application needs to use xp_instance_regread for reading registry values (values like TCPPort in registry), I couldn't find a documentation but by experimenting I saw that only sysadmin role can use this xp.
Is there any way to your knowledge, to grant read registry without making the login sysadmin? (something like using Certificate, credential..??) I just want to grant "read registry", is this possible?

Have you tried to grant exec on xp_instance_regread to public role?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

sporoy Hall of Fame Points: 3114 More actions · Answer 2

yes, the error is :

Msg 22001, Level 16, State 1, Line 0

xp_instance_regread() returned error 5, 'Access is denied.'

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 3

you could create a stored procedure your application is allowed to execute and use the EXECUTE AS option in that SP so that it is executed with a sysadmin account.

sporoy Hall of Fame Points: 3114 More actions · Answer 4

This was the best option but application is a sealed application, and what is said to us is, the master.sys.xp_instance_regread procedure will be used..

sporoy Hall of Fame Points: 3114 More actions · Answer 5

I also tried signing xp_instance_regread with a certificate but it gives alter procedure error. ( I guess because it is a system procedure)

Perry Whittle SSC Guru Points: 234027 More actions · Answer 6

sporoy (1/6/2012)
yes, the error is :
Msg 22001, Level 16, State 1, Line 0
xp_instance_regread() returned error 5, 'Access is denied.'

This works for me

grant execute on object::sys.xp_instance_regread to public

Who are you logged on as when granting the permissions?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

I would be throwing this back at the developer of your sealed application (using unsupported and deprecated procedures with no documentation whatsoever sounds like a winning combination by the way). If they're that brilliant to hand you such a find, they should be brilliant enough to tell you how to make your server unsecure enough to run this.

That said - the access denied may be external to SQL Server. In general when SQL requests that don't originate from sysadmins need to access external resources, the external access is authorized under the context of the SQL Server service account. So this may be what is required (i.e. grant the service account running SQL Server on the server permission to read the registry). Note that this would be a red flag in terms of a security violation so I am not advocating you implement it, but it may fix your issue.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Perry Whittle SSC Guru Points: 234027 More actions · Answer 8

granting permissions on the xp worked for me, i suspect the account trying to grant has insufficient permissions

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"

sporoy Hall of Fame Points: 3114 More actions · Answer 9

Perry Whittle (1/6/2012)
granting permissions on the xp worked for me, i suspect the account trying to grant has insufficient permissions

In my case when I make the executing user sysadmin, xp works, all other server roles I granted gave me access denied error, is your executing user not sysadmin and xp works for that account?

bbcolo22 SSC-Addicted Points: 465 More actions · Answer 10

I am having the same problem. I have tried every possible grant on XP_REGREAD and XP_INSTANCE_REGREAG but I can only run XP_REGREAD or XP_INSTANCE_REGREAD if I have the SYSADMIN role.

Did you ever find a solution?

Lowell SSC Guru Points: 323518 More actions · Answer 11

here's a start to finish example, where i create a user, grant just the permissions Perry identified in his posts, specifically impersonate that user for testing, and then read from the registry:

this worked flawlessly for me.

USE master;

CREATE USER TestingXPRead WITHOUT LOGIN;

grant execute on object::sys.xp_instance_regread to TestingXPRead;

EXECUTE AS USER='TestingXPRead';

SELECT USER_NAME();

declare @DefaultBackupDirectory nvarchar(512);

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @DefaultBackupDirectory OUTPUT;

SELECT ISNULL(@DefaultBackupDirectory,'');

--change back into myself

REVERT;

DROP USER TestingXPRead;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bbcolo22 SSC-Addicted Points: 465 More actions · Answer 12

This could be what the other poster has also... Your example script is different from my situation.

In my case, I just have a login, but no user. This login runs SQL with XP_REGREAD (and XP_INSTANCE_REGREAD)

and this works fine with SYSADMIN granted to this login.

We want to revoke SYSADMIN from this account, so it is my job to get this working.

In other words, can you help with an example script similar to what you provided, but with "create login" instead of "create user" (within MASTER).

Jeff Moden SSC Guru Points: 1004525 More actions · Answer 13

sporoy (1/9/2012)
Perry Whittle (1/6/2012)
granting permissions on the xp worked for me, i suspect the account trying to grant has insufficient permissions
In my case when I make the executing user sysadmin, xp works, all other server roles I granted gave me access denied error, is your executing user not sysadmin and xp works for that account?

NEVER EVER grant sysadmin privs to a user or application login. It may take a bit to find the right way to do this but granting sysadmin to anyone or anything other than the resident DBAs is one of the worst things you could ever do for too many reasons to list here.

{EDIT} Sorry... I didn't see the post immediately above this one. I stopped scrolling when I saw the post quoted above because it was a showstopper for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Deque Mr or Mrs. 500 Points: 589 More actions · Answer 14

Jeff Moden (6/6/2013)
NEVER EVER grant sysadmin privs to a user or application login. It may take a bit to find the right way to do this but granting sysadmin to anyone or anything other than the resident DBAs is one of the worst things you could ever do for too many reasons to list here.

In an ideal world, yeah, I agree with that. Sometimes exceptions have to be made, though, because some applications require the service account to be a SysAdmin, such as Microsoft BizTalk Server. Sure, you may be able to work it out so the account doesn't require SysAdmin access, but I'm not sure what Microsoft's support stance is going to be if you don't even follow step 1 of their documentation.

Of course, that requirement is one of the major reasons why BizTalk Server gets it's own instance in our environment.

read registry w/o sysadmin, is it possible?

Cookies on SQLServerCentral