Query AD from sql server 2008 Description field gives error

Question

I am trying to query the Active Directory with the following SQL query, which works perfectly.

SELECT * FROM OpenQuery(ADSI, 
                         'SELECT title, displayName, sAMAccountName, givenName, 
                                 telephoneNumber, facsimileTelephoneNumber, sn,  
                                 mail, physicalDeliveryOfficeName
                          FROM ''LDAP://DC=mydomain,DC=org'' 
                          WHERE sAMAccountName = ''myUser''')

When I add the description attribute I get the following error:

Msg 7346, Level 16, State 2, Line 6
Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.

Answer 1

The SQL/ADO query capabilities into Active Directory are very limited - I would try to avoid using them if ever possible.

The reason here is that the description attribute in Active Directory is multi-valued - it could potentially contain multiple values (something that's impossible in a relational database model).

Therefore, the SQL/ADO query provider cannot read any of those multi-valued attributes from LDAP - there's no way or option or checkbox to enable that - it's just not possible.

Answer 2

I have the same problem getting the Description field from AD while attempting to use ADsDSOObject and ADO.

you have to inter-ate through the values with a For Each or Do until ... EOF type of statement.

Here is a good example