Querying Active Directory

Question

Querying Active Directory

hawg

SSCertifiable

Points: 6044
More actions
June 10, 2003 at 1:56 pm

#59236

Can anyone help me understand how to query Active Directory from SQL Server?
I have been reading a lot on a bunch of forums and articles I can find but I always seem to miss something. I have not been able to get this to work.
I know I need to add a linked server to the Active Directory server and then build some views to query it but I don't know how I am supposed to do that.
Any help is appreciated.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

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

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

ckempste SSCoach Points: 17983 More actions · Answer 1

Hi

I did this via a linked server, see the BOL, they have an example. Unfortunatly I blew away the link and for the life of me cant remember the syntax. All that said - I found that the linked server oledb provide to AD restricted itself to 1000 rows (max), which in may case was not acceptable and couldnt use it.

Cheers

Ck

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

ckempste SSCoach Points: 17983 More actions · Answer 2

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q251/1/95.ASP&NoWebContent=1

not really what your after but worth a good look.

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

ckempste SSCoach Points: 17983 More actions · Answer 3

Found it!

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'AD Provider'

grant select on corpsysad_allusers to public

select * from corpsysad_allusers

create view corpsysad_allusers as

SELECT name

FROM OPENQUERY( ADSI, '

SELECT name

FROM ''LDAP://163.232.12.200/CN=Application Users,DC=ppl2kdv''')

where ppl2kdv is the root node of my AD (others may include the complete .com.au or whatever, if so, your need DC=com, DC=au for example. Check this via the AD Maintenance GUI on your server. CN is for container, OU is for org unit.

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

hawg SSCertifiable Points: 6044 More actions · Answer 4

Thanks for your help, but now I have some questions about exactly what you are showing me.

One, in the EXEC sp_addlinkedserver command, do I run the command just as you wrote it or are there some things that will be specific to my environment?

Two, can you tell me where I might find more information about the LDAP string in the query? I am trying to figure out what to put in it for my environment so I need to know what it is looking for.

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

Hans Lindgren SSChampion Points: 10496 More actions · Answer 5

Hello.

If you are still here maybe this can help you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/joining_heterogeneous_data.asp

//Hanslindgren