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=....'' ')

score 0 · Accepted Answer · edited May 23 '17 at 12:16

0

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.

edited May 23 '17 at 12:16

Community

1
1

answered Nov 21 '14 at 09:14

baldpate

1,707
1
14
23

Update im getting a error when i try using this. FROM ''LDAP://DC=..,DC=.. WHERE ObjectClass = ''user'' and memberof:1.2.840.113556.1.1.4.1941:=''CN=...,OU=...,DC=..,DC=...'' ') i founde that using :1.2.840.113556.1.1.4.1941: i will get nested groups and users that are from other domains but im not getting ths syntax to work?.. – Nils Nov 21 '14 at 09:55
:1.2.840.113556.1.1.4.1941: (the LDAP_MATCHING_RULE_IN_CHAIN matching rule) requires domain/forest functional level of 2008 R2 (forgot domain or forest). Does your forest/domain support that? – baldpate Nov 22 '14 at 06:37
Your update works with "1.2.840.113556.1.4.1941" however in the domain im searching the forest/domain is only W2K3 and that can be the reason why i can not see nested groups or users from other domain. thank i will mark your post. – Nils Nov 22 '14 at 11:56
I got this almost to work now. do anyone know how i can use varible in this? SELECT samAccountName,distinguishedName FROM OPENQUERY (ADSI, '; (&(objectCategory=user)(member:1.2.840.113556.1.4.1941:=CN='+@varible+',OU=..,DC=...,DC=....,DC=....));samAccountName, distinguishedName;subtree'); the varible @varible get a error in the first + i think i might need alot of ('''''')?? – Nils Nov 22 '14 at 19:05
According to this [link](http://msdn.microsoft.com/en-us/library/ms188427.aspx). OPENQUERY does not accept variables for its arguments. You may need to compose the SQL statement into a string first, and then execute it. – baldpate Nov 23 '14 at 11:07

SQL with Active Directory and nested groups/users

1 Answers1