Error: SQL Server Maintenance Plan, Database Backup Job

Description:

When executing a SQL Server maintenance plan, having a database backup job, an error occurs. The issue is that the SQL Server Agent service account does not have access to the backup folder. This happens more frequently on backup folders which exist on a separate server, such as a backup server.


1. First, navigate to SQL Server Management Studio > Maintenance Plans > Right click on maintenance plan > View History.

You might see something like this in the history:

"Action: Execute maintenance plan (full backup of all SQL databases). ErrorMessage: Execution failed: See the maintenance plan and SQL Server Agent job history for details. Additional Information: Job 'Backup SQL' failed. (SqlManagerUI)"


2. Next, navigate to the history log for the SQL Server Agent job:

SQL Server Management Studio > SQL Server Agent > Jobs > Right click on job > View History > Highlight the error > Click Export > Export a log file > Open the log file in a text editor

...and you might see entries like this:

"Reason for failure: " failed with the following error: "Cannot open backup device 'backup device name'. "
"Error information: Operating system error 5(Access is denied.)."


Solution:

The solution to this is to configure the appropriate access for the SQL Server Agent service account on the folder security access control list and the folder sharing access control list.

However, the catch here is that you may need to add the service account directly to the access control list, as opposed to an AD group in which the user account belongs. In fact, I tested this by doing the steps below.


1. Deleted the maintenance plan and created it again.


2. Added the backup folder location, and clicked Next.

This brought up an error:

"Maintenance Plan Wizard: The Database Engine service could not resolve the specified folder location. Either the location does not exist, or the current login account does not have access to it. Verify that the folder exists, and that the login account has permissions on it."


3. Added the SQL Server Agent service account directly to the backup folder's access control lists for folder security and folder sharing. After doing this, the creation of the maintenance plan was successful.