How to query Active Directory

Question

Post reply

How to query Active Directory

hawg

SSCertifiable

Points: 6044
More actions
November 4, 2005 at 9:22 am

#110740

I have read tons of articles and looked at tons of scripts and I cannot get anything to work to query Active Directory. Can anyone help me figure out how to do this?
I am using a lot of AD groups for access to my SQL Servers and I sometimes need to know who is in these groups.
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 1

Take a look at this KB from Microsoft:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q299410

I had tried this only once before and it worked fine at that time. Don't have it set up right now.

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 2

To add the linked server and set th security for the linked server try

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

'ADSDSOObject', 'adsdatasource'

GO

EXEC sp_addlinkedsrvlogin 'ADSI', false

This is a sample query to return some info about your users from the AD obviously you'll need to change the LDAP bit depending on the OU you wish to query and distinguisehd names etc.

SELECT *

FROM OPENQUERY( ADSI,

'SELECT displayName,

department,

manager,

telephoneNumber,

mobile,

facsimileTelephoneNumber,

mail

FROM ''LDAP://OU=Test, DC=INTERNAL,DC=TEST, DC=LOCAL''

WHERE objectCategory = ''Person'' AND objectClass= ''user''')

hth

David

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 3

I've just remebered while I was writing the above the query, which is really useful for creating a view from which you can do a compny telephone list in reporting services, I was also trying to write one to get some info about who was in what group etc and I don't think you can because it returns a multi valued field which you can't do

hth

David

hawg SSCertifiable Points: 6044 More actions · Answer 4

OK, I got it working somewhat but I don't really understand how it works. The following are two examples of queries I was able to get working based on some examples:

SELECT * FROM OpenQuery(

ADSI,'<;(&(objectCategory=Person)(objectClass=user)(sAMAccountName=givensj));givenName,sn'">LDAP://swn.com>;(&(objectCategory=Person)(objectClass=user)(sAMAccountName=givensj));givenName,sn')

SELECT * FROM OpenQuery(

ADSI,'<;(&(objectCategory=OrganizationalUnit));*,cn'">LDAP://swn.com>;(&(objectCategory=OrganizationalUnit));*,cn')

I was wondering if someone could help me figure out how to write a query that will give me a listing of the group name and the user name (account and full name) for all members of groups that start with PA.

Thanks,

hawg

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 5

If all you're after is members of a group login....

master..xp_logininfo 'Your_Domain\Your_Domain_Group', members

Your friendly High-Tech Janitor... 🙂