How to Query Active Directory Using Enterprise Manager
Step 1
Open Security and find Linked Servers (Figure 1). This is where your linked server will show up after you add it.
Open Tools -> SQL Query Analyzer and paste in the following code:
exec sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADsDSOObject', 'adsdatasource'
You should now be able to expand Linked Servers and see a server called ADSI…and under that Tables and Views (Figure 2).
Step 2
Go back to SQL Query Analyzer and run the following query:
SELECT SAMaccountname, CN, telephoneNumber FROM OPENQUERY(ADSI, 'SELECT telephoneNumber, CN, SAMaccountname FROM ''LDAP://DC=your-domain,DC=com'' WHERE objectCategory=''person'' AND objectClass=''user'' ')
Nice huh?!? Now, create a new view and paste in this code! To get more fields, simply add the AD properties you would like returned…and update your SELECT. You can also filter by using WHERE on the outside of the parenthesis. For example:
SELECT SAMaccountname, CN, telephoneNumber FROM OPENQUERY(ADSI, 'SELECT telephoneNumber, CN, SAMaccountname FROM ''LDAP://DC=your-domain,DC=com'' WHERE objectCategory=''person'' AND objectClass=''user'' ') WHERE SAMaccountname='sshepherd'
hmmmm . . . .this looks vaguely familiar . . .
You might want to mention that this won’t work so well if your query is returning over 1000 records (the default AD return limit).
Comment on October 26, 2006 @ 6:45 pm
Good point Stephen. I also discovered, thanks to you, that it’s better to filter your search results inside the parens (in your LDAP query), than outside. I need to post an example since we had such a hard time finding this information documented anywhere.
Comment on October 26, 2006 @ 8:30 pm
SELECT * FROM OPENQUERY(ADSI,
‘;(&(objectCategory=Person)(objectClass=user));name, adspath;subtree’)
From http://msdn2.microsoft.com/en-us/library/aa746379.aspx
Comment on April 26, 2007 @ 10:03 am
Ok, the query got messed up by something…
Go to the link for more information.
Comment on April 26, 2007 @ 10:04 am