Query a non-MS/non-Active Directory LDAP Server

[Clifford Bass via AccessMonster.com]

Hi All,

Does anyone know how to query an LDAP server from Access VBA that is not
a Microsoft / Active Directory (AD) server? I have found a lot of stuff
about how to query AD and have succeeded in doing so. But that does not seem
to transfer to the non-MS LDAP server. It gives the error "Automation error"
"There is no such object on the server."

Thanks,

Clifford Bass

 

[Jeff Boyce]

Clifford

Is there a chance that there's a ODBC driver for the non-AD data store?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Clifford Bass via AccessMonster.com]

Hi Jeff,

Good thought! But no :-(

Clifford Bass

 

[david]

ADSI OLEDB will query any LDAP server provided you can
get the connection and authentication right. Can you query
LDAP using something like ldapsearch?

How is the data stored? Are you able to query
the database using ODBC rather than LDAP?

(david)

 

[Clifford Bass via AccessMonster.com]

Hi David,

The local departmental one is just a huge text file with some sort of
tagged field format. I do not know about the organizational one; however
that is less of a concern. There are no ODBC drivers for the local one. So
ODBC is not an option.

If I do an ldpasearch it works; i.e.:

ldapsearch -h ldap.dept.org.com "sn=bass"

correctly finds and displays my information.

Here is the code that works with Active Directory.


Public Function SearchDirectory( _
ByVal strLastName As String, _
strFirstName As String) _
As String

Dim cnnAD As ADODB.Connection
Dim rstAD As ADODB.Recordset
Dim intCount As Integer
Dim objADsUser As Object
Dim objDS As Object
Dim objIADS As Object
Dim strReturn As String
Dim strSQL As String

On Error GoTo Handle_Error

SearchDirectory = "*** Not Found ***"
If strLastName <> vbNullString And _
strFirstName <> vbNullString Then
Set objDS = GetObject("LDAP://rootDSE")
'Debug.Print objDS.Get("defaultNamingContext")
Set objIADS = GetObject("LDAP://" & _
objDS.Get("defaultNamingContext"))
'Debug.Print objIADS.ADSPath
Set cnnAD = New ADODB.Connection
With cnnAD
.Open "Provider=ADsDSOObject"
strSQL = _
"select ADsPath " & _
"from '" & objIADS.ADSPath & "' " & _
"where displayName='" & Replace( _
strFirstName, "'", "''") & "*" & _
Replace(strLastName, "'", "''") & _
"' and objectClass = 'user' and " & _
"cn <> 'systemmailbox*'"
Set rstAD = .Execute(strSQL)
With rstAD
intCount = 0
Do While Not .EOF
If intCount > 0 Then
strReturn = strReturn & ", "
End If
'Debug.Print .Fields(0).Value
Set objADsUser = GetObject( _
.Fields(0).Value)
strReturn = strReturn & _
objADsUser.displayName
intCount = intCount + 1
.MoveNext
Loop
.Close
If intCount > 0 Then
SearchDirectory = intCount & _
": " & strReturn
End If
End With
.Close
End With
End If

Exit_Function:
On Error Resume Next

Set objADsUser = Nothing
Set objDS = Nothing
Set objIADS = Nothing

If Not cnnAD Is Nothing Then
If Not rstAD Is Nothing Then
If rstAD.State <> _
ADODB.ObjectStateEnum.adStateClosed _
Then
rstAD.Close
End If
Set rstAD = Nothing
End If

If cnnAD.State <> _
ADODB.ObjectStateEnum.adStateClosed _
Then
cnnAD.Close
End If
Set cnnAD = Nothing
End If

Exit Function

Handle_Error:
SearchDirectory = "Error #" & Err.Number & _
": " & Err.DESCRIPTION
Resume Exit_Function

End Function


The local LDAP server does not require authentication. I tried various
substituations in the "Set objDS = GetObject("LDAP://rootDSE")" line without
success (usually automation errors).

GetObject("LDAP://ldap.dept.org.com")
GetObject("LDAP://ldap.dept.org.com/root")
GetObject("LDAP://ldap.dept.org.com/rootDSE")

Any suggestions?

Thanks,

Clifford Bass

 

[david]

Do you know what line the error is coming from?

I would have thought "no such object on the server" might have
happened if there was no defaultNamingContext object on the
server at the line:

objDS.Get("defaultNamingContext"))

