locked
Are nested Windows groups supported as SQL Server 2008 logins? RRS feed

  • Question

  • Hello,

    I am attempting to configure a new SQL Server 2008 instance to use Windows authentication, and am encountering a problem when adding Windows domain groups as logins. Specifically, Windows users who are direct members of a Windows group that has been added as a login behave normally. Users who are members of groups that are, in turn, members of other groups which have been added as logins are unable to connect.

    In a simplified example, consider the following:

    Group: MYDOMAIN\DataViewers
      Member: MYDOMAIN\Hazel
      Member: MYDOMAIN\Seamus

    Group: MYDOMAIN\DataEditors
      Member: MYDOMAIN\Vaughan
      Member: MYDOMAIN\Liesel

    Group: MYDOMAIN\AllUsers
      Member: MYDOMAIN\DataViewers <-- This is a group
      Member: MYDOMAIN\DataEditors <-- This is a group

    If I add the MYDOMAIN\AllUsers group as a SQL Server login (and map it to some databases), none of the end users (i.e. Hazel, Seamus, Vaughan, Liesel) can connect. If I add the MYDOMAIN\DataViewers or MYDOMAIN\DataEditors groups as logins, though, their members can connect. Similarly, if I add an end user directly to the AllUsers group, they can connect normally.

    It appears as if the nested group model I had envisioned is not supported in SQL Server 2008, but I have been unable to find any documents that state this conclusively. Therefore, my question is:

      Q: Does SQL Server 2008 support nested Windows groups - and, if so, how do I make the model described above work properly?

    Thank you, in advance, for your assistance.

    -Michael
    Friday, July 31, 2009 6:28 AM

Answers

  • *** Please stand down ***

    Before well-intentioned forum member wastes their valuable time responding to my inquiry above, I'd like to pause this thread until I am able to do a little more testing on our local server.

    I decided to remove all logins based on Windows groups and re-test everything in an organized, documented fashion. During this testing I found that the MYDOMAIN\AllUsers group appears to be exhibiting problems outside of SQL Server. For example, I recently found the NET GROUP command, and can use:

      NET GROUP DataViewers /DOMAIN
      NET GROUP DataEditors /DOMAIN

    to view the members of those groups. If I try to do this for AllUsers, though, I get an error:

      The request will be processed at a domain controller for domain myorg.org.

      The group name could not be found.

      More help is available by typing NET HELPMSG 2220.

    The same thing happens with 'xp_logininfo' in SQL Server 2008:

      Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 42
      Could not obtain information about Windows NT group/user 'MYDOMAIN\AllUsers', error code 0x8ac.

    Oddly, I can add MYDOMAIN\AllUsers as a login, but I can't see who is in the group - or even verify that it exists - using xp_logininfo or NET GROUP.

    I believe now that this is something I need to resolve by working with our Windows administrator. Thank you if you've read this far, and I apologize for wasting your time. I will post an update to close this thread once I figure out exactly what is going on. Have a good weekend.

    -Michael

     

    Friday, July 31, 2009 8:57 PM

