MS SQL Server - Yukon xp_cmdshell setting

Author

Yukon xp_cmdshell setting

Vaibhav

2006-02-09, 3:23 am

Hello,

How does Yukon keep the information about the security context of
xp_cmdshell (user or agent) ?
How can we retrieve it ?

I could not find a registery setting for that.

Please help.

Thanks a lot in Advance
Regards,
Vaibhav

Mike Hodgson

2006-02-09, 3:23 am

Firstly, don't cross-post, it's just annoying. Pick the most likely
group for your question and just post there - if it's not the right
group someone will usually forward it to the right group.

That said... As you probably know, xp_cmdshell
<http://msdn2.microsoft.com/en-us/library/ms175046.aspx> is disabled by
default in SQL 2005. When it's enabled you have to be a member of the
sysadmins role to use it. It you're not, then the call fails. Unless,
that is, you've defined a proxy account. You create that proxy account
with the system stored proc sp_xp_cmdshell_proxy
_account
<http://msdn2.microsoft.com/en-us/li...8SQL.90%29.aspx> and
it stores the proxy account credentials in a system table (in the hidden
resources database I think). You can use the sys.credentials
<http://msdn2.microsoft.com/en-us/library/ms189745.aspx> system view to
query those stored credentials.

select * from sys.credentials;

The row returned from that view with a name column of
" ##xp_cmdshell_proxy_
account##" is the proxy account you just created
for xp_cmdshell.

--
*mike hodgson*
http://sqlnerd.blogspot.com

Vaibhav wrote:

>Hello,
>
> How does Yukon keep the information about the security context of
>xp_cmdshell (user or agent) ?
> How can we retrieve it ?
>
> I could not find a registery setting for that.
>
>Please help.
>
>Thanks a lot in Advance
>Regards,
>Vaibhav
>
>
>
>

Ike the Potato

2006-02-09, 3:23 am

[.../]
Irony meter, Mike!

"Mike Hodgson" <e1minst3r@gmail.com> wrote in message news:eIdW5eULGHA.668@TK2MSFTNGP11.phx.gbl...
Firstly, don't cross-post, it's just annoying. Pick the most likely group for your question and just post there - if it's not the right group someone will usually forward it to the right group.

That said... As you probably know, xp_cmdshell is disabled by default in SQL 2005. When it's enabled you have to be a member of the sysadmins role to use it. It you're not, then the call fails. Unless, that is, you've defined a proxy account. You create that proxy account with the system stored proc sp_xp_cmdshell_proxy
_account and it stores the proxy account credentials in a system table (in the hidden resources database I think). You can use the sys.credentials system view to query those stored credentials.

select * from sys.credentials;

The row returned from that view with a name column of " ##xp_cmdshell_proxy_
account##" is the proxy account you just created for xp_cmdshell.

--
mike hodgson
http://sqlnerd.blogspot.com

Vaibhav wrote:
Hello,

How does Yukon keep the information about the security context of
xp_cmdshell (user or agent) ?
How can we retrieve it ?

I could not find a registery setting for that.

Please help.

Thanks a lot in Advance
Regards,
Vaibhav

Vaibhav

2006-02-09, 7:23 am

Mike,
Thanks for that Info.
I have still a doubt about the security context in which the xp_cmdshell runs in each case ( proxy acc Impersonation case Vs if the user is memnber of sysadmins role).

TIA
Vaibhav
"Mike Hodgson" <e1minst3r@gmail.com> wrote in message news:eIdW5eULGHA.668@TK2MSFTNGP11.phx.gbl...
Firstly, don't cross-post, it's just annoying. Pick the most likely group for your question and just post there - if it's not the right group someone will usually forward it to the right group.

That said... As you probably know, xp_cmdshell is disabled by default in SQL 2005. When it's enabled you have to be a member of the sysadmins role to use it. It you're not, then the call fails. Unless, that is, you've defined a proxy account. You create that proxy account with the system stored proc sp_xp_cmdshell_proxy
_account and it stores the proxy account credentials in a system table (in the hidden resources database I think). You can use the sys.credentials system view to query those stored credentials.

select * from sys.credentials;

The row returned from that view with a name column of " ##xp_cmdshell_proxy_
account##" is the proxy account you just created for xp_cmdshell.

--
mike hodgson
http://sqlnerd.blogspot.com

Vaibhav wrote:
Hello,

How does Yukon keep the information about the security context of
xp_cmdshell (user or agent) ?
How can we retrieve it ?

I could not find a registery setting for that.

Please help.

Thanks a lot in Advance
Regards,
Vaibhav

Andrew J. Kelly

2006-02-09, 9:23 am

IN SQL2005 the proxy accounts don't work like they did in 2000. You determine exactly what permissions they have when they run any type of job by associating a credential with the Proxy. Then you associate the proxy witht he job step. Unless they are SA (which they should not be) they can only use the proxy account(s) that you give them access to. And they will only have permissions that the account from the credentials for that proxy has and no more.

--
Andrew J. Kelly SQL MVP

"Vaibhav" <vaibhavtole@yahoo.com> wrote in message news:%23QWv6BXLGHA.3272@tk2msftngp13.phx.gbl...
Mike,
Thanks for that Info.
I have still a doubt about the security context in which the xp_cmdshell runs in each case ( proxy acc Impersonation case Vs if the user is memnber of sysadmins role).

