Query Active Directory and Return Member or MemberOf attributes

I am trying to query active directory to get the groups and their members.  I am trying to conduct access reviews.  When I try to get the 'member' attribute, I get the following error


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

From what I understand, the member attribute is returned as an array.  Is there a way to get that into SQL Server 2000?  I know I can get this via .net, but I would like to keep it all in SQL Server.
LVL 2
smcdrcAsked:
Who is Participating?
 
Ted BouskillConnect With a Mentor Senior Software DeveloperCommented:
CREATE TABLE ##Groups ( CN VARCHAR(128), DN VARCHAR(1024), Email VARCHAR(128), ADSPath VARCHAR(1024) );
CREATE TABLE ##Users ( DistributionGroup VARCHAR(128), FirstName VARCHAR(50), LastName VARCHAR(50), EmailAddress VARCHAR(128) );

DECLARE @sql VARCHAR(1024)

SET @sql = 'INSERT INTO ##Groups (CN, DN, Email, ADSPath) SELECT CN, distinguishedName DN, mail Email, ADSPath FROM OpenQuery(ADSI, ''<LDAP://domaincontroller.yourdomain.com:389/DC=yourdomain,DC=com>;(&(objectClass=Group));cn, distinguishedName, mail, ADSPath;subtree'') ORDER BY distinguishedName';
EXEC(@sql);

DECLARE @CN VARCHAR(128)
DECLARE @DN VARCHAR(1024)

SELECT TOP 1 @CN = CN, @DN = DN FROM ##Groups

WHILE EXISTS(SELECT DN FROM ##Groups WHERE DN > @DN)
BEGIN

      SET @sql = 'INSERT INTO ##Users (DistributionGroup, Firstname, LastName, EmailAddress) SELECT '''+@CN+''' [Distribution Group], ISNULL(givenName, '''') FirstName, ISNULL(sn, '''') LastName, mail EmailAddress FROM OpenQuery(ADSI, ''<LDAP://domaincontroller.yourdomain.com:389/DC=yourdomain,DC=com>;(&(objectClass=User)(memberOf='+@DN+'));givenName, sn, mail;subtree'')';
      EXEC(@sql)

      SELECT TOP 1 @CN = CN, @DN = DN FROM ##Groups WHERE DN > @DN
END

SELECT * FROM ##Groups;
SELECT * FROM ##Users;

DROP TABLE ##Groups;
DROP TABLE ##Users;
0
 
smcdrcAuthor Commented:
Thanks, Worked great the first time.
0
 
Ted BouskillSenior Software DeveloperCommented:
You are welcome.  This is a copy of what I did for someone else.  The tricky bit was learning how to do LDAP queries.
0
 
sheajim1Commented:
I'm running the code exactly as shown above and although it appears to work fine it fails to return all the users in a group, stopping at 12 or 13 out of 22.  Is there someting you are aware of that would prevent all users from being returned?  Running a utility such as ldap.exe I am able to see all members of a group.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.