Share via

SQL Server 2016: Add database to availability group failed: Access denied

  • Article

Problem

You are remoted into the primary replica of a two-SQL Server failover cluster employing a file share witness.  The failover cluster serves as the backend to a SharePoint 2016 farm.  The cluster is up and healthy.  The Availability Groups dashboard shows both replicas synchronized and all user databases also synchronized. You temporarily removed the SharePoint farm's Usage and Health database from the availability group while executing PSCONFIG on farm servers.  You now need to add it back. You've already made a full backup of the database and set its recovery model back to Full. You have performed this procedure for at least six months previously without issue.

In SSMS, you first connect to the secondary replica and delete the Usage and Health database from the secondary replica.  You then connect to the primary replica and initiate the Add Database wizard.  The first Panel, Select Databases, listed the Usage and Health database with status Meets prerequisites.  Later, working through the panels, the Validation panel showed the database meeting all prerequisites.  At the Summary panel, you launch the process to initiate full database and log backup data synchronization.

While waiting for the wizard to complete, you open Explorer and browse to the location of the file share.  You note that the backup file was created initially but not fully populated. After about 15 minutes, the wizard finishes with errors. It fails at the process: Backing up log for [SharePoint Usage and Health database].  Reviewing the error, it reads

Backup Database [SharePoint Usage and Health database] Log fail (Microsoft.SqlServer.Management.HadrModel)...System.Data.SqlClient.SqlError: Cannot open backup device...Operating system error 5(Access is denied).

Solution

  • Check to make sure that the SQL Server service account has Full Control to the folder used as the file share. 
  • Changes in GPO configuration implemented by systems and security administrators may inadvertently remove this permission or even the account from access to the network share.  Granting the service account read and write permission to the folder will be insufficient.

References

Notes

  • What was confusing initially was that the SQL Server service account was in fact able to write to the folder so as to create the initial file footprint.  Why it could not complete this is still unclear.  In any case, granting the account Full Control to the folder solved the problem.