URGENT - Retrieve Users From Active Directory Group, Access 2007

[Ramya_mudambi]

Master,
In the SELECT query, i'm trying to lookup with ADgroupNames ending with "NET".
.CommandText = "SELECT name, member FROM 'LDAP://" & ADServer & "' WHERE name like '%NET'"
The system stops at line- "Set rsGroups = .Execute" and displays Run-time error '-2147217900 (80040e14)': One or more errors occurred during processing of command.

When tried in SQL "where name like '%NET'" it works fine. I tried using "' WHERE name = '%NET'". It did not work either

Request you to guide me in the right direction here.

 

[Ramya_mudambi]

Believe it or not, WHERE name = '*NET' worked !!

.CommandText = "SELECT name, member FROM 'LDAP://" & ADServer & "' WHERE name = '*NET'"

Wishing you all a very happy new year 2014

 

[Galaxiom]

Yes the syntax of LDAP is its own animal. Doesn't support SELECT * either.

 

[Ramya_mudambi]

Really URGENT - Active Directory to access | Hard-coded values in SELECT statement's ADgroup name lookup string

Dear Sir,
In Access, via LDAP the system is retrieving the list of users assigned to the specific ADGroups.

In the SELECT statement below, the lookup values in the WHERE clause has been hard-coded to accomodate nearly 50+ ADgroup names with OR operator via string "name = 'ABCD' OR name = 'EFGH' OR name = 'xyz'" (in this example just 3 ADGroup names have been mentioned)

.CommandText = "SELECT name, member FROM 'LDAP://" & ADServer & "' WHERE " & _
"name = 'ABCD' OR name = 'EFGH' OR name = 'xyz'"
Set rsGroups = .Execute

Earlier instead of using the hard-coded values, in the WHERE clause i have tried referring to column in a table containing these 50+ ADgroup names via string " WHERE name IN (SELECT [ADGroupName] FROM Unique_ADgroup)". The system throws error and stops right at "Set rsGroups = .Execute"

Right now, the AUDITing team is finding it tough to compare the ADgroup names in excel against these hardcoded values of 50+ ADgroup names in VBA string "name = 'ABCD' OR name = 'EFGH' OR name = 'xyz'".

Kindly let me know,
a. How to fine tune the SELECT statement to lookup for ADgroup names from a Table's column instead of hard-coded values
OR
b. Easier way to compare the ADgroup names in excel against these hardcoded values of 50+ ADgroup names in string "name = 'ABCD' OR name = 'EFGH' OR name = 'xyz'"

Request you to prioritize this and Please help.

Many thanks,
Ramya

 

[Ramya_mudambi]

Dear Sir,
I managed to update the code to lookup from the table in access and then extract these specific ADgroups and Users associated to this Adgroup (without hard-coding the ADgroup Names in code). This is working fine now

ATTACHED IS THE UPDATED DATABASE

Here are the updates:

With objCommand1
StrText = GetText()
.ActiveConnection = objConnection
.CommandType = adCmdText
.CommandText = StrText
Set rsGroups = .Execute
End With

Then a small FUNCTION which prepares the SQL SELECT Statement from the table in Access, which looks like:
Private Function GetText()
Dim DB As DAO.Database, RsAdGroup As DAO.Recordset, StrText As String
Set DB = CurrentDb
Set RsAdGroup = DB.OpenRecordset("SELECT ADGroupName FROM Unique_ADgroup;")
StrText = "SELECT Name, member FROM 'LDAP://" & ADServer & "' WHERE Name='Dummy'"
Do While Not RsAdGroup.EOF
StrText = StrText & " OR Name='" & RsAdGroup.Fields("ADGroupName").Value & "'"
RsAdGroup.MoveNext
Loop

GetText = StrText
End Function

------------------

Sir, from observation there appears that there are NESTED groups within each ADgroup. Hence i updated the code from " objectCategory='user' " to " objectCategory='group' or objectCategory='user' " as below
.CommandText = "SELECT sAMAccountName" _
& " FROM 'LDAP://" & ADServer & "'" _
& " WHERE objectCategory='group' or objectCategory='user'" _
& " AND CN='" & strUser & "'"

If the ADgroup name i searched was "dnagsCompadm", the code is extracting the List of GroupNames (under "dnagsCompadm") and Users in "dnagsCompadm". This was ONLY in the client DEMO I realized there were nested Groups and Users belonging to these nested groups were not displaying

Request you to kindly let me know how we can extract Users from the NESTED GroupNames under ADgroup"dnagsCompadm"

I would like to take this opportunity to heartily acknowledge, Within this very short span of time i have managed to gain good amount of knowledge in Access.
Thank you for your continuous guidance and inspiration.

Thanks again Sir.

 

[Galaxiom]

Unfortunately I am not so deeply familiar with the Active Directory structure to know what happens about groups in groups.

All I can offer right now are guesses since I have no domain controller at home.

I imagine you would need some nested loops through the members of any groups that were found in the loop through members of the each group.

This would constitute a recursive process to an unknown depth so probably best done using a function that calls itself.

 

[Ramya_mudambi]

Thank you for the guidance Sir. I will try and implement your suggestions.

 

[Ramya_mudambi]

Sir,
Please let me know if I'm correct here... In the DistinguishedName, "OU=Groups" is what diffrentiates the Group to Users.

 

[Ramya_mudambi]

Sir,
I need your guidance.

I tried writing the function for extracting Members from the Nested group and it did not work

From google search, i found (http://gallery.technet.microsoft.com/scriptcenter/List-All-Members-of-a-2ff57ca3#content) which would List GroupMembers Including members from Nested groups

Currently, in the code they're looking up for StrGroupName = "Domain Admins and
Set ObjRootDSE = GetObject("LDAP://RootDSE")
StrDomainName = Trim(ObjRootDSE.Get("DefaultNamingContext"))

Here, i wanted to know, where i could update the <IP Address>:<PORT>?
Should it be "RootDSE" or "DefaultNamingContext". If not, are these something that supposed to be updated?

I do not know where to view the ECHO. The code throws error '91' at WScript.Echo

I'm eager to learn, Kindly guide me Sir.

Many thanks.

 

[Galaxiom]

I do not know where to view the ECHO. The code throws error '91' at WScript.Echo

That is because it is a VB Script. It is the same as a Message box in VBA.

 

[Ramya_mudambi]

Thank you sir. I was able to write the recursive function and complete the rest.

Thank you so much.