LooksAlive and IsAlive checks by Microsoft Cluster Service

When an instance of SQL Server is running in a Clustered Environment, Microsoft Cluster Service performs two checks to confirm if the SQL Server resource is still available. One is LooksAlive and the other one is IsAlive check.

LooksAlive is a basic check in which the Cluster service queries the Windows Service Control Manager to check if the SQL Server service is still running. By default this check happens every 5 seconds. During IsAlive check the Cluster Service connects to the SQL Server instance with the help of c:\windows\system32\sqsrvres.dll and runs SELECT @@SERVERNAME against the instance. This check does not check if the user databases are online or not. It just checks the SQL Server instance availability. This check happens every 60 seconds by default.

During the IsAlive check the Cluster Service connects to the SQL Server Instance. What privileges does the Cluster Service Service Account needs to have on the SQL Server instance?

The cluster service account needs privileges sufficient to execute SELECT @@SERVERNAME command against the SQL Server instance. Any user who has Public rights on the instance can execute this query. By default, the Cluster Service startup account is part of the Local Administrators group on the cluster nodes. Until SQL Server 2005, the Builtin\Administrators group was granted SA privileges on the instance during SQL Server installation. Hence the Cluster Service startup account had System Administrator privileges on the instance. Usually during the hardening process it is a common practice to “revoke” access to Builtin\Administrators login on the instance. If this is done on an instance running in Clustered environment, the cluster service service account will not have access to the instance and as a result the SQL Server resource will not come online after hardening! Similar issue occurred in some other team in my organization. They tightened the instance too hard. The issue was resolved after the Builtin\Administrators login was granted Public or higher privileges on the instance.

On a closing note, starting Windows Server 2008 the Cluster Service does not use a domain account to start, by default it uses the Local System Account (NTAUTHORITY\SYSTEM). One should be careful not revoke the access for NTAUTHORITY\SYSTEM login on an instance running on Windows Server 2008.

@Maha, hope this post answers your question.

6 thoughts on “LooksAlive and IsAlive checks by Microsoft Cluster Service

  1. Maha

    Heartfelt thanks Pradeep. The second half of the post was more informative. But I am sorry, questions creep coming in….how does BUILT IN\Administrators & NT Authority\SYSTEM differs in a normal machine(/Clustered machine)…?

    1. PradeepAdiga Post author

      Thanks Maha. The “Local Administrators” group on Windows has a login with the name “BUILTIN\Administrators” in SQL Server. NTAUTHORY\SYSTEM is windows user who is part of “Local Administrators” on Windows. I had talked about it here

      In short “BULTIN\Administrators” is a windows group and NTAUTHORITY\SYSTEM is a user member of that group.

  2. karthik

    As always,very informative Pradeep. But i would like to know,cant we add the cluster service as a login and grant the necessary privileges to it,instead of giving the Builtin\Administrators group ,the privileges?

    1. PradeepAdiga Post author

      Thanks Karthik. Best practice is to grant the Cluster Service service account explicit login privileges in SQL Server. This post was targeted towards the installations where there is no explicit login granted to service account and it inherits the permission from Builtin\Administrators login.

  3. Prabodh

    Hi pradeep..could you please quote an situation where Looksalive creeps through but Isalive gets failed apart from the permission issues for cluster admin

    1. PradeepAdiga Post author

      Hi Prabodh,

      I cannot think of any situation apart from the permissions issue of Cliuster Servoce Account, where the LooksAlive check is successful but IsAlive fails.

Comments are closed.