In SQL Server 2012 onwards, the cluster health check detection logic has been enhanced. Instead of the traditional pull mechanism of the cluster (IsAlive and LooksAlive), SQL Server (version 2012 onwards) uses a push mechanism to detect the health of the SQL instance. This is done by a special stored procedure called sp_server_diagnostics. We should remember that the failover mechanism for the AlwaysOn FCI and the AlwaysOn Availability Groups is same.
While troubleshooting, it is very important to know which log to look at along with the basics of a feature. Recently, one of my clients was having trouble in bringing SQL Server AlwaysOn availability group resource online.
Whenever I am stuck with a cluster related issue, I always look at cluster log. You can refer my previous blog about how to generate cluster logs. SQL SERVER – Steps to Generate Windows Cluster Log?
Now let us change see the error logs.
Now let us inspect them heavily and you will find following error.
The User Does Not Have Permission to Perform this Action. (297)
I have removed a date-time column from the output to provide the clarity. If we look at series of messages, you would notice that cluster has made a connection to SQL Server. After this, it executes below statement
exec sp_server_diagnostics 10
As we can see in the next line that this execution failed with error.
The user does not have permission to perform this action
Due to the above error diagnostic health check failed and SQL Server will not be able to come online in a cluster. The same error can also appear in the AlwaysOn availability group as well.
Now the real question is which user? And how to fix this issue?
WORKAROUND/SOLUTION
The account which is used to connect to SQL Server from a cluster is a local system account. My client informed that due to hardening they have modified default permissions in SQL Server.
To fix this issue, we can add VIEW SERVER STATE permission to the SYSTEM account.
use [master] GO GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM] GO
Once done, the issue was resolved and SQL came online in the cluster as well.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
