How get multi value data form Active Directory using SQL

Question

Is it possible to get multi value properties form AD like description, memberOf. if I run simply by adding memberOf this gives error

select * 
FROM OPENQUERY(ADSI,'SELECT initials, samAccountName, displayName, distinguishedName, mail, memberOf FROM ''LDAP://DC=corp, DC=contoso, DC=com'' WHERE objectClass=''Person''')

Error:

Msg 7346, Level 16, State 2, Line 1
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.

This is because of memberOf is multi valued property in Active Directory. I am using SQL Server 2008 R2

Answer 1

No, you cannot do this - and there's no "trick" or hack to get it to work, either.

The ADSI provider for SQL Server is rather limited - not supporting multi-valued attributes is one of those limitations.

So you'll need to find another way to do this, e.g. by using SQl-CLR integration and accessing the Active Directory through .NET, or by e.g. exposing the data you need as a web service that you consume from SQL Server.

Answer 2

While you can't use ADSI to return memberof you can query memberof so if you have a group you want to check against you can do the following where extenstionAttribute3 is the employee ID:

SELECT displayName
FROM OPENQUERY(ADSI,
'SELECT displayName
FROM ''LDAP://DC=company,DC=com''
WHERE memberof = ''CN=staff,OU=SharepointGroups,DC=company,DC=com''
AND extensionAttribute3 = ''12345678''
')

If the return value is not null then you can assume the user is part of the group.