All replies

  • I have some new information that may be relevant, which I'd like to post.

    Since my last message I've been reading about Active Directory to gain some understanding about how the fundamentals of that system work. I recently learned about Universal, Global, and Domain Local groups and, correspondingly, asked our Windows administrator about the scope of the groups we are attempting to use with SQL Server. Continuing from the example above, it appears that our groups are configured as follows:

      MYDOMAIN\DataViewers: Global
      MYDOMAIN\DataEditors: Global
      MYDOMAIN\AllUsers: Domain Local

    Our Windows administrator explained to me that this was the only configuration that would allow him to nest the DataViewers/DataEditors groups inside the AllUsers group. According to the Windows Server documentation I am referencing, other configurations should support nesting, as well. He is re-testing nesting groups right now, and I will let you know what we find.

    In the mean time, I was curious as to whether the scope of the Windows groups that we are adding as logins would have any impact on their behavior. Thanks for any feedback on this topic.

    -Michael

    Friday, July 31, 2009 6:01 PM
  • *** Please stand down ***

    Before well-intentioned forum member wastes their valuable time responding to my inquiry above, I'd like to pause this thread until I am able to do a little more testing on our local server.

    I decided to remove all logins based on Windows groups and re-test everything in an organized, documented fashion. During this testing I found that the MYDOMAIN\AllUsers group appears to be exhibiting problems outside of SQL Server. For example, I recently found the NET GROUP command, and can use:

      NET GROUP DataViewers /DOMAIN
      NET GROUP DataEditors /DOMAIN

    to view the members of those groups. If I try to do this for AllUsers, though, I get an error:

      The request will be processed at a domain controller for domain myorg.org.

      The group name could not be found.

      More help is available by typing NET HELPMSG 2220.

    The same thing happens with 'xp_logininfo' in SQL Server 2008:

      Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 42
      Could not obtain information about Windows NT group/user 'MYDOMAIN\AllUsers', error code 0x8ac.

    Oddly, I can add MYDOMAIN\AllUsers as a login, but I can't see who is in the group - or even verify that it exists - using xp_logininfo or NET GROUP.

    I believe now that this is something I need to resolve by working with our Windows administrator. Thank you if you've read this far, and I apologize for wasting your time. I will post an update to close this thread once I figure out exactly what is going on. Have a good weekend.

    -Michael

     

    Friday, July 31, 2009 8:57 PM
  • I know this is an old posy but...  Did you know that SQL 2008 does not support nested groups in SQL2008 mixed mode?
    Thursday, April 8, 2010 1:14 PM
  • I'm curious..where did you get this information that sql mixed mode doesn't support nested groups?  We're having issues as well, so was just wondering where you got that information from.

    Thanks!

    Amy

    Tuesday, April 27, 2010 7:59 PM
  • Sorry it's taken me so long to reply, but I'm still not getting any alerts from any Microsoft forum. No spam filtering by my ISP, junk mail filter disabled in Outlook, mail anti-virus disabled in Kaspersky...

    Anyway, I have the same question as Amy:

    Did you know that SQL 2008 does not support nested groups in SQL2008 mixed mode?

    Q: Can you cite a reference for this?

    If nested groups are truly not supported, I need to work on another solution, rather than just waiting for a bug fix. Thanks.

    Saturday, August 7, 2010 4:23 PM
  • Nested groups are FULLY supported.

    If there is any MS documentation which states otherwise please let me know.

    thanks -Vijay

    Tuesday, August 17, 2010 4:30 PM
    Answerer
  • I got the same problem, running Sql2008 sp1. 

    I got domain group in local administrators group. And in SQL builtin\administrators are added as sysadmin. Local administrator account can login to sql, but not user added to domain group. 

    Strange. 

     

    Running SQL in mixed mode. 

     

    Thanks, Magnus

    Wednesday, September 22, 2010 9:30 AM
  • Hey Magnus - How are you connecting locally or remotely? Windows 2003 or 2008? and importantly whats the error message (in sql error logs)

    In case you are on windows 2008/vista/win 7 - might consider running as admin.

    Thursday, September 23, 2010 4:44 AM
    Answerer
  • I am having a similar problem with SQL 2008 SP1.  I have some servers that are SP1 and some that are not.  They are both running the same builds of Windows 2008 R2.  I have an proprietary application our dev team built and I can connect via a nested group to one server but not another.
    Friday, October 15, 2010 12:11 PM
  • Sorry late answer. I running SQL2008 R2 on Win2008 R2. 

    My problem is solved now, need to start SQL studio with admin rights. 

    http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/c3e0713c-b4e6-400e-9ba2-448cd5bf3cb8

     


    Magnus
    Monday, January 10, 2011 2:46 PM