TIA
Vaibhav
"Mike Hodgson" <e1minst3r@gmail.com> wrote in message news:eIdW5eULGHA.668@TK2MSFTNGP11.phx.gbl...
Firstly, don't cross-post, it's just annoying. Pick the most likely group for your question and just post there - if it's not the right group someone will usually forward it to the right group.

That said... As you probably know, xp_cmdshell is disabled by default in SQL 2005. When it's enabled you have to be a member of the sysadmins role to use it. It you're not, then the call fails. Unless, that is, you've defined a proxy account. You create that proxy account with the system stored proc sp_xp_cmdshell_proxy
_account and it stores the proxy account credentials in a system table (in the hidden resources database I think). You can use the sys.credentials system view to query those stored credentials.

select * from sys.credentials;

The row returned from that view with a name column of " ##xp_cmdshell_proxy_
account##" is the proxy account you just created for xp_cmdshell.

--
mike hodgson
http://sqlnerd.blogspot.com

Vaibhav wrote:
Hello,

How does Yukon keep the information about the security context of
xp_cmdshell (user or agent) ?
How can we retrieve it ?

I could not find a registery setting for that.

Please help.

Thanks a lot in Advance
Regards,
Vaibhav

Vaibhav

2006-02-10, 3:23 am

Hello Andrew,
Thanks for that Info.

Actually I started facing the problem with ImpersonateClient property of DMO (for sql-2000) which is not supported in Yukon.

I am facing problems in getting the state of ImpersonateClient property to
true. (even for 2000).

By documents , The ImpersonateClient property exposes the security context
for nonadministrative users executing xp_cmdshell.
This property applies To the IntegratedSecurity Object (DMO).

I tried the following ...

1. Firstly I removed the proxy account on the agent.
2. Tried to print the value of the ImpersonateClient ..
code snippet ::
//
Set oIntegratedSecurity = oSQLServer.IntegratedSecurity
oIntegratedSecurity.ImpersonateClient = False
x = oIntegratedSecurity.ImpersonateClient
MsgBox x
//

It prints False.
// its Obvious behaviour

3. Now I created a proxy account on the agent.

4. Again Tried to print the value of ImpersonateClient.

It again prints False.
// its unexpected behaviour

Internally when I saw the registery value ::
HKEY_LOCAL_MACHINE\S
OFTWARE\Microsoft\MS
SQLServer\MSSQLServe
r\ImpersonateClient

It was false.

5. Then I manually set the value as true by ..
oIntegratedSecurity.ImpersonateClient = True

6. Then I repeated the above steps ( Disabling Proxy account) .. I GET
ImpersonateClient = True ( whn used to print )

( reEnabling Proxy account) .. I GET
ImpersonateClient = True ( whn used to print )

Please Help Me out , in which case I should get ImpersonateClient = True
(without explicitly doing that).

According to my observations I think that property is never being used. Is
it a really useless property ??

Do we have similar property in Yukon ??

Documentation says that the functionality has been moved to "Server
Connection" class of SMO but I couldnt find the property that will return
the result for the above.

Please help me.

Thanks a lot in advance
Regards
Vaibhav
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message news:eFlj32XLGHA.3944@tk2msftngp13.phx.gbl...
IN SQL2005 the proxy accounts don't work like they did in 2000. You determine exactly what permissions they have when they run any type of job by associating a credential with the Proxy. Then you associate the proxy witht he job step. Unless they are SA (which they should not be) they can only use the proxy account(s) that you give them access to. And they will only have permissions that the account from the credentials for that proxy has and no more.

--
Andrew J. Kelly SQL MVP

"Vaibhav" <vaibhavtole@yahoo.com> wrote in message news:%23QWv6BXLGHA.3272@tk2msftngp13.phx.gbl...
Mike,
Thanks for that Info.
I have still a doubt about the security context in which the xp_cmdshell runs in each case ( proxy acc Impersonation case Vs if the user is memnber of sysadmins role).

TIA
Vaibhav
"Mike Hodgson" <e1minst3r@gmail.com> wrote in message news:eIdW5eULGHA.668@TK2MSFTNGP11.phx.gbl...
Firstly, don't cross-post, it's just annoying. Pick the most likely group for your question and just post there - if it's not the right group someone will usually forward it to the right group.

That said... As you probably know, xp_cmdshell is disabled by default in SQL 2005. When it's enabled you have to be a member of the sysadmins role to use it. It you're not, then the call fails. Unless, that is, you've defined a proxy account. You create that proxy account with the system stored proc sp_xp_cmdshell_proxy
_account and it stores the proxy account credentials in a system table (in the hidden resources database I think). You can use the sys.credentials system view to query those stored credentials.

select * from sys.credentials;

The row returned from that view with a name column of " ##xp_cmdshell_proxy_
account##" is the proxy account you just created for xp_cmdshell.

--
mike hodgson
http://sqlnerd.blogspot.com

Vaibhav wrote:
Hello,

How does Yukon keep the information about the security context of
xp_cmdshell (user or agent) ?
How can we retrieve it ?

I could not find a registery setting for that.

Please help.

Thanks a lot in Advance
Regards,
Vaibhav

Sponsored Links