Local Groups in Cluster
-
I am trying to standardize our SQL Server security using MS recommended method of Domain Users -> Domain Groups -> Local Groups -> SQL Permissions. I hit a brick wall when I try to implement this in the cluster since the local system groups in the cluster will not work when the cluster fails to the other node. Otherwise I need to make local groups on both the nodes and assume which ever node the cluster is running on it will have the permissions OR add the domain group directly to the SQL server and grant permissions directly to the group. Any one has gone through this and how did you resolve this?
thanks.
-
In clustered SQL Server environment, The IsAlive thread runs under the context of the cluster service account which have to be able to log into SQL Server for the IsAlive check and must have sysadmin rights to the SQL Server. The cluster service account has to be a domain account.
-
In our cluster all the access is granted via membership of domain wide groups. We don't use groups local to the cluster member nodes at all.
Tony -
The previous post was partially correct in that the cluster account should be granted login privilege inside SQL Server. But it need not have sysadmin privilege and we have tested this and it work fine without sysadmin privilege.
-
The cluster account should be made a member of the sysadmin role.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;263712
K. Brian Kelley
@kbriankelley -
This raises a good question. I hear less and less about using Local groups and see more and more people assigning access to local resources directly to global security groups. Has Microsoft changed the old, Domain Users -> Domain Groups -> Local Groups -> SQL Permissions rule of thumb? What is the advantage of granting SQL permissions to a local group rather than to a global security group?
-
They haven't backed off on the OS side... however, from a SQL Server perspective I lean towards global groups when I'm considering a rapid recovery situation... here's why. A Windows group has a SID whether it's domain or local. If I have to take a database and restore to a different server (hardware issues with the first, for instance), I want to get back and running right away. The SID for the users in the database need matching up. If I'm using global groups, no problem. I grant the login and away we go. If however, we're using local groups, the group will need to be created on the database server, and then you'll have to run sp_changeuserslogin to reconnect everything. The stored procedure isn't the biggie. But the members of the local group might be.
K. Brian Kelley
@kbriankelley -
I see some value in Brian's argument. Nevertheless, my original question was whether or not local groups can be used at all in the cluster server implementation for granting SQL permissions. Looks like NO is the answer and that we should use the domain groups directly.
Now coming back to the auxiliary question of whether or not we should use the local groups at all in the SQL server, I still would like to use the local groups to grant permissions. This is because, in our environment, we have a fixed set of users/developers who access the servers, then there are folks who come and go (like consultants, support folks) who need access for few weeks. In this case I can add/drop them in the local group and be done with it. No need to submit requests to the folks who administer the domains to add and drop users in the domain groups. As far as addressing recoverabilbity is concenred, if we script out the part to create local groups and synch them up in the SQL Server, that would do it right? All this argument is valid only in the context of non-clustered servers, since in the clustered servers, you do not have the option to use the local groups anyway. So the decision has already been made (domain groups).
-
For clusters, no, you don't want to use local group. The groups, while named the same to you and me, will have different SIDs on each node of the cluster. Also, should you try to add the name of the local group while it resides on the other node of the cluster, I believe you get an error because it can't find the group in question. The only way to get around this is to use domain groups. In non-clustered cases you don't have to worry about this and local groups work just fine.
As for folks coming and going, one of the issues I see with what you're doing is you're handling security in more than one place... meaning something could get lost in the handshake. If you're creating local groups and the folks administering the domain are creating global groups, you could end up with both groups creating groups for the exact same reasons. Not exactly a clean solution and one likely to get cited on an audit. It's typically best to allow the domain folks to create and place people in domain global groups. You create local groups and assign those global groups to them. Then as the folks are rotated in and out, the logins will be granted and revoked login rights as a result. If you're building groups based on roles (using role-based security beyond SQL Server), this works out rather well.
K. Brian Kelley
@kbriankelley -
I have a very close relationship with my domain admins, so they have given me account administration rights. I only create groups in my OU's and edit group membership. We report to the same manager, so that makes it easier.
-
I am wondering if you have to install or setup each windows group on the O\S side on each server in a clustered environment or does the heartbeat see the security on the primary and automatically keep the same security on the secondary?
-
The heartbeat only verifies that the SQL Server can be connected to. It doesn't handle keeping local groups in sync. Nothing is responsible for doing so within the operating system.
On a side note, Microsoft has changed the recommendation and is no longer stating that the cluster service account be a full blown sysadmin for SQL Server. It just needs the ability to log on.
K. Brian Kelley
@kbriankelley -
The best way and actually the only way that Window Authentication will work so that a user in a group will have access to the server when it fails over is to get Domain groups created, add them into SQL and then assign permissions to the groups.
I have tested this with SQL 2000 and it does work.
The hard part is getting the domain groups created and assigning all the users to the groups, if you are not allowed access to create domain groups.
-
If role-based (or task-based) security is used as the model, then those groups would already exist and all you would have to do is add them. If your security account administrators haven't looked into that model, point them in that direction.
K. Brian Kelley
@kbriankelley
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply