URGENT - Retrieve Users From Active Directory Group, Access 2007 (1 Viewer)

Ramya_mudambi

Registered User.
Local time
Today, 20:25
Joined
Dec 27, 2013
Messages
32
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 :confused:

Request you to guide me in the right direction here.
 

Ramya_mudambi

Registered User.
Local time
Today, 20:25
Joined
Dec 27, 2013
Messages
32
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

Super Moderator
Staff member
Local time
Tomorrow, 01:55
Joined
Jan 20, 2009
Messages
12,849
Yes the syntax of LDAP is its own animal. Doesn't support SELECT * either.
 

Ramya_mudambi

Registered User.
Local time
Today, 20:25
Joined
Dec 27, 2013
Messages
32
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

Registered User.
Local time
Today, 20:25
Joined
Dec 27, 2013
Messages
32
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.
 

Attachments

  • AD.zip
    18.1 KB · Views: 183
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:55
Joined
Jan 20, 2009
Messages
12,849
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

Registered User.
Local time
Today, 20:25
Joined
Dec 27, 2013
Messages
32
Thank you for the guidance Sir. I will try and implement your suggestions.
 

Ramya_mudambi

Registered User.
Local time
Today, 20:25
Joined
Dec 27, 2013
Messages
32
Sir,
Please let me know if I'm correct here... In the DistinguishedName, "OU=Groups" is what diffrentiates the Group to Users.
 

Ramya_mudambi

Registered User.
Local time
Today, 20:25
Joined
Dec 27, 2013
Messages
32
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.
 

Ramya_mudambi

Registered User.
Local time
Today, 20:25
Joined
Dec 27, 2013
Messages
32
Thank you sir. I was able to write the recursive function and complete the rest.

Thank you so much.
 

Users who are viewing this thread

Top Bottom