LDAP Query

[snufse]

I have created a sp querying LDAP such as:

ALTER PROCEDURE [dbo].[sp_LDAP_Query] 

as  

set nocount on

select  * 
from  openquery(ADSI, ' 
select  title, sAMAccountName, displayName, userAccountControl
from    ''LDAP://DC=xxx,DC=COM'' 
where   objectCategory = ''User''') 
where title is not null and (userAccountControl <> '66050' and userAccountControl <> '514')
order by displayName

I sign on to Sequel Server 2005 in Windows Authentication mode and execute the sp:

exec vecellio.dbo.sp_LDAP_Query

A result set is returned. So far, so good.


Now in ASP I have a connection string like:

<add key="ConnectionString" value="Data Source=VGIBESQL;Initial Catalog=XXX;Integrated Security=SSPI;"/>

also using Windows Authentication and code to call the same sp:

If mySqlConnection.State = Data.ConnectionState.Closed Then
            mySqlConnection.Open()
        End If

        Try
            mySqlCommand.Connection = mySqlConnection
            mySqlCommand.CommandType = Data.CommandType.StoredProcedure
            mySqlCommand.CommandText = ("vecellio.dbo.sp_LDAP_Query")
            mySqlCommand.Connection = mySqlConnection
            mySqlDataAdapter.SelectCommand = mySqlCommand
            mySqlDataAdapter.Fill(mySqlDataTable)
            Session("myMenuDataTable") = mySqlDataTable
        Catch ex As Exception
            Message = ex.ToString
            myError = True
        End Try

Now I get error on the "mySqlCommand.ExecuteScalar()" line:

System.Data.SqlClient.SqlException: An error occurred while preparing the query " 
select  title, sAMAccountName, displayName, userAccountControl
from    'LDAP://DC=Vecelliogroup,DC=COM' 
where   objectCategory = 'User'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

Any ideas???

[gep13]

Hey,

I think this question might be best asked over in the Database forum.

Gary

[snufse]

I have tried to create a LDAP query in the code behind instead of calling a sp and using linked server that apparently have some authority issues. Below is the query I have:

Dim prefixText As String = "vg"
Dim cn As New Data.OleDb.OleDbConnection("Provider=ADSDSOObject")
Dim cmd As Data.OleDb.OleDbCommand = cn.CreateCommand()
cmd.CommandText = "select sAMAccountName, displayName, title, userAccountControl from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User' and title <> ' ' and (userAccountControl <> '66050' and userAccountControl <> '514') and sAMAccountName like '%" & prefixText & "%'"
 Try
            Dim da As New Data.OleDb.OleDbDataAdapter(cmd)
            da.Fill(mySqlDataTable)

Value of my select string is: select sAMAccountName, displayName, title, userAccountControl from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User' and title <> ' ' and (userAccountControl <> '66050' and userAccountControl <> '514') and sAMAccountName like '%vg%'

Getting error:

System.Data.OleDb.OleDbException: 'ADSDSOObject' failed with no error message available, result code: DB_E_ERRORSINCOMMAND(0x80040E14).

Now, if I leave out the "title" and the "sAMAccountName" from my select query it works fine. I am trying to get records where the "title" is not empty and name matches my prefix. Maybe somebody might be able to help?

[mendhak]

Try with objectClass=''User''. From this example:


SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whaever,DC=domain,DC=org'' where objectClass = ''User''')

Found here: http://codebetter.com/blogs/brendan....2/19/4746.aspx

[snufse]

Hi mendhak,

Both ObjectClass=User and ObjectCategory=User works.

I have problems with selecting where "title" is empty and sAMAccountName using the LIKE keyword.

The query works fine without them. I need to use the "LIKE" as this is part of Ajax AutocompleteExtender for textbox in a GridView (which you mendhak gave me solution to in another post). I think my problem is syntax related.

Dim prefixText As String = "vg"
cmd.CommandText = "select sAMAccountName, displayName, title, userAccountControl from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User' and title <> ' ' and (userAccountControl <> '66050' and userAccountControl <> '514') and sAMAccountName like '&#37;" & prefixText & "%'"

Thank you

[mendhak]

OK, you may need the wildcard * instead of &#37;

cmd.CommandText = "select sAMAccountName, displayName, title, userAccountControl from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User' and title <> ' ' and (userAccountControl <> '66050' and userAccountControl <> '514') and sAMAccountName like '*" & prefixText & "*'"

[snufse]

Tried:

cmd.CommandText = "select sAMAccountName, displayName, title, userAccountControl from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User' and title <> ' ' and (userAccountControl <> '66050' and userAccountControl <> '514') and sAMAccountName like '*" & prefixText & "*'"

Value: select sAMAccountName, displayName, title, userAccountControl from 'LDAP://DC=Vecelliogroup,DC=COM' where objectCategory = 'User' and title <> ' ' and (userAccountControl <> '66050' and userAccountControl <> '514') and sAMAccountName like '*vgwprja*'

Still getting error:

System.Data.OleDb.OleDbException: 'ADSDSOObject' failed with no error message available, result code: DB_E_ERRORSINCOMMAND(0x80040E14).


Any other good ideas? Thank you.

[mendhak]

OK, I'm going to move this to the VB.NET forum where there are more members and therefore a higher chance of someone else with LDAP knowledge.