I am using SQL Server 2012 and have created a Maintenance Plan to backup user databases nightly to a folder on the hard drive. However, when this plan is executed it fails with an "Access is denied" error.
Here are the details:
- I created a Maintenance Plan, adding a "Back Up Database Task" task that backs up all user databases.
- I verified that the plan is set to run as "SQL Server Agent service account"
- When I go to SQL Server Configuration Manager and view the Properties fo rthe SQL Server Agent under the Log On tab it shows the NT Service\SQLSERVERAGENT as the account being used for this service.
- I created a folder on the hard drive to store these backups named C:\SQLBackups and gave full control to the NT Service\SQLSERVERAGENT account
- I configured the Maintenance Plan in Step 1 to store to C:\SQLBackups, creating subfolders for each database.
When I run the Maintenance Plan I get the following error:
Executing the query "EXECUTE master.dbo.xp_create_subdir N'C:\SQLBackup..." failed with the following error: "xp_create_subdir() returned error 5, 'Access is denied.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I grant Everyone full access to the C:\SQLBackups folder then the Maintenance Plan runs without issue.
What accounts must have access to this folder for the backup to work? I presumed I'd only need to grant access to the NT Service\SQLSERVERAGENT account, but clearly that is not working.