Why Don’t Universal Groups Work in SQL Server?

Published On: 2018-04-02By:
Locked Bike

https://www.flickr.com/photos/123327536@N08/23891946434

If you’ve tried using Universal Groups in Active Directory to access your SQL Servers, you may notice that the users who are members of these groups can’t access the SQL Server Instance. The reason for this has more to do with active directory than with SQL Server.  Normal groups in Active Directory are cached so authentication requests can return groups that the user is a member of as part of the Windows Token. Universal groups, however, aren’t included in the Windows Token as the Universal groups that the user might be a member of might not be in the same domain that the request is handled by.

The internals of why Universal Groups don’t work requires a decent understanding of the internals of Windows Authentication Tokens and Windows Security. But needless to say, all that you need to know is that Universal Groups don’t work with SQL Server.

Because the Universal Groups aren’t in the authentication token when the SQL Server goes to see if the user has access, the token says that the user doesn’t.  The fix for this is quite easy, use a different Windows Domain Group type than Universal Groups.

Denny

The post Why Don’t Universal Groups Work in SQL Server? appeared first on SQL Server with Mr. Denny.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.