How do I get the sAMAccountName of a Foreign Security Principal using VBA

[Event Horizon]

I use Excel to audit Active Directory Group memberships. Starting with the distinguishedName of a group I get the group object and then
enumerate all of the group members (and imbedded groups recurrsively). For each member of the group (distinguishedName) I obtain the object. Some of these objects are Foreign Security Principals of the form "CN=S-1-5-21-xxxxxxxxxx-xxxxxxxxx-xxxxxxxxxx-xxxxxxx.CN=foreignSecurityPrincipals". These FSP
objects appear to contain no information.

I have done extensive research on this problem and have found no VBA solutions for resolving the sAMAccountNames of these FSP objects.

There is a stand alone program called "adFind" from "Joeware" and for .net programs there is a way to use the classes from "system.directoryservices" to determine the actual sAMAccountNames. However I have not been successful in adapting either of these in VBA.

Any help would be appreciated.

 

[jimrward]

can you post what code you have so far, i did some dabbling in AD/LDAP last year, but cannot test as I am at home, but i still have vague memories do might be able to shed some light

 

[Event Horizon]

First of all thanks for taking a look at this. It may be more of an AD problem than a VBA problem but I didn't know where else to look for help on this.
I've edited out all of the error checking to simplify the code

Dim userObject
Dim primaryDNSName as string
Dim distinguishedDomain as string
Sub GetAllDomainGroupInformation(domain As String, distinguishedDomain As String)
Dim objConnection
Dim objCommand
Dim objRecordSet
Const ADS_SCOPE_SUBTREE = 2
Const ADS_CHASE_REFERRALS_ALWAYS = &H60
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Chase referrals") = ADS_CHASE_REFERRALS_ALWAYS
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "samAccountName"

objCommand.CommandText = _
"SELECT samAccountName, distinguishedName, description, info, managedBy, samAccountType, groupType, member " & _
"FROM 'LDAP://" & primaryDNSName & "/" & distinguishedDomain & "' " & _
"WHERE objectCategory='group'"

Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

Do While Not objRecordSet.EOF
'This sub just prints out all of the Group information contained in the objRecordSet
Call printGroupInformation(domain, objRecordSet)

'This sub gets all of the users and prints out their information
Call PrintUserGroupInformation(domain, objRecordSet)

objRecordSet.MoveNext

Loop

Set objRecordSet = Nothing

End Sub

Sub PrintUserGroupInformation(groupDomain As String, objRecordSet)
Dim member
Dim memberArray
Dim userDistinguishedName As String
Dim userDomain As String
Dim groupDistinguishedName As String
Dim groupDistinguishedArray(10)
Dim groupDisplayNameArray(10)
Dim groupDomainArray(10)
Dim groupLevel As Integer
Dim userName As String
If IsArray(objRecordSet.fields("member")) Then
memberArray = objRecordSet.fields("member")
For Each member In memberArray

userDistinguishedName = member
If getUserInfo(userDistinguishedName) then
userObjectClass = userObject.class
If userObjectClass = "user" Then
'Print out all of the user information here
'i.e., userObject.sAMAccountName
' userObject.displayName
' etc.
ElseIf userObjectClass = "group" Then
'Call re-entrant routine to grab nested groups and users
groupLevel = 1
groupDistinguishedName = objRecordSet.fields("distinguishedName").Value
groupDistinguishedArray(1) = groupDistinguishedName
groupDisplayNameArray(1) = objRecordSet.fields("samAccountName").Value
groupDomainArray(1) = getFirstDC(groupDistinguishedName)
Call getGroupInfo(userDistinguishedName, groupLevel, groupDistinguishedArray, groupDisplayNameArray, groupDomainArray)
ElseIf userObjectClass = "ForeignSecurityPrincipal" Then
'There doesn't appear to be any useful information in the userObject here
'userObject.sAMAccountName is blank
'userObject.displayName is blank
'userObject.description is blank
'UserObject.info is blank
'UserObject.DistinguishedName = "CN=S-1-5-21-xxxxxxxxxx-xxxxxxxxx-xxxxxxxxxx-xxxxx,CN=ForeignSecurityPrincipals, etc. ,DC=domain,DC=com"
'userObject.name = "CN=S-1-5-21-xxxxxxxxxx-xxxxxxxxx-xxxxxxxxxx-xxxxx"
Else
'Ignore other stuff (computers, etc.)
End If
End If

Next member
End If

End Sub
Function getUserInfo(userDistinguishedName As String) As Boolean
getUserInfo = False

If userDistinguishedName = "" Then
Exit Function
End If
'If the following query fails then the user doesn't exist in AD
On Error GoTo missingUser:
Set userObject = GetObject("LDAP://" & primaryDNSName & "/" & userDistinguishedName)
On Error GoTo 0

