Using ADSI to query eDir via LDAP

Question

Using ADSI to query eDir via LDAP

Merrill Aldrich

SSCrazy

Points: 2358
More actions
July 7, 2005 at 5:20 pm

#86751

Has anyone had any success making a linked server query Novell eDirectory via ADSI / LDAP? I've been trying most of the day today, and I seem to be stuck at how to set the security. I can either query the directory and get no results, with no credentials, or I can supply credentials and the query fails with:
"Could not get the data of the row from the OLE DB provider 'ADSDSOObject'. Could not convert the data value due to reasons other than sign mismatch or overflow.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IRowset::GetData returned 0x80040e21: Data status returned from the provider: [COLUMN_NAME=sn STATUS=DBSTATUS_E_CANTCONVERTVALUE]]."
I am on SQL Server 2000 SP3, Windows 2000 Server.
I have a linked server made using
sp_addlinkedserver 'PRMC_EDIR', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
and I am trying simple stuff like
SELECT * FROM OPENquery(
PRMC_EDIR
,'<LDAP://server/o=org>;(cn=foo);cn;subtree'
)
or
SELECT * FROM OpenQuery(PRMC_EDIR
, 'SELECT sn
FROM ''LDAP://server/o=org''
WHERE cn=''me''
')
[names changed to protect the innocent.]

Viewing 9 posts - 1 through 8 (of 8 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Do you get the same errors when you're running from another tool like ldifde or through scripting such as with VBScript? Also, what service pack on Windows 2000?

K. Brian Kelley
@kbriankelley

philcart SSC-Forever Points: 47794 More actions · Answer 2

Never was successful using SELECT * in the query. Here's what we use for Win 2K3 Active Directory.

 SELECT 
  AD.[SAMAccountName] as [Account]
  , AD.[givenName] as [GivenName]
  , AD.[SN] as [Surname]
  , AD.[title] as [Title]
  , AD.[ST] as [State]
  , AD.[Mail] as [EMailAddress]
  , AD.[scriptPath] as [LoginScript]
  , AD.[adspath] as [ADPath]
 FROM OPENQUERY(ADSI,
     '<;(&(objectCategory=Person)(objectClass=user">LDAP://DC=foo,DC=bar>;(&(objectCategory=Person)(objectClass=user));
    SAMAccountName, givenName, SN, title, ST, Mail, scriptPath, adspath;subtree'
 &nbsp as AD

--------------------
Colt 45 - the original point and click interface

Merrill Aldrich SSCrazy Points: 2358 More actions · Answer 3

Thanks guys -

The machine is on Windows 2000 Server SP4; I have not tried ldifde specifically, as our directory is Novell-based rather than AD. I suppose that it might work anyway. However, I do have a third-party LDAP browser and it works as expected; I can "see" all objects in the tree.

Phill - no joy, even with specific columns:

SELECT sn

FROM OPENquery(

PRMC_EDIR

,'<LDAP://10.100.141.11/ou=SEATTLE, o=prmc>;(sn=aldrich);sn;subtree'

)

also barfs up the same error.

philcart SSC-Forever Points: 47794 More actions · Answer 4

Ok, what are the specific properties on your linked server? I'm not at work at the moment, but I can post our settings on Monday if you think that might help.

--------------------
Colt 45 - the original point and click interface

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

A Google search pulls up some errors on the Novell support side but to get to the actual docs requires username/password. So there does seem to be some issue with ADSI querying Novell. If you have access on that side, you might inquire as to what you have to do to get ADSI to work with Novell.

K. Brian Kelley
@kbriankelley

Merrill Aldrich SSCrazy Points: 2358 More actions · Answer 6

Thanks, Brian. I will check that out. Phill, if I don't find anything on the Novell site, I'll post those settings.

Merrill Aldrich SSCrazy Points: 2358 More actions · Answer 7

Okey dokey, just for the sake of completeness:

ADSI as a Linked Server is not happy with Novell eDirectory LDAP. I have not totally isolated why, but I am giving up and moving on :-). Novell makes an active X control that works, and in my book things that work are on page 1 and things that don't might still be interesting but are on page 50+. Downside is now I have to write a whole program :-).

In case anyone else tries to do this, the last bits of info I located that might be the culprit are:

http://www.novell.com/documentation/edir873/index.html?page=/documentation/edir873/edir873/data/agazepd.html

under the heading "Enabling Nonstandard Schema Output"

and in http://support.microsoft.com/kb/251195/EN-US/

under "Obtain Correct Authentication"

both of which seem to indicate that ADSI in its default mode does not quite exactly precisely comply with LDAP, hence it works with Active Directory, but does not actually work with other directories in many circumstances.

Also, in case another poor DBA is dredging this up, the ODBC Driver from Novell will not work with SQL server because of some problem with the compatibility of the Microsoft OLE DB Provider for ODBC. See:

http://forge.novell.com/modules/xfmod/newsportal/thread.php?group_id=1075&group=novell.devsup.odbc

several threads on "unexpected null" error from SQL server.

Also, that driver cannot authenticate as anything but the currently logged in user, so I could not practically run it in a service setup where the server isn't actually logged in as a user with rights to the directory.

Gabriel Rizzuti SSC Enthusiast Points: 138 More actions · Answer 8

look this microsoft´s article

http://support.microsoft.com/kb/892098