or at any later stage when there was no such object on the server
-- not a VBA LDAP problem, just a problem working out what
the structure of your database is. It may not have a top level
attribute "defaultNamingContext" -

although I actually get "No such property in the cache" for that
when I query "directory.verisign.com"

(david)

 

[Clifford Bass via AccessMonster.com]

Hi David,

There are several different errors and a couple different locations
depending on what values I try. Here is some testing code and the results:

======================================
Public Sub TestLDAP1()

TestLDAP2 "LDAP://rootDSE"
TestLDAP2 "LDAP://ldap2.vetmed.wisc.edu"
TestLDAP2 "LDAP://ldap2.vetmed.wisc.edu/root"
TestLDAP2 "LDAP://ldap2.vetmed.wisc.edu/rootDSE"

End Sub

Public Sub TestLDAP2(ByVal strLDAP)

Const cstrDNC As String = "defaultNamingContext"

Dim objDS As Object
Dim strDNCValue As String

On Error Resume Next

Debug.Print
Debug.Print "Testing " & strLDAP
Set objDS = GetObject(strLDAP)
If Err.Number = 0 Then
Debug.Print "Successfully got """ & strLDAP & _
""" object."
strDNCValue = objDS.Get(cstrDNC)
If Err.Number = 0 Then
Debug.Print "Successfully got """ & cstrDNC & _
""" string; value = """ & strDNCValue & """"
Else
Debug.Print "Unable to get """ & cstrDNC & _
""" string."
Debug.Print "Error #" & Err.Number & ": " & _
Err.DESCRIPTION
Err.Clear
End If
Else
Debug.Print "Unable to get """ & strLDAP & _
""" object."
Debug.Print "Error #" & Err.Number & ": " & _
Err.DESCRIPTION
Err.Clear
End If

End Sub
======================================
Testing LDAP://rootDSE
Successfully got "LDAP://rootDSE" object.
Successfully got "defaultNamingContext" string; value = "DC=dept,DC=org,
DC=com"

Testing LDAP://ldap.dept.org.com
Successfully got "LDAP://ldap.dept.org.com" object.
Unable to get "defaultNamingContext" string.
Error #-2147417848: Automation error
The object invoked has disconnected from its clients.

Testing LDAP://ldap.dept.org.com/root
Unable to get "LDAP://ldap.dept.org.com/root" object.
Error #-2147016555: Automation error
A directory service error has occurred.

Testing LDAP://ldap.dept.org.com/rootDSE
Successfully got "LDAP://ldap.dept.org.com/rootDSE" object.
Unable to get "defaultNamingContext" string.
Error #-2147463155: The directory property cannot be found in the cache.
======================================

Interestingly, if I cut through all the detail and look directly at what
goes into the select statement I find for the Active Directory version:

select ADsPath
from 'LDAP://DC=dept,DC=org,DC=com'
where displayName='Clifford*Bass' and objectClass = 'user'
and cn <> 'systemmailbox*'

This of course executes just fine and returns a recordset. I can change
it to this and it will still work:

select ADsPath
from 'LDAP://DC=dept,DC=org,DC=com'
where sn='Bass' and objectClass = 'person'

However, if I follow the pattern and add another DC that presumedly
should get it to use the other LDAP server I get an error:

select ADsPath
from 'LDAP://DC=ldap,DC=dept,DC=org,DC=com'
where sn='Bass' and objectClass = 'person'

Error #-2147217865: Table does not exist.

Thanks for your time; it is appreciated!

Clifford Bass

 

[david]

None of those examples seem to be the example you tested with:

ldapsearch -h ldap.dept.org.com "sn=bass"

So I'm not sure if you are happy yet:~)

In any case, if you can get rootDSE (which I think means that you
are querying an LDAP 3.0 database), then you should be able to
read what the default naming context is called. If not, you should
be able to just query "sn=bass" (which is clearly in the default naming
context, what ever it is called)

I don't understand this at all:

However, if I follow the pattern and add another DC that presumedly
should get it to use the other LDAP server I get an error:

I don't see how adding another DC would get one LDAP server to
query another LDAP server? Maybe in Active Directory, but not
in general.


(david)

 

[Clifford Bass via AccessMonster.com]

Hi David,

Good thing to check. Makes no difference. Passing:

select ADsPath
from 'LDAP://DC=ldap,DC=dept,DC=org,DC=com'
where sn='Bass'

to the cnnAD.Execute gives me:

Error #-2147217865: Table does not exist.

In can be confusing. I am admittedly flying in the dark as I have not
found any helpful clues as how to do this with a non-MS LDAP server and am
trying to adapt code that works with Active Directory. Perhaps that is the
first mistake. Active Directory is at dept.org.com. The server I want to
query is at ldap.dept.org.com. My guess was that adding another DC would in
theory point it to the non-MS LDAP server.

Any clue as to how I can determine what the default naming context is
called? I have found some code for enumerating the mandatory and optional
properties available in an Active Directory. But again, it starts out:

' Connect to the LDAP server's root object
Set objRootDSE = GetObject("LDAP://RootDSE")

' Form a path to the root domain object
strADsPath = "LDAP://" & objRootDSE.Get("defaultNamingContext")

So I am stopped by the same issue in that it requires access
"defaultNamingContext" before it will get the properties.

Appreciate your efforts David!

Clifford Bass

 

[david]

According to your previous post:

Testing LDAP://ldap.dept.org.com
Successfully got "LDAP://ldap.dept.org.com" object.

So you have successfully connected to the LDAP
server at ldap.dept.org.com.

After that, it is only a question of what the structure
is and permissions are for that database.

rootDSE is a standard element for an LDAP 3.0
database, and you have successfully got that:

Testing LDAP://ldap.dept.org.com/rootDSE
Successfully got "LDAP://ldap.dept.org.com/rootDSE" object.

All LDAP 3.0 servers recognise these attribute names:

subschemaSubentry
namingContexts
supportedControl
supportedSASLMechanisms
supportedLDAPVersion
supportedExtension
altServer

(the attributes will be absent if they are empty)

Since you don't have a "DefaultNamingContext", because that is an Active
Directory Attribute, let's see what naming contexts you do have:
set objds = getobject("LDAP://ldap.dept.org.com/rootDSE")

vlist = objds.Get("namingContexts")
For each s in vlist
msgbox s
next

And query the first name Context:

set obj = getobject((LDAP://ldap.dept.org.com/"& vlist(0) )

msgbox obj.name

(david)

 

[Clifford Bass via AccessMonster.com]

Hi David,

That provides some, but limited progress. Only two of the attributes
are recognized and they are:

subschemaSubentry = "cn=schema"
nameContexts = ""

Does that mean it is not an LDAP 3.0 server? Obviously with a blank
nameContexts value, it does not enumerate any.

----Later...

Did some online searching on those attributes and discovered information
about a program named Ldp. Apparently I have that program and when I run it
and connect to the server I get:

---------------------
Established connection to ldap.dept.org.com.
Retrieving base DSA information...
Result <0>: (null)
Matched DNs:
Getting 1 entries:1> objectclass: top;
1> namingContexts: ;
1> subschemaSubentry: cn=schema;
2> supportedCapabilities: 1.2.840.113556.1.4.800; 1.2.840.113556.1.4.1791;
2> supportedControl: 1.2.840.113556.1.4.319; 1.2.840.113556.1.4.473;
2> supportedLDAPversion: 2; 3;
8> supportedSASLMechanisms: LOGIN; PLAIN; CRAM-MD5; DIGEST-MD5; GSSAPI; GSS-
SPNEGO; MSN; NTLM;
---------------------

I tried playing around in it for awhile, but often did not know what to
enter into various dialogs. Any of that help? Anything I would do in that
program that would help point in the right direction to go in VBA?

Again, Thanks Much,

Clifford Bass

 

[david]

It appears to be a very flat database.

program that would help point in the right direction to go in VBA?

It's not the VBA. The VBA already works.

Having a blank nameContexts value isn't a problem, as you
saw with your ldapsearch.

ldapsearch -h ldap.dept.org.com "sn=bass"

set obj = getobject("LDAP://ldap.dept.org.com/" & "sn=bass")

to enumerate the objects at the base level:

set objds = getobject(LDAP://ldap.dept.org.com)

for each obj in objds
msgbox obj.name
next

But I do not know about LDAP, nor about your LDAP server. Now that you have
the VBA sorted out, you need to work out what the structure of your database
is.

(david)

 

[Clifford Bass via AccessMonster.com]

Hi David,

Sigh! That looked hopeful; but did not work "-(. I won't keep
pestering you--you have been very kind in your attempts to help. I will
putter around some more on my own. In the event I succeed I will post back
with the solution.

Thanks Again,

Clifford Bass