Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Clustered instance will not start after removing builtin admins

By Perry Whittle,

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.

Total article views: 1445 | Views in the last 30 days: 2
 
Related Articles
BLOG

Assign SQL Service Account with Group Policy

How do I assign and enforce a service account for the SQL Server Services with Active Directory Grou...

FORUM

SQL Server service running under non admin account in cluster environment

Make SQL Server run with non admin account on cluster server.

FORUM

Sql Server 2005 Clustering

Cluster Service Account is being used by sql server

FORUM

Chaning SQL Cluster Service Account Passowrd

How to change the password for multiple cluster service and SQL instances running with same service ...

FORUM

SERVICE ACCOUNT PASSWORD CHANGE ON CLUSTER INSTANCE

URGENT HELP= SERVICE ACCOUNT PASSWORD CHANGE ON CLUSTER INSTANCE

Tags
builtin\admins    
clustering    
impede    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones