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 SQL plugin not being able to back up databases on Microsoft SQL server 2012. This appears to be an issue with the permissions of the account the agent is running as. The job doesn’t fail, but completes with errors, and doesn’t actually back up the SQL databases.
The requested backup can’t 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.
Steps to resolve
It seems that SQL 2012 has additional security requirements to enable our plugin to run a backup that weren’t present in earlier versions of SQL. Depending on your SQL security settings there may be a number of places that you’ll have to change permissions 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’re 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.
In SQL Management Studio, go to Security > Logins.
Under Login. right-click the NT Authority\System account, and then select Properties
In the Properties window, click Server Roles.
Make sure both the Sysadmin and Serveradmin roles are selected.
Click OK to close the window, and then try new backup.
If you’re still getting the error after setting the roles:
Sign in to the SQL Management Studio.
Right-click the server, and then select Properties.
In the Properties window, select Permissions, and then select the NT Authority\System account from the list in logins or roles.
In the Permissions group, select Grant for all allowable permissions, verifying that none of these permissions have deny the selection.
Click OK to close the window, and try new backup.
At this point, if the backup is still getting this error, then it’s 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.