We have been seeing issues with the SQL plugin not being able to backup databases on SQL server 2012 systems. This appears to be an issue with the permissions of the account the agent is running as. The job does not fail, but completes with errors, and does not actually back up the SQL databases.
The error message in the message logs for the job are usually something like the following:
"SNFCSQL2012-FileIO JobId 9929: MSSQL: error: can't get configuration for backup of database 'distribution' on DEFAULT server instance: GetConfiguration timed out after 300000ms
The requested backup cannot be completed by the SQL Server because the log chain has been broken. This is likely caused by a conflict from another tool or service."
It seems that SQL 2012 has additional security requirements to enable our plugin to run a backup that were not present in earlier versions of SQL. Depending on your SQL security settings there may be a number of places that you will have to change permissions in order to allow backups.
**Note:** Since our agents run as the local system account by default the instructions below will reference the NT Authority\System account. If you are using a different account to run the agent then change these settings for that account. These instructions also assume that you have the agent set up according to the normal set up instructions and have the SMOs and the NCLI installed, but are getting the error above.
First log into the SQL Management studio and drill down into Security > Logins. Under Login right click on the NT Authority\System account and select properties, which should launch the login properties window. In this window click on Server Roles, and make sure that both the Sysadmin and Serveradmin roles are checked. Click OK to close the window and attempt another backup.
If you are still getting the error after setting the roles above you will next need to log into the management studio again. Right click on the server at the top of the tree and select properties which will bring up the server properties window. In this window select Permissions from the list on the left, and select the NT Authority\System account from the list in Logins or Roles. In the Permissions For frame check the grant column for all of the allowable permissions, verifying that none of these permissions have deny checked. Click OK to close the window and attempt another backup.
At this point if the backup is still getting this error then it is not likely a permission issue. Most of the time you can skip making the individual changes to the security settings above by setting the agent to run as your SQL administrator account. To do this log into the agent configuration on the SQL server, browse to the Service Control tab, stop the service, then enter the username/password of the account, then start the service again.