[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5698
  • Last Modified:

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.
0
smcdrc
Asked:
smcdrc
  • 2
1 Solution
 
Ted BouskillSenior 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now