The scenario.

In an effort to secure your SQL Server 2005 instance, you have removed the BUILTIN\Administrators group from your clustered SQL Server instance. You have done this before granting any other accounts\groups a login to SQL Server. Even before you logout or cycle the services, you will possibly see the message below if your current login relies on this group for access (in my case domain administrator).

The error dialog above appears because the account you authenticated to SQL Server with relies on the Builtin administrators group. Before you remove the builtin group, ensure you have at least one account\group added to the Sysadmin role along with the cluster service account and the SQL Server services service account(s).

Once you cycle the SQL Server services you are essentially locked out. As the Windows cluster administrator attempts to bring the services back online, the cluster service account connects to the SQL Server instance to perform the "IsAlive" check. The authentication for this check takes place through the BUILTIN\Administrators group. The "IsAlive" check cannot take place because you removed the group as a server login, the instance will not start, not even from the command line (as you could with a non clustered instance).

The following events will be present in the event logs confirming the problem (events 19019 and 18456), the cluster service account can no longer login to the SQL Server instance.

The boss wants to know what's happening and management is screaming for blood. Don't panic or start typing your resume just yet, there is a life saving action available.

Cast your mind back to when you installed your SQL Server instance. You created 3 Active Directory groups, a non clustered instance creates these groups as local groups but with clustering they are Directory Services bound. These groups like their non clustered counterparts have provisioned access into the SQL Server instance. Mine are shown below

Contact the domain administrator making promises of free beer for the next 3 months and beg him to put the cluster service account into one of these groups, it doesn't matter which one as they all have provisioned access into the SQL Server instance. This is all the "IsAlive" process requires to connect.

You can then reboot the server and when you login to the cluster administrator, the SQL Server service should be back online. Add the cluster service account immediately to the server logins along with the SQL Server service account(s) for the SQL Service and its agent. You can then remove the cluster service account from the AD group and your cluster should be fully operational.

It is important to fully realize the implications of removing BUILTIN\Administrators before doing so and to make sure you also grant the NT AUTHORITY\SYSTEM account a login into the Sysadmin role too. For more details on the points above and the process of impeding Windows administrators on SQL Server see the following link: http://support.microsoft.com/kb/263712

Watch out for my next article coming soon, which details how to create a virtual Windows 2008 cluster.