For historical reasons, I have the personalisation information for some users stored in a Sun LDAP server, and I have some data about them stored in a Microsoft SQL Server. Unfortunately we are using Microsoft Reporting services.

In the database users are referred to by a system wide unique identifier. The mapping from this unique identifier to the user’s display name is stored in the LDAP directory server.

Fine, I thought, I think SQL Server has a way to do queries which span multiple data sources. And I think Microsoft has a way to talk to Active Directory servers over LDAP. So, I should be able to co-opt that for my purpose.

Only it turns out that you can’t.

[OLE/DB Provider ‘ADSDSOObject’ IRowset::GetData returned 0x40eda: Data status returned from the provider: [COLUMN_NAME=sn STATUS=DBSTATUS_E_CANTCONVERTVALUE]]

A lot of LDAP attributes are marked as multi-valued in the schema. And, even though I know that there will only be one value (I am happy to treat multiple values as an error condition) – the ADSDSOObject just doesn’t support multi-valued attributes, no matter how many values there actually are. Its in the distributed query documentation. For various other reasons I can’t change the LDAP schema.

The first limitation is that multivalued properties cannot be returned in the result set to SQL Server. ADSI will read schema information from the LDAP server that defines the structure and syntax of the classes and attributes used by the server. If the attribute that is requested from the LDAP server is defined in the schema as being multi-valued it cannot be returned in an OPENQUERY statement.

So near, and yet so far away as to be nearly incomprehensible. Why, oh why did they design it that way?