userObject.GetInfo

getUserInfo = True

missingUser:

End Function

From what I've read a Foreign Security Principal of the form "S-1-5-21-" is a place holder (SID or pointer?) for an account from another domain.
"Well Known" Foreign Security Principals are references to Built-In user accounts and groups but are of a different form.
How do I find out the sAMAccountName and User Name associated with this account in the other domain?
How do I find out what the other domain is?
The "domain" in the userDistinguishedName is my domain so this doesn't seem to be helpful either.

 

[jimrward]

i will take a proper look in the morning when I am a bit more beer free.

One thing I did find when dealing with AD is that it is crap, how something so powerful can have so useless user interfaces and logic was astounding

Anyway, one way to check your logic versus AD is to lookup a group or a name and make sure the detail actually exists in the record using AD itself, I know thru the versions of AD certain extra fields both set and custom were introduced, as I found out by using my entry as an example which was created 10 years ago, against someone created last year the fields are not the same, well the core fields are, just not the others

With that said, when querying AD, if you did not provide definitive fields then all sorts of errors and empty sets occured, also ANDing in filters cos AD stores things as bit masks in some fields just to confuse matters

 

[Event Horizon]

A "Partial" solution

Problem backstory:

The object was to audit all of the users who have access to all of the groups in our companys AD forest.
Domain\UserID
DisplayName

Our Auditors want this information in Excel on a monthly basis.
Since the information was to be presented in Excel we decided to simply use Excel (VBA) to gather the results.
On the face of it, this appears to be a relatively easy task.

1) Enumerate all of the domains in our forest

Set objRootDSE = GetObject("ldap://RootDSE")
strForest = objRootDSE.Get("rootDomainNamingContext")
primaryDNSName = objRootDSE.Get("dnsHostName")
Set objForest = getObject("GC://" & strForest")
objForest.Filter = Array("domain")
For Each objDomain in objForest
distinguishedDomainName = objDomain.distinguishedName

2) For each domain, get all of the groups

Const ADS_SCOPE_SUBTREE = 2
Const ADS_CHASE_REFERRALS_ALWAYS = &H60
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Chase referrals") = ADS_CHASE_REFERRALS_ALWAYS
objCommand.Properties("Cache Results") = False
objCommand.CommandText = _
"SELECT samAccountName, distinguishedName, description, info, managedBy," & _
samAccountType, groupType, member " & _
"FROM 'LDAP://" & primaryDNSName & "/" & distinguishedDomainName & "' " & _
"WHERE objectCategory='group'"
Set objRecordSet = objCommand.Execute
objRecordSet.moveFirst
Do While Not objRecordSet.EOF
objRecordSet.moveNext
Call OutputGroupInformation(objRecordSet)
Call OutputUserInformation(objRecordSet)
objRecordSet.moveNext

3) For each group, get all of the users

groupDistinguishedName = objRecordSet.distinguishedName
Set objGroup = getObject("LDAP://" & primaryDNSName & "/" & groupDistinguishedName)
memberArray = objGroup.GetEx("member")
For Each member in memberArray
userDistinguishedName = member
Set objUser = GetObject("LDAP://" & primaryDNSName & "/" & userDistinguishedName)
If objUser.class = "group" then
'Handle groups of groups here
Else
userDomain = getFirstDC(userDistinguishedName)
userID = objUser.sAMAccountName
userName = objUser.displayName

The code above is very simplified as it has no error checking and does not consider groups in the forest rootDomain.

The Problem:

This is where we ran into problems. When gathering group membership information for each group many of the "users" (members that were not identified as groups) had no sAMAccountName or displayName.

The first CN of the "users" distinguishedName was of the form:

CN=S-1-5-21-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-zzzzz

and all of these "users" appeared in the:

CN=ForeignSecurityPrincipals

container in the domain where the group appeared.

After some research we found that there was another object class which all of these "users" were members of:

objUser.class = "foreignSecurityPrincipal"

so we could at least identify them.

So, thinking that surely there was some mapping between this FSP object and the user information that we were looking for we now looked at all of the properties of the objects in the ForeignSecurityPrincipals container and found that there was no more useful information there than that which could be gleaned from the objects distinguished name.
Thanks Microsoft.

Thus the original question:

"How do I get the sAMAccountName of a Foreign Security Principal using
VBA"

A "Partial" Solution:

After further research on our part we found out that the weird "S-1-5-21-..." identifier was in fact the SID (Security Identifier) for a user in a trused external domain and that everything to the left of the last -zzzzz
was the SID of the trusted domain itself. So, how do we track down a trusted domain from it's SID?

