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.