Accessing Active Directory Role Membership through LDAP using SQL Server 2005

Question

I would like to get a list of Active Directory users along with the security groups they are members of using SQL Server 2005 linked servers. I have the query working to retrieve records but I'm not sure how to access the memberOf attribute (it is a multi-value LDAP attribute).

I have this temporary to store the information:

DROP TABLE #ADUSERGROUPS
CREATE TABLE #ADUSERGROUPS 
(
sAMAccountName varchar(30),
UserGroup varchar(50)
)

Each group/user association should be one row.

This is my SELECT statement:

SELECT sAMAccountName,memberOf
FROM OpenQuery(ADSI, '<LDAP://hqdc04/DC=nt,DC=avs>;
                (&(objectClass=User)(sAMAccountName=9695)(sn=*)(mail=*)(userAccountControl=512));
                sAMAccountName,memberOf;subtree')

I get this error msg:

OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IRowset::GetData returned 0x40eda: Data status returned from the provider: [COLUMN_NAME=memberOf STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=sAMAccountName STATUS=DBSTATUS_S_OK]]. 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.

Answer 1

Looks like this is a limitation that cannot be directly overcome: - http://stackoverflow.com/questions/1766061/tsql-how-to-get-a-list-of-groups-that-a-user-belongs-to-in-active-diretory. OpenQuery cannot handle multi-valued attributes.

I ended up writing a .NET CLR job to handle this.