locked
Backup fail --Access is denied. RRS feed

  • Question

  • Esteemed listers,

    I have failed some of backup jobs with below error message. I've tried many different ways but couldn't fix it. Could you give me some advice?

    "Executed as user: COMP\XXXXX. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:07:39 AM  Progress: 2010-03-16 11:07:41.99     Source: {91EBDCB5-4E9D-4945-ABA7-57BB23DA6176}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Error: 2010-03-16 11:07:43.46     Code: 0xC002F210     Source: Backup DB Execute SQL Task     Description: Executing the query "BACKUP DATABASE [TO1] TO  DISK = N'\\server5\mitsd..." failed with the following error: "Cannot open backup device '\\server5\mitsdb_backup$\TRG\XXXXX\TO1\TO1.bak'. Operating system error 5(Access is denied.).  BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:07:39 AM  Finished: 11:07:43 AM  Elapsed:  4.313 seconds.  The package execution failed.  The step failed."
    Tuesday, March 16, 2010 3:20 PM

Answers

  • If your SQL server is running under a local account or system account (local system , nt authority) then this job will not succeed as you are trying to take backup on a network folder where your service doesn’t have access. In this case , you should try to modify your package / job to backup the databases in local drive.

    If your SQL server is running under a domain account , then make sure SQL services DOMAIN account has access to the above mentioned shared drive.


    Thanks, Leks
    • Proposed as answer by Glenn A. Berry Wednesday, March 17, 2010 2:59 AM
    • Marked as answer by Tom Li - MSFT Tuesday, March 23, 2010 9:17 AM
    Tuesday, March 16, 2010 8:59 PM
    Answerer

All replies

  • The SQL Server service account does not have access to "\\server5\mitsdb_backup$\TRG\XXXXX\TO1\TO1.bak".  Normally $ shares are only used by Domain Admins.

    Tuesday, March 16, 2010 3:22 PM
  • Yes, the folder name 'XXXXX' after TRG ends with '$'. Doesn't it have access still?
    Tuesday, March 16, 2010 3:25 PM
  • I take it 'COMP\XXXXX' is a local machine account used to start the sql server service, and that the \\server5\mitsdb_backup$ share is on a remote computer.

    You should start the sql server service with a domain account, and give it permissions to access the network share.
    Tuesday, March 16, 2010 3:56 PM
  • If your SQL server is running under a local account or system account (local system , nt authority) then this job will not succeed as you are trying to take backup on a network folder where your service doesn’t have access. In this case , you should try to modify your package / job to backup the databases in local drive.

    If your SQL server is running under a domain account , then make sure SQL services DOMAIN account has access to the above mentioned shared drive.


    Thanks, Leks
    • Proposed as answer by Glenn A. Berry Wednesday, March 17, 2010 2:59 AM
    • Marked as answer by Tom Li - MSFT Tuesday, March 23, 2010 9:17 AM
    Tuesday, March 16, 2010 8:59 PM
    Answerer