SQL Server Performance

xp_cmdshell problem 'Access Denied'

Discussion in 'General DBA Questions' started by sonnysingh, May 28, 2007.

  1. sonnysingh Member

    Hi Folks

    I require to copy backup files from one server to another. I am usind xp_cmdshell command. I have tried most of the suggestion available on these sites.


    http://www.sqlservercentral.com/columnists/bknight/xpcmdshell.asp
    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=276120&post=true
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77181
    http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm

    but when run exec master..xp_cmdshell 'dir c:*.*' then it work fine but when I run this command
    exec master..xp_cmdshell 'copy c:*.bat \backupmine'
    with my windws authentication as well SA accounts it's show 'Access Denied'. I have shared rights on target server with same windows authentication. I have also tried this method with SQL Agent Proxy setting http://support.microsoft.com/kb/890775. But no luck..

    guys what I am missing???

    Thanks in advance,
    sonny

  2. satya Moderator

  3. sonnysingh Member

    First of all accept my apology for incomplete content of post by mistake..

    Windows 2000 Server with SP4..no satya.. when I activate using sp_configure it's say "The configuration option 'xp_cmdshell' does not exist or...." I also gave rights to execute it for system account..I have looked at refered site by you and most of the csiggested points are fine but I do not got this part
    If you are using a Local System account for SQL Server services with the local security policies enabled on a domain, then ensure to start the SQL Server services with a Network Service account where the Administrator login will be used to perform such command-line operations in SQL Server..

    Thanks
  4. satya Moderator

    That XP_CMDSHELL is for SQL 2005 and not in SQL 2000, as I have realised yours in SQL 2000 version.

    If it is the local system account then look at the local security policies that WINDOWSSYSTEM32 access is not restricted to
    Everyone group (atleast it should have read) for other windows related commands & statements.

    Network service account is introduced from Windows 2000 server and if you look at Administrative tools --> Services and look for relevent SQL Server services to startup options.

    Coming to your initial problem ensure that \backup share exists and SQL Server service account has relevant privileges to access that share too.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. sonnysingh Member

    satya..
    I have checked the services.. and SQL Server 2000 running undet local system and I have logged through Network account. I have also tried to run this job under SA account and ran fine with succeeded but file was not copied...why this?

    so in this case I think following check need to require as you mentioned..

    quote:If it is the local system account then look at the local security policies that WINDOWSSYSTEM32 access is not restricted to
    Everyone group (atleast it should have read) for other windows related commands & statements.

    could you please guide accordingly..

    Thanks..
  6. sonnysingh Member

    satya.. as SQL Server running under Local System, Technical department have ownership of it means I need to ask one of their person get the rights on destination folder that is not seems to be comfortable.
    is there any way to solve thi without technical department intervention???? please suggest..

    Thanks..
  7. satya Moderator

    If there are such security policies then there isn't any way out, if you have admin rights on the server then you could do that.
    But as this is on the production server I wouldn't suggest to take any shortcut to dent any security policies on your environment.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page