After much more research we found that information for all of the trusted domains in a forest was contained in each domains "system" container.
This is easy enough to get:

'zero based arrays
trustDirectionArray = Array("disabled", "inbound", "outbound", "both (trusted and trusting)")
trustTypeArray = Array("", "downlevel trust", "Windows 2000 trust", "MIT", "DCE")
For Each objDomain In objForest
Set objConfiguration = GetObject("ldap://cn=System," & objDomain.distinguishedName)
For Each objContainer In objConfiguration
If objContainer.class = "trustedDomain" Then
trustingDomain = objDomain.name
trustedDomain = objContainer.name
trustDirection = trustDirectionArray(objContainer.trustDirection))
trustType = trustTypeArray(objContainer.trustType)
trustAttributes = objContainer.trustAttributes
trustPartner = objContainer.trustPartner
flatName = objContainer.flatName

unfortunately it contained no mapping of an external domains SID to it's domain name. So, we still couldn't even tell what external domain a user was a member of. So, we decided to look at the problem in reverse. Let's just get each external domain as an object and look at it's SID. We could then make up a table of the external domain names and their respective SIDs. Then using this table we could determine what domain each Foreign security Principal belonged to and then, hopefully, get their information. But I'm getting ahead of myself.

First, a note here about the LsaPolicy routines (specifically the LsaQueryTrustedDomainInfo routine) that comes up in every internet query about trusted domains. These routines, from the "ADVAPI32.dll" library" have extremely complex (some would say convoluted) parameter definitions, making them almost impossible to call from VBA, that in the end don't expose any information that doesn't appear directly in AD. In my opinion, don't waste your time on the interfaces to these routines
if you're programming in VBA.

A little more background for our particular problem is in order.
The company has a forest which consists of many domains. Each of these domains has trust relationships. So, we end up with a total of approximately 300 external trusts for other domains, forests, realms, etc.
Some of these trust relationships are with business partners and some with external vendors. So, many of these trusts are one way, inbound, so that we do not have access to these external entities.
This is where the "Partial" comes into this solution.

So, back to the "solution". If we take the name of a trust partner for a two way trust (trustDirection = 3) where the trust type is "Windows 2000 trust" (trustType = 2) we can usually get the external domain object:

If objContainer.trustDirection = 3 and objContainer.trustType = 2 then
'Change objContainer.name into a distinguishedName for the lookup
'i.e., CN=domain.company.com to DC=domain,DC=company,DC=com
domainName = Replace(objContainer.Name, "CN=", "DC=")
domainName = Replace(domainName, ".", ",DC=")
'Try to get trusted domain
Set objDomain = GetObject("LDAP://" & primaryDNSName & "/" & domainName)
If Not IsEmpty(objDomain) Then
domainObjectSID = objDomain.objectSID
'Note: There are many routines available on the internet to translate the domainObjectSID into an SDDL SID (the standard text SID format) so
they will not be included here.
Hint: Lookup octetToHexStr and hexToSddlStr.

After we have generated the domain name to domain SID table we can lookup any of the Foreign Security Principals that are in the domains that we were able to get the domain SID for.

Get SID of Foreign Security Principal
Strip the user identifier from the right hand side of the SID to get the domain SID
Lookup the domain SID in the table created above
If the domain SID is found:
Get the distinguished name of the foreign domain and lookup the user using the user's SID

Const ADS_SCOPE_SUBTREE = 2
Const ADS_CHASE_REFERRALS_ALWAYS = &H60
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Chase referrals") = ADS_CHASE_REFERRALS_ALWAYS
objCommand.CommandText = _
"SELECT sAMAccountname, displayName " & _
"FROM 'LDAP://" & primaryDNSName & "/" & domainDistinguishedName & "' " & _
"WHERE objectSID='" & userSID & "'"
Set objRecordSet = objCommand.Execute
If Not objRecordSet.EOF And Not objRecordSet.BOF Then
objRecordSet.moveFirst
If Not IsNull(objRecordSet.Fields("distinguishedName").Value) Then
userID = objRecordSet.Fields("sAMAccountname").Value
userName = objRecordSet.Fields("displayName").value

A note about the "WHERE" clause in the lookup above. We have found that it does not matter what format the userSID appears in. That is octetString, HEX delimited or SDDL string. They all seem to work.

So, it appears that the auditing problem is partially solved. In my opinion it won't be fully solved until Microsoft fixes some of the obivious omissions in AD.

Final Note: If you look in the "Active Directory Users and Computers" control panel and navigate to the ForeignSecurityPrincipals container and look at the resulting list you will find that many of the entries in the "Readable Name" column are blank. I believe that this means that the information is not available so I think that the ball is now in Microsoft's
court to fix AD so that real auditing is possible.