SQL with Active Directory and nested groups/users

Question

i have one AD group that contains 5 users. 3 of the users are from the Domain1 and 2 are from a trusted Domain2

i have a problem getting the users from domain2 after i read out what users have access to what groups.

I'm using openquery to adsi. (The domain and servers are 2008 R2)

This is an expansion of this topic

Query AD Group Membership Recursively Through SQL

Update This is the query but im getting error:

select samAccountName,distinguishedName 
            FROM OPENQUERY(ADSI,'
                SELECT samAccountName,distinguishedName 
                FROM ''LDAP://domain/DC=...,DC=....,DC=....''
                WHERE 
                    objectCategory=''user'' AND
                    memberof:1.2.840.113556.1.4.1941:= ''CN=..,OU=..,DC=...,DC=....,DC=....'' ') 

This is my query that workes but im not getting the nested groups OR users from other domain.

select samAccountName,distinguishedName 
            FROM OPENQUERY(ADSI,'
                SELECT samAccountName,distinguishedName 
                FROM ''LDAP://domain/DC=...,DC=....,DC=....''
                WHERE 
                    objectCategory=''user'' AND
                    memberof= ''CN=..,OU=..,DC=...,DC=....,DC=....'' ') 

Answer 1

No knowledge on openquery to adsi. Only some comments:

• On second search statement, should use memberOf instead of member
• FSPs are used to represent security principal from foreign forest. See http://msdn.microsoft.com/en-us/library/cc223700.aspx. The only hint to get back the user/group from FSP is the SID in objectSID attribute. But there seems no easy way to do this. See Accessing Foreign Security Principals
• You can only get direct member or containing group using member and memberOf attribute but not nested one
• Group members due to primary group are not listed in member and memberOf at all.

UPDATE:

Okay, according to your link Query AD Group Membership Recursively Through SQL,
the following should work:

SELECT samAccountName,distinguishedName
    FROM OPENQUERY (ADSI, '<LDAP://domain/DC=...,DC=....,DC=....>;
    (&(objectCategory=user)(member:1.2.840.113556.1.4.1941:=CN=..,OU=..,DC=...,DC=....,DC=....));samAccountName, distinguishedName;subtree');

Also, please check the functional level according to:

http://www.technipages.com/active-directory-how-to-check-domain-and-forest-functional-level

The ":1.2.840.113556.1.4.1941:=" syntax requires functional level of 2008 R2.