Question Locked

SQL syntax for querying AD group membership

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I've established a connection to Active Directory through Crystal Reports. Now I need to write an SQL select statement to return all computer accounts and their group memberships. My statement returns all the computer accounts in the target OU, but the MemberOf field is empty. I've included below my query statement and would welcome any suggestions...
*******************************************
Select CN, memberOf
From 'LDAP://OU=Managed Accounts, OU=Central Valley Service Area, DC= root, DC=sutterhealth, DC=org'
where ObjectClass='computer'
*********************************************

4 replies

by slksport

If you're using SQL Server 2005 (you don't say which version you're using), then you can write your query in any .NET language like C# or VB.NET. That in turn means you can call out to a class in an assembly that will read this data for you and return it in any kind of handy container (probably a collection, but whatever).

Which means I'd look for some open source project on sourceforge.net or codeplex or any .NET-friendly open source site, and find a project that already handles running a quesry against an AD forest. Use that to build your assembly with a public class that returns the neccesary data (with appropriate auth protections).

If you're using an earlier version of SQL Server you can still perform the same trick, but you'll have to do it via a COM object, with all of the attendant problems (potential memory leaks, early binding those pesky ids just right versus late binding and lousy performance, etc.).

Rule of thumb: if it doesn't seem simple to do in the thing you're trying to do it in, maybe you're trying to shoehorn the funcitonality in the wrong place, and it's time to abstract it out to a seperate class encapsulated behind an interface.

Hope that helps,

Ron

by smilingsheep

I think you're going to have to create a second query to find out what groups the computers are in. Logically like this: LDAP query AD for all groups and enter into dictionary. For each computer in resultset, report "YES" if the resultset(nextobj) is in the dictionary(i). Next i, Next.

At least that's how I'd do it in vbs.

This discussion is closed to new replies.