RE: xp_cmdshell, Access Denied, Further Investigation Reveals

From: TdarTdar (Tdar_at_noemail.nospam)
Date: 10/14/05 

Date: Thu, 13 Oct 2005 22:00:07 -0700

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

"Peter Yang [MSFT]" wrote:

> 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.
>
> =====================================================
>
> 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==
> | X-WBNR-Posting-Host: 24.73.223.27
> | From: "=?Utf-8?B?VGRhclRkYXI=?=" <Tdar@noemail.nospam>
> | Subject: xp_cmdshell, Access Denied, Further Investigation Reveals
> | Date: Thu, 13 Oct 2005 10:00:11 -0700
> | Lines: 26
> | Message-ID: <C9ABAD68-E537-46BD-BE4B-196CFB9A277F@microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.security
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.security:6263
> | X-Tomcat-NG: microsoft.public.sqlserver.security
> |
> | 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??
> |
> |
> |
> |
>
>

Relevant Pages

  • Re: Batch File in DTS
    ... I assume that you are running SQL Server 2000. ... is run as a system administrator) or else of the SQL Agent proxy account. ... This works in DTS package, but when I schedule the package, ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL Agent Non SysAdmin Job Proxy User Account
    ... "Unable to set the SQL Agent Proxy Account because of the reason ... "How to configure a SQL Server Agent proxy account to enable ...
    (microsoft.public.de.sqlserver)
  • Re: Can SQL Users have Access to A Win2k Share?
    ... they can use so-called proxy account for the xp_cmdshell operations. ... Dejan Sarka, SQL Server MVP ... > rather just the SQL user info. ...
    (microsoft.public.sqlserver.security)
  • Re: Operating System Command (CmdExec) will not run from Job Agent
    ... I created a proxy account, from what I understand SQL Server should only need ... Having created a proxy account who is a memeber of the local admin group I ...
    (microsoft.public.sqlserver.dts)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I moved every table I was able to move to the SQL ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... communication between ODBC (OLEDB and Native Client, ...
    (microsoft.public.sqlserver.connect)