Querying the Active Directory from SQL Server

Question

Post reply

Querying the Active Directory from SQL Server

petpet

SSC Veteran

Points: 236
More actions
November 5, 2003 at 8:13 am

#65251

Good afternoon,
This morning I have commented in my company that is possible to do queryies to the Active Directory through SQL Server and ten minutes after, this was already a necessity for the company.
I have been looking and I have found a very interesting article in the Microsoft website:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_94fn.asp
The first problem is that I have not "Active Directory Services" OLE DB component in any server. I think "Microsoft Directory Services" is the new one.
I create a new linked server from my SQL Server 2000. But when I do a query like this:
CREATE VIEW viewADContacts
AS
SELECT [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT Name, SN, ST
FROM ''LDAP://GALCD2/ OU=USERS,DC=CIBELES,DC=SGC''
WHERE objectCategory = ''Person'' AND
objectClass = ''contact''')
GO
SELECT * FROM viewADContacts
I get the following error:
Servidor: mensaje 7321, nivel 16, estado 2, línea 1
Error al preparar una consulta para su ejecución con el proveedor OLE DB 'ADsDSOObject'.
Traza de error de OLE DB [OLE/DB Provider 'ADsDSOObject' ICommandPrepare::Prepare returned 0x80040e14].
I apologize to write it in Spanish. If somebody needs I will write it in English.
Does somebody know what it can be failing?
Does somebody know some other article about this?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

I think error 7321 is caused by a malformed LDAP query.

Try the following. If it works then the link is OK, the query is the problem.

SELECT [Name], SN [Last Name], ST State 

FROM OPENQUERY( ADSI, 

'SELECT Name, SN, ST 

FROM ''LDAP://GALCD2'' 

WHERE objectCategory = ''Person'' AND 

objectClass = ''contact''')

Far away is close at hand in the images of elsewhere.
Anon.

petpet SSC Veteran Points: 236 More actions · Answer 2

Thanks David,

This query already works fine:

SELECT * FROM OPENQUERY( ADSI, 'SELECT Name, SN, ST FROM ''LDAP://GALCD2''')

and this other too:

SELECT * FROM OPENQUERY( ADSI, 'SELECT sAMAccountName,cn,userWorkstations FROM ''LDAP://GALCD2/OU=SETSIyGABINETE,DC=galatea,DC=cervantes''')

The biggest inconvenience is that I cannot see the Active Directory fields. Executing the Windows command "ldifde -m -f ADEXPORT.TXT" from a server I have export to a text file the active directory and I have see the following fields. They can be of help for somebody:

AdsPath

name

sAMAccountName

cn

userWorkstations

scriptPath

whenCreated

whenChanged

My problem is solved. Thank you.