Database Backup Failed in Protect with "Generic data access layer exception."

Version 5


    Symptoms

     

    Backups setup in the Database Maintenance feature of Protect are not completing.

     

    In the Shavlik event history (View > Event history), users will see the following error message:

     

    Error message event vieweredit.PNG

     

    "Generic access data layer exception. SQL exception message: Cannot open backup device "C:\..." Operating System Error 5 (Access is denied.)."

     

    This error should also be found in similar wording in the ST.ServiceHost.managed.log file on the console.

     

    2015-01-05T06:00:04.4727735Z 0019 E BackupDatabaseTask.cs:155|Generic data access layer exception. SQL exception message: Cannot open backup device 'C:\...'. Operating system error 5(Access is denied.).

    BACKUP DATABASE is terminating abnormally.

    Cannot open backup device 'C:\...'. Operating system error 2(The system cannot find the file specified.).

    VERIFY DATABASE is terminating abnormally.

    SqlError message: 'Cannot open backup device 'C:\.... Operating system error 5(Access is denied.).', source: '.Net SqlClient Data Provider', server: '(local)\SQLEXPRESS', number: 3201, state: 1, procedure: 'BackupDatabase', line number: 19.

    SqlError message: 'BACKUP DATABASE is terminating abnormally.', source: '.Net SqlClient Data Provider', server: '(local)\SQLEXPRESS', number: 3013, state: 1, procedure: 'BackupDatabase', line number: 19.

    SqlError message: 'Cannot open backup device 'C:\...'. Operating system error 2(The system cannot find the file specified.).', source: '.Net SqlClient Data Provider', server: '(local)\SQLEXPRESS', number: 3201, state: 2, procedure: 'BackupDatabase', line number: 46.

    SqlError message: 'VERIFY DATABASE is terminating abnormally.', source: '.Net SqlClient Data Provider', server: '(local)\SQLEXPRESS', number: 3013, state: 1, procedure: 'BackupDatabase', line number: 46.

      at ST.Data.Services.Maintenance.DbMaintenance.EndInternal(IAsyncResult asyncResult)

      at ST.Services.Administration.Maintenance.BackupDatabaseTask.BackupComplete(IAsyncResult asyncResult)

     

     

    Cause

     

    There are insufficient file permissions on the backup directory on the Shavlik console machine. The operation while committed through the application is performed by the SQL server itself. If the account the SQL service is running under does not have sufficient file privileges to read and write in the database backup directory, the backup operation will fail.

     

     

    Resolution

     

    The Shavlik Protect help documentation notes of the file permission requirements for this backup operation specifically under the section Backup database and transaction log:

    "If you specify a UNC path to a location on SQL Server, your SQL Server account must have access to the path. If a built-in account is being used (such as Local System or Network Service) then the machine account needs access to the path."

     

    To resolve this issue, you must ensure that the directory of the backup has sufficient privileges to perform the backup operation. The following step-by-step directions will help show how this can be done.

     

    1. Determine the account the SQL service runs under.

     

    To determine the account the SQL service runs under perform the following on the machine that host the database :

    Click Start > Run and type services.msc and hit Enter.

    Locate the SQL Server service and Right-click on it and select Properties.

    Select the Log On tab, and next to "This account" there should be an account listed- this is the account the SQL service uses and which needs rights over the file directory of the backups.

    SQL account.PNG


    In this case this account is "NT Service\MSSQL$SQLEXPRESS"

    For the next step- copy and paste that account.

     

    2. Provide this account with Read/Write permissions on the specified database backup directory in Protect.

     

    To obtain/specify the file path of the database backup in Protect, go to Tools > Operations > Database maintenance. You can specify the directory under SQL server, specifically under the check-box, "Backup database and transaction log" below "UNC or local path" as shown below.

     

    dbbackup.PNG

     

    To provide the SQL account the proper permissions to this directory, navigate to the path in the file explorer.

    Right click the folder, in this example it is "C:\dbbackup", and click Properties

    Select the Security tab, and click the button "Edit..." to modify the permissions

    Click "Add..." to provide file permissions for the SQL service account.

    In the Select Users or Groups window, copy and paste the account- in this example "NT Service\MSSQL$SQLEXPRESS" and click OK.

    Ensure that the account has both Read and Write permissions, as follows:

    permissions.PNG

     

    After completing these steps, reattempting to perform the backup using Protect should complete successfully. To confirm, return to the event history and you should see a status of Success on the backup task.

     

    aftereventhistoryedit.PNG

     

     

    Additional Information

     

    SQL Database Maintenance Recommendations for Protect

    How to create a backup of the database with Protect

     

     

    Affected Product(s)

     

    Shavlik Protect 9.x