Linking eDirectory to other Database(s)
This page is intended to share techniques for linking eDirectory to other databases such as SQL Server, Oracle, MS Access.
I have been working on such a solution for SQL Server over the past few days, and hit quite a few roadblocks, so it'd be great to get some shared experience on this topic.
I found three possible means to move data from eDirectory to SQL Server, and tried all three:
- Using ADSI's LDAP features to make a linked server.
- Using Novell's ODBC Driver to make a linked server.
- Creating a .NET executable that uses the Novell Active X control for LDAP, and running that as a scheduled job periodically.
In my case, I was only able to make the third solution work, and failed at the first two.
ADSI Linked Server
This looked quite promising, as I found many SQL Server users who had had success doing this against Active Directory. However, there are some challenges. In the first document above, the section "Obtain Correct Authentication" explains how ADSI will default to using NT authentication, an issue which I was not able to work around in setting it up as a linked server in SQL server.
Furthermore, the second document lists "Limitations," which include the inability to retrieve any multi-valued attribute. From what I was seeing, if you query e-Directory with this method, it seems to return virtually all attributes that way, and so they all cause an error indicating that the value returned cannot be converted into a datatype that can be put in a table.
There is additional information in the eDirectory docs under "Enabling non-standard schema output."
Novell ODBC Driver
This looked good, too.
Challenges here: the ODBC driver seems not to allow one to authenticate as anybody but the user logged in at the workstation. In my environment, I needed an automated, server-based solution, and would have to authenticate against the directory with a specific login that had rights to what I needed to see.
Next, there appears to be some issue with the Novell driver passing data to the Microsoft OLE DB Provider for ODBC, where any Null values are not accepted by SQL server. Could have been something I overlooked, but I also saw this issue in several posts on the forum related to the ODBC driver.
Novell LDAP ActiveX Control
This solution worked: I created a .NET VB console application and used a Data Adapter and Data Set, which I filled with data by performing a search with NWIDir.
This enabled me to log in with a specific account to the directory to retrieve the data, and gave me an opportunity programmatically to handle the multiple-value attributes with a simple function.The down side here is that the information can only be cached in a SQL server table, and has to be periodically updated via a scheduled job. With the other solutions one would be able to query the "live" directory data.