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.