NEVER use NT Groups to control SQL Server Permissions
About a year I go I looked at the use of Windows Group as a mechanism for controlling access to SQL Server for a PCI-DSS project. PCI requires that all access be done by accounts that uniquely identify the user. Best practices mandate Integrated Security so we must use Windows Users. There were two apparent solutions:
- Associate each Windows user to appropriate SQL Login
- Associate each Windows user to a Windows Group that is associated to a SQL Login
Since the expected administrators of the PCI application are not DBA’s and may not be SQL Server knowledgeable, the second approach looked ideal. Their role is to determine who may or may not access the application.
This simple idea had a nasty gotcha:
- Grant SQL permissions to anyone in the Windows Group.
- This work perfectly for granting permissions
- I expected that if a user was removed from the Windows Group, permissions would be immediately removed.
UNFORTUNATELY, if a Windows user was removed from the Windows Group, the user still retained SQL permissions.
Some Bingoogling found that the issue is known in another context and cited on an official Microsoft Site:
“Note If you use SQL Server integrated security, keep in mind that if you grant a Windows NT user group access to the SMS site database, this permission is not dynamic. As new users are added to the Windows NT user group, they are not given SQL Server security rights unless you add them individually.”
Hence the apparent behavior of granting permissions to users in the current Windows group (a snapshot) appears to be confirmed. So this preferred approach totally collapses:
- When the association is originally done, it grants all NT Users the specified permission. The User Group is nothing more than a temporary container.
- Adding a user to the User Group later will NOT result in permissions being given.
- Removing a user to the User Group later will NOT result in permissions being revoked.
In practice, when someone adds a user to the NT Group they will expect it to work. When the user complains, the admin will assume something went wrong and manually add the user’s SQL Permission. When they remove a user from the NT Group, they will also expect it to work – after all, looking under Security, they not see the user, just the NT Group; the user will not complain – and you have a security breach.
Bottom line: The Domain/Windows Users must be individually added to and removed from SQL Server (for example mapped to a specific SQL Login). Never use a NT Group account to control permissions is SQL Server. If you are ever called on to audit a system, check if any NT Groups are used – if so, feel free to go berserk (it is totally justified)!
Will it be fixed? Is this a bug?
Including the ability to assign Windows/Domain Group to a SQL Login is the bug. A Domain/NT Group can consist of Domain/NT groups – so doing a dynamic permission could means every query may require a Remote Domain Server to be contacted and hundreds of groups walked. This can be a performance devastating overhead. To resolve the group to individual users and grant those users the permission is a logical solution. The presentation/representation is the bug, not the behavior. The presentation should read “Grant to all current NT Users in this NT Group” as an action, SMS not show any NT Group tied to a login <—that is the bug! It misleads most users.
Another way to view it is this: It is easy to walk all children of a NT Group to get a list of all NT Users under that NT Group and then give those users permissions tied to their NT User account. The reverse is an exploding search problem: To find out in an user has SQL permission, you have to walk all of the NT Groups that they belong; then the NT groups that those groups belong to, etc until you have exhausted the parentage of every group that the user may belong to OR until you found the needed permission. It is a potential complete enumeration problem – manageable for a small domain with few NT Groups – but with a large corporation it can result in a massive number of groups that must be traced upwards until SQL permission is found or the NT groups are exhausted – a SQL performance buster because every security operation may take minutes to determine if it is allowed.
THANK YOU! I was in the process of testing using NT user groups as an alternative to maintaining custom database roles or individual login permissions. I turned up this post in an internet search I did while trying to figure out why it wasn't working as expected. Now I know ... Time to go delete those test group IDs from the database.
ReplyDeleteAnd here I thought I could hand off some of my work to the Windows network admins. Teach me to be lazy! :)
wow - i agree with Scott - i was also in the process of testing using NT user groups. Thanks for saving me a bunch of time and headaches.
ReplyDeleteSQL 2005 and Windows Server 2003 with users in a security group. No problem at all.
ReplyDelete140+ servers here with SQL2005\2008 and Windows2000\2003\2008 and using NT groups with no problems at all. I can add and remove users at will from domain groups and access is immediately granted or denied.
ReplyDeleteSince it seems that it alleged that it works some times and documented as not working other times I suspect that there is some {obtuse} configuration issue involved between SQL Server and the domain.
ReplyDeleteIf you plan on using groups, you may wish to verify that it actually works (especially when a user is removed from a group that is contained in another group -- the permission cascade problem) and then make a point of re-verifying it periodically -- in case the {obtuse} configuration issue is changed.
Nobody mentioned this, so I thought I'd point it out. A user that is removed from a group continues to have that permission until they log out of Windows and back in.
ReplyDeleteHi,
ReplyDeleteAfter hours of investigations, I thing I found the {obtuse} cause : Using NT Security groups in SQL works very well (adding/removing users to group produce a correct behavior in SQL Server)... HOWEVER : User have to logoff/logon again in Windows to make new membership effective (as for NTFS permissions)...
Not to pile on....but this is important. Anonymous from Feb 13, 2012 is correct.
ReplyDeleteLogoff and login is needed and all is ok.
THE PREMISE OF THIS ARTICLE IS INCORRECT
The BUG here is actually in the "Microsoft official Site" the author uses to support his incorrect hypothesis (It has to do with SMS installation, not Directly from SQL Server documentation). The SMS installation page he references is incomplete. It does say that changing users is not dynamic...but should also say "Unless the user reconnects to the database".....that's where the error is in that SMS doc .... and I can see why the Author of this article was mis-lead by it.
AS for his further criticism of complex AD collections/heirarchies and walking permissions through them...well that depends on if your AD is spaghetti or logical. Using Domain Groups to manage permissions within SQL Server is not lazy as implied by a different Anonymous (March 15, 2010). It is a perfectly viable why to logically control data access according to logical AD setup.
Hope you read this far and din't jump thru SQL permissioning hoops you didn't have to.
*The author should soon revise its proclamation. Whoever does not read the comments is lost with false information!*
ReplyDelete