Answer
Step 1:- Add a linked server for the Active Directory
Syntax: exec sp_addlinkedserver ‘ADSI’, ‘Active Directory Services 2.5′, ‘ADsDSOObject’, ‘adsdatasource’
Example: exec sp_addlinkedserver ‘ADSI’, ‘Active Directory Services 2.5′, ‘ADsDSOObject’, ‘kenstest.krsdom.com’
Note: I had to use exec sp_dropserver ‘ADSI’, droplogins in order to readd it since ADSI already existed but with an incorrect datasource.
Step 2 :- You can query the Active Directory listing (LDAP is limited to only 1000 rows returned. You may need to adjust this) Refer to the following Microsoft link for directions on how to change the maxpagesize. http://support.microsoft.com/kb/315071
Select * from OpenQuery(ADSI, ‘Select name from ''LDAP://kenstest.krsdom.com/DC=krsdom,DC=com’’ where objectClass =''Computer’’ and memberOf = ''CN=
Note1: All quotes in the query are single quotes.
Note2: Used DSQuery.exe to find proper syntax for memberOf
Ran Dsquery * dc=krsdom, dc=com –attr * -limit 2000 >c:\ad.txt and then searched for AdminSecurity in ad.txt
Here is the list of columns that can be used to query.
List of columns for querying Active Directory using LDAP.
1. countrycode |
28. mail |
SELECT vc.guid, vc.Name as [Computer Name],vc.[User],sn.[Computer Model],sn.[Computer Type] FROM vComputer as vc
JOIN Inv_AeX_HW_Serial_Number as sn ON sn._ResourceGuid = vc.Guid
WHERE Name IN
(SELECT * FROM OpenQuery(ADSI,
'Select Name from ''LDAP://kenstest.krsdom.com/DC=krsdom,DC=com''
where objectClass=''computer'' and
memberOf=''CN=
ORDER BY vc.Name