Querying email addresses other than the primary in Active Directory

Question

I'm trying to query Active Directory from a stored procedure using OPENQUERY and add the values to a temporary table. I've got everything working, except when I add the 'proxyAddresses' field to my query, it throws this error:

"OLE DB error trace [OLE/DB Provider 'ADSDSOOBject' IRowset::GetData returned 0x80040e21: Data status returned from the provider: [COLUMN_NAME=proxyAddresses STATUS=DBSTATUS_E_CANTCONVERTVALUE]]. Msg 7346, Level 16, State 2, Line 2 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."

Has anyone ever had experience with querying the 'ProxyAddresses' property from SQL? I think I read somewhere that it was returned as an array. Maybe that's the problem?

Thanks!

Answer 1

I guess the problem here is that proxyAddresses can be multi-valued (i.e. contains multiple values) and I suspect OPENQUERY in SQL Server can't deal with that. Not sure if there's anything you can do to get this to work, really.

Found this in an article on CodeProject which seems to confirm your and my suspicion:

Limits

Unfortunately, there are some Active Directory values that can’t be read with the ADSI linked servers. Those values are called “Array Values”. Array values in the Active Directory are fields that allow to insert unlimited multiple values. For example, if you store multiple phone numbers in the Active Directory General tab under “Telephone numbers/Other…”, you won’t be able to read those via ADSI into your SQL Server.

Can you skip that and query for those addresses in C# ? I could show you a sample of that.

Marc

UPDATE:
OK, here's the update with the method to grab a multi-valued attribute into a list of strings (assuming you already have a DirectoryEntry that represents that user):

public List<string> GetMultiValues(DirectoryEntry entry, string propertyName)
{
  if(entry == null)
  {
     return null;
  }

  List<string> results = new List<string>();

  if (entry.Properties.Contains(propertyName))
  {
     foreach (object propertValue in entry.Properties[propertyName])
     {
        results.Add(propertValue.ToString());
     }
  }

  return results;
}

You can now called this function like this:

List<string> proxyAddresses = GetMultiValues(userEntry, "proxyAddresses");

Cheers! Marc

Answer 2

Did you try to CAST the proxyAddresses field manually in your OPENQUERY statement?