Access Denied to backup but xp_cmdshell no Acess Denied

• pascalbjr

  Mr or Mrs. 500

  Points: 528

  More actions

  February 6, 2010 at 6:34 am

  #210699

  Hi,

  I'm trying to backup to a network drive, but I keep getting the message below:

  Cannot open backup device '\\Server\Projects\Exodus\Backup_Banco'. Operating system error 5(Access is denied.).

  The weird thing is that if I use xp_cmdshell on the same folder, I can dir, copy files, etc... Why is that?

  Below is by backup command code that I get the Access Denied:

  BACKUP DATABASE Exodus

  TO DISK = '\\Server\Projects\Exodus\Backup_Banco'

  WITH

  COPY_ONLY,

  COMPRESSION,

  NOFORMAT,

  INIT

  And both xp_cmdshell commands below works without a glitch:

  exec xp_cmdshell 'dir \\Server\Projects\Exodus\Backup_Banco'

  exec xp_cmdshell 'copy \\Server\Projects\Exodus\Backup_Banco\text.txt \\Server\Projects\Exodus\Backup_Banco\text4.txt'

  Both SQL Server and SQL Agent are running on domain accounts, and I gave Modify rights on the \\Server\Projects\Exodus\Backup_Banco folder to the Domain Local Group that holds both accounts, as the copy commands shows.

  Both commands use SQL Server's service account rights, so I should get the same treatment for both commands. But that's not what is happening.

  Any ideas why?

  Thanks

• pascalbjr

  Mr or Mrs. 500

  Points: 528

  More actions

  February 6, 2010 at 12:57 pm

  #1115069

  The file name was missing... stupid mistake... focusing on the complex things, and missed the simple things...

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 18, 2010 at 6:55 pm

  #1121521

  I see that you posted this issue on another forum and the problem was resolved:

  http://social.msdn.microsoft.com/Forums/en-IE/sqlsecurity/thread/ecec679e-1e50-4eff-beb1-283fb0fb8f54

  Sorry but I missed your reply...

  This Post should be closed.

  Regards 🙂

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 18, 2010 at 7:05 pm

  #1121526

  You should disable the xp_cmdshell unless absolutely needed:

  http://www.sqlservercentral.com/Forums/Topic828767-386-1.aspx

  http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123821

  http://blogs.msdn.com/sqlsecurity/archive/2008/01/10/xp-cmdshell.aspx

  Respectfully,

  Welsh

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• pascalbjr

  Mr or Mrs. 500

  Points: 528

  More actions

  February 19, 2010 at 2:42 am

  #1121647

  I did disable xp_cmdshell after my test.

  And I posted back the solution I received from the other forum... Is this what you mean by closing the topic?

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 19, 2010 at 5:08 am

  #1121727

  Yes, thank you. 🙂

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• pascalbjr

  Mr or Mrs. 500

  Points: 528

  More actions

  February 19, 2010 at 5:17 am

  #1121733

  Ok. I was worried there for a second.

  Tks for the tips!