April 13, 2015 at 5:50 am
Hi
Since they have change AD structure i'm getting trouble to make my query work as i wanted. Below is the structure containing two groups one nameed 'Coordination' and the oher 'Administrator' Administrator is under OAS and contain Coordination.
I'm trying to display all users of Administrator group and since Coordination is defined in Administrator, i want its users too.
my query is like this
SELECT
cn,ADsPath,givenname
FROM OPENQUERY(ADSI,';(&(objectCategory=person)(objectClass=user)
(members='',CN=''Coordination'',OU=Groupes,OU=Applications,
OU=PROJET_COMMUN,OU=OAS,DC=IPPM,DC=QC,DC=CA));cn,ADsPath,givenname;subtree')
My AD structure is like this
+ DC=IPPM,DC=QC,DC=CA
- OU=Applications
- OU=PROJET_COMMUN
- OU=OAS
In OAS i've three groups one of them is the Adminitrator group containing the Coordination group. Users are defined in both Adminstratoer and Coordination group.
My query returns nothing
Can any one help me ?
Regards
April 14, 2015 at 7:33 am
This is an interesting question. I don't know the answer but I'm happy to "bump" it for you.
Anyone have a clue on this? Seems like it could be quite useful for tracking down the privs of individuals by AD group.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2015 at 5:38 pm
I can get the following query to work. Directory path has been changed to what you have listed above, it will pay to check it for errors.
SELECT *
FROM OpenQuery(ADSI, 'SELECT cn,ADsPath,givenname
FROM ''LDAP://ippm.qc.ca/DC=IPPM,DC=QC,DC=CA''
where objectClass = ''User'' and objectClass=''Person''
and memberOf=''cn=Coordination,OU=Groups,OU=Applications,OU=Project_COMMUN,OU=OAS, DC=IPPM,DC=QC,DC=CA'' ')
April 15, 2015 at 8:26 am
Hi L30
First of all thank you
In fact I already had this solution. Actually my question is, how can I recursively get all users in Administrator and those of Coordination since Coordination is contained in Administrator. Thanks again
Regards
April 15, 2015 at 3:48 pm
Ah... apologies asene, I should have read it more thoroughly. So you want to query a nested group?
If so I found this on StackOverflow
http://stackoverflow.com/questions/13914698/query-ad-group-membership-recursively-through-sql
April 16, 2015 at 6:36 am
Hi L30
Thanks for taking time for this. I'm not to far from the the result i wanted but i'm stuck. My query and the one provided from your link are almost the same. The difference is the definition of the domain. I think i'm doing something wrong in my query. I've tried so many thing and the query still returning empty. Wich is false.
SELECT adspath,displayName,givenName,cn
FROM OPENQUERY(ADSI,
'<LDAP://DC=IPPM,DC=QC,DC=CA>;
(&(objectCategory=Person)(objectClass=User)
(!(userAccountControl:1.2.840.113556.1.4.803:=2))(memberof:1.2.840.113556.1.4.1941:=CN=GROUPE ACCES APPLICATION OAS GESTIONNAIRES,
OU=OAS,OU=PROJET_COMMUN,OU=Applications,OU=Groupes,DC=IPPM,DC=QC,DC=CA));adspath,displayName,GivenName,cn;subtree')
I've used the same approch as in the link like '<LDAP: dc="corp,dc=mycorp,dc=com"> and got an error message from sql
Regards
April 16, 2015 at 9:44 pm
If you can, try using power shell instead. The "Get_ADGroupMember" command has a -recursive option that will give you a list of the members of the AD group you specify and also the member of any groups that the specified AD Group contains.
Also, using the ADSI method, you very quickly find its limits. If you have more than 1,000 records as the result of you LDAP query, too bad. The query will only return 1000 records. There are a number of workarounds for this such as changing your query to return data for smaller ranges (e.g. user names starting with each letter of the alphabet) but there gets tedious real fast. ADSI is not that fast either (which I suspect is one of the reasons that the 1,000 record limit was put in place).
Power shell will return all records and it is a heap faster than ADSI. Downside is that you may need to change the way you are getting the data.
April 19, 2015 at 4:52 pm
Hi asene, are you able to successfully query any other objects in the AD?
Are you able to get results from the group that is not nested, or any group with no nested groups as members?
To check AD access permissions, can you get the results you desire using ldife or powershell?
It took me a little while to get a query to return results on my test server. You may have tried this but I notice that I need to pass the select statement in to the linked server also. So it would look something like -
SELECT adspath,displayName,givenName,cn
FROM OPENQUERY(ADSI
, 'SELECT adspath,displayName,givenName,cn
FROM ''<LDAP://DC=IPPM,DC=QC,DC=CA>''
WHERE (&(objectCategory=Person)(objectClass=User)
(!(userAccountControl:1.2.840.113556.1.4.803:=2))(memberof:1.2.840.113556.1.4.1941:=CN=GROUPE ACCES APPLICATION OAS GESTIONNAIRES,
OU=OAS,OU=PROJET_COMMUN,OU=Applications,OU=Groupes,DC=IPPM,DC=QC,DC=CA));adspath,displayName,GivenName,cn;subtree')
-- FROM ''<LDAP://ippm.qc.ca/DC=IPPM,DC=QC,DC=CA>''
I am unsure why but the domain root syntax I commented out I had to use to get it to work for me.
April 20, 2015 at 5:22 am
Hi L30
Thanks again. Yes I can retreive all users with the same query you provided at your first post, of any group including the nested one. I've tried this way to, and got the same message while trying to run it. You can see the mesage here (What a vague message)
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query execution "SELECT adspath,displayName,givenName,cn FROM '<LDAP://DC=IPPM,DC=QC,DC=CA>'
WHERE (&(objectCategory=Person)(objectClass=User)
(!(userAccountControl:1.2.840.113556.1.4.803:=2))(memberof:1.2.840.113556.1.4.1941:=CN=GROUPE ACCES APPLICATION OAS GESTIONNAIRES,
OU=OAS,OU=PROJET_COMMUN,OU=Applications,OU=Groupes,DC=IPPM,DC=QC,DC=CA));adspath,displayName,GivenName,cn;subtree" sur le fournisseur OLE DB "ADsDSOObject" from linked "ADSI".
The only think i'm pointing now is my domain name. Because everything seems ok. Today i'll try to investigate that way. Anyway i thank you a lot L30. i'll let you know my investigation resluts.
Best regards
April 20, 2015 at 11:06 am
L30 (4/15/2015)
Ah... apologies asene, I should have read it more thoroughly. So you want to query a nested group?If so I found this on StackOverflow
http://stackoverflow.com/questions/13914698/query-ad-group-membership-recursively-through-sql
Thanks for that link. It actually led me down a path where I found tons of MS documentation on the subject (I'd post some links but we know how unstructured MS online documentation can be. Gotta makes heads or tails out of it first.). I don't yet understand it all but I'm going to get there because it's going to help me automate the answer to the frequent question of "what are the effective privs that {someperson} has in the {somedatabase}"?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2015 at 7:03 pm
Jeff Moden (4/20/2015)
L30 (4/15/2015)
Ah... apologies asene, I should have read it more thoroughly. So you want to query a nested group?If so I found this on StackOverflow
http://stackoverflow.com/questions/13914698/query-ad-group-membership-recursively-through-sql
Thanks for that link. It actually led me down a path where I found tons of MS documentation on the subject (I'd post some links but we know how unstructured MS online documentation can be. Gotta makes heads or tails out of it first.). I don't yet understand it all but I'm going to get there because it's going to help me automate the answer to the frequent question of "what are the effective privs that {someperson} has in the {somedatabase}"?
Jeff - I'd be obliged if you wrote up something on this. I've looked into it and it ain't particularly easy. Had I a definite need (other than curiosity) I probably could have cracked it. But my curiosity was never satiated.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 20, 2015 at 7:15 pm
dwain.c (4/20/2015)
Jeff Moden (4/20/2015)
L30 (4/15/2015)
Ah... apologies asene, I should have read it more thoroughly. So you want to query a nested group?If so I found this on StackOverflow
http://stackoverflow.com/questions/13914698/query-ad-group-membership-recursively-through-sql
Thanks for that link. It actually led me down a path where I found tons of MS documentation on the subject (I'd post some links but we know how unstructured MS online documentation can be. Gotta makes heads or tails out of it first.). I don't yet understand it all but I'm going to get there because it's going to help me automate the answer to the frequent question of "what are the effective privs that {someperson} has in the {somedatabase}"?
Jeff - I'd be obliged if you wrote up something on this. I've looked into it and it ain't particularly easy. Had I a definite need (other than curiosity) I probably could have cracked it. But my curiosity was never satiated.
NP. I have to write something about it even if it's for myself so I don't have to look at all the incomplete or difficult to follow documentation on it ever again. Might as well share. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2015 at 7:24 pm
Jeff Moden (4/20/2015)
dwain.c (4/20/2015)
Jeff Moden (4/20/2015)
L30 (4/15/2015)
Ah... apologies asene, I should have read it more thoroughly. So you want to query a nested group?If so I found this on StackOverflow
http://stackoverflow.com/questions/13914698/query-ad-group-membership-recursively-through-sql
Thanks for that link. It actually led me down a path where I found tons of MS documentation on the subject (I'd post some links but we know how unstructured MS online documentation can be. Gotta makes heads or tails out of it first.). I don't yet understand it all but I'm going to get there because it's going to help me automate the answer to the frequent question of "what are the effective privs that {someperson} has in the {somedatabase}"?
Jeff - I'd be obliged if you wrote up something on this. I've looked into it and it ain't particularly easy. Had I a definite need (other than curiosity) I probably could have cracked it. But my curiosity was never satiated.
NP. I have to write something about it even if it's for myself so I don't have to look at all the incomplete or difficult to follow documentation on it ever again. Might as well share. 🙂
Besides, I don't think I've seen you publish anything new on SSC in what now? A year or so?
I miss your articles.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 20, 2015 at 7:39 pm
It has been quite a while. Over a year. Thanks for the vote of confidence, Dwain. Much appreciated.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2015 at 10:30 pm
L30 (4/19/2015)
Hi asene, are you able to successfully query any other objects in the AD?Are you able to get results from the group that is not nested, or any group with no nested groups as members?
To check AD access permissions, can you get the results you desire using ldife or powershell?
Hi asene, can you see if the following ldifde query will work from a cmd window?
ldifde -f C:\temp\<YourFileName>.ldf -p Subtree -s <YourDCServer> -d "CN=GROUPE ACCES APPLICATION OAS GESTIONNAIRES,OU=OAS,OU=PROJET_COMMUN,OU=Applications,OU=Groupes,DC=IPPM,DC=QC,DC=CA" -l member,adspath,displayName,GivenName,cn
The LDAP path has to be perfect so I can recommend using the tip I found below.
If you get no entries exported, copy the full DN as per these steps.
1. Start, Run, dsa.msc (Part of Windows Server Support Tools)
2. Expand your Domain. This should mimic your OU structure.
3. Expand through your OU tree until you locate your group.
4. Once you have located your group CN=Group Name, right click properties.
5. Attribute Editor tab. Locate the attribute DistinguisedName and double click.
6. Copy the entire string which should be in the following format:
CN=My Group Name,CN=Users,DC=corp,DC=domain,DC=net
7. Enter in the following LDIFDE command.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply