As per our security guidelines Builtin\Administrator login should be removed from all the SQL Server instances.It was implemented on all the SQL Server instances including those which are on MCSC (Windows Cluster).
After that, the nodes were rebooted due to patching requirements .The nodes came up , but SQL Server did not :D ...
Error in cluster logs (you will not find it in SQL Server logs) :
ERR SQL Server
ERR SQL Server
The error was clear .The cluster service was not able to login to SQL Server through user XXXXX\clusterservice but via a LOGIN ...That login is BUILTIN\Administrators.
But why it needs to login to SQL Server ?? Because it needs to run the isAlive check to make sure that the SQL Server is up and running .It also runs the looksalive (its a function)check but that does not need to query SQL Server .Is Alive check runs select @@servername and waits for the return message through ODBC client (in our case its SQL Server Native client).Thus the Isalive check was not able to create a trusted connection and we lost the access to Virtual server.
So, in a SQL Server 2005\2008 failover cluster installation, the cluster service account relies on membership in the BUILTIN\Administrators group to log on to SQL Server 2005\2008 to run the IsAlive check.If you remove the BUILTIN\Administrators group from a failover cluster, you must explicitly grant the MSCS service account permissions to log on to the SQL Server 2005 failover cluster.
The SQL Server 2005 resource starts an instance of the Sqlcmd.exe utility under the security context of the MSCS service account. Then, the SQL Server 2005 resource runs an SQL script over a dedicated administrator connection (DAC) that samples various dynamic management views (DMV). Because a DAC connection is used to collect some diagnostic data, the clustering service account must be provisioned in the SYSADMIN fixed server role. If later someone says that clustering service account cannot be provisioned in the SYSADMIN fixed server role, then we can create a login for cluster service account that is not given the SYSADMIN fixed server role .I have not tested it yet .So cannot confirm that this will work on not ...
CREATE LOGIN [
EXEC master.sp_addsrvrolemember @loginame = N'
happly learning .....