xp_cmdshell, Access Denied, Further Investigation Reveals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TdarTdar - 13 Oct 2005 18:00 GMT

Hi,

I am stall having a problem with access is denied running a simple
exec xp_cmdshell 'dir \\othercomputer\cshare\'. I have setup my
SQL agent and proxy account to a account called SQIUSER and gave
that Admin Rights to the whole network, and added that user to the
' \\othercomputer\cshare\' However I am still getting access is denied.

So, I loged into that SQIUSER account as a normal user from the SQL Server
system and browsed to the network path \\othercomputer\cshare\ and i got
its contents and can read/write to it.

So opened SQL QA and ran the command again and Got 'Access is denied'

I decied to run a ShareEnum from sysinternals and that shows me as getting
access is denied on that \\othercomputer\cshare\ path.

So what is wrong here i can list read write \\othercomputer\cshare\ as the
loged in user but SQL QA and Sysinternals ShareEnum says I am not allow
access to that
drive.

What am I missing here??

Reply to this Message

Peter Yang [MSFT] - 14 Oct 2005 04:25 GMT

Hello,

As you know, when xp_cmdshell is invoked by a user who is a member of the
sysadmin fixed server role, xp_cmdshell will be executed under the security
context in which the SQL Server service is running. When the user is not a
member of the sysadmin group, xp_cmdshell will impersonate the SQL Server
Agent proxy account, which is specified using xp_sqlagent_proxy_account.

Please make sure the domain user of SQL Server Agent proxy account has both
NTFS and shared permssion on the folder \\othercomputer\cshare. You could
right click the folder->Properties, and check this on both Shared and
Security tab.

If the issue persists, please tempoarily add this domain user to local
admin of the othercomputer to test the sitaution.

Thanks & Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469

Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.

=====================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

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

| Thread-Topic: xp_cmdshell, Access Denied, Further Investigation Reveals
| thread-index: AcXQF5KwAo/U8c11QvGY0J6YgrPF1Q==
[quoted text clipped - 42 lines]
|
| What am I missing here??

Reply to this Message

TdarTdar - 14 Oct 2005 06:00 GMT

Hello,
As i tried to explain the Agent proxy account has Admin rights, also
this share and security tab show the admin group in with this user
resides. This is not just a problem with SQL if you read below i used
the sysinternals program called ShareEnum V1.6 Loged into the win 2k
server that i host sql sever 2k from and used this ShareEnum program
and i get access is denied to that \\othercomputer\cshare but i can
do start run and \\othercomputer\cshare and get the directory listing.

Tonite I totally redid the security by remove this nt 4 server from the
domain and cleaning out all the junk users etc then readded this to the
domain and we did the shares, figuring maybe there was a problem
with the sig's or something. well this gave me the same result.

So why when the user is log in to the system they can see the
\\othercomputer\cshare but SQL QA (with proxy user of the same) and
shareEnum (with same user as loged in and sql proxy user) get "access is
denied"??

I know this may sound weird but that is what is happening.

So your comments Peter are things i have done already, i just must
have not explined it right.

Is there a higher level poicy that might not be right that could cause
this and what can i do to solve this I really need this to work.

Thanks,
Tdar

> Hello,
>
[quoted text clipped - 91 lines]
> |
> | What am I missing here??

Reply to this Message

Peter Yang [MSFT] - 14 Oct 2005 12:02 GMT

Hello Tdar,

It seems a issue for Windows NT because it does not support Kerberos
authentication. SQL cannot delegate a Windows user to access a network
resource on Windows NT server. Please see if you could access a local file
path such as c:\ or \\<local server>\.

Also, you may want to use a domain user with sysadmin right on server to
start SQL service to work around this issue.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

TdarTdar - 14 Oct 2005 14:51 GMT

I'll doubple check the sysadmin rights on that user. Also I can do a local
disk access from QA. Humm re the kerberos issuse I hope not.... That would
kinda nullify that backwards complatblity ....

> Hello Tdar,
>
[quoted text clipped - 16 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Peter Yang [MSFT] - 17 Oct 2005 06:02 GMT

Hello Tdar,

Since there is no issue for local disk access, it does seem to be a
delegation issue related to kerberos. You may want to install SQL on a
Win2000/2003 server, move the database to the server, and then test the
situation.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

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

>Thread-Topic: xp_cmdshell, Access Denied, Further Investigation Reveals
>thread-index: AcXQxlD0966O1STeRKyZhbqhJxYpfA==
>X-WBNR-Posting-Host: 65.35.95.11
>From: "=?Utf-8?B?VGRhclRkYXI=?=" <Tdar@noemail.nospam>
>References: <C9ABAD68-E537-46BD-BE4B-196CFB9A277F@microsoft.com>

<WMTii7G0FHA.1144@TK2MSFTNGXA01.phx.gbl>
<0AFDB9AE-9CA7-4284-B133-E467FAE96FC7@microsoft.com>
<ajcp36K0FHA.1468@TK2MSFTNGXA01.phx.gbl>

>Subject: RE: xp_cmdshell, Access Denied, Further Investigation Reveals
>Date: Fri, 14 Oct 2005 06:51:03 -0700
[quoted text clipped - 39 lines]
>>
>> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message