Accessing Active Directory Role Membership through LDAP using SQL Server 2005
- by David Neale
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.
