Importing Active Directory Users and Groups into SQL Server (possibly via c#)

I need to import all ad groups in a few OUs into a table in SQL Server 2008. Once I have those I need to import all the members of those groups to a different table. I can use c# to do the work and pass the data to SQL server or do it directly in SQL server.

Suggestions on the best way to approach this?

From stackoverflow Arry

• Add a Linked Server to your SQL Server and query the Active Directory via LDAP queries. This here described this quite well:

  Create a SQL Server View of your AD Users, Brendan Tompkins (MVP)

  Jeremy : I think you can only return 1000 active directory objects from within SQL server, because you can't do a paged query from SQL server.
  From Mudu

• Mudu,

  Thanks, that has got me started. I can query users and groups from the domain that the SQL Server is in. Problem is I want to query against a different domain, ideas how I would do this.

  Also, how would I get group membership?

  Thanks in advance.

  From Arry

• Arry,

  I don't know exactly, but found some links that may help you. I think the hottest track is this expression:

  "(&(objectCategory=Person)(memberOf=DN=GroupName, OU=Org, DC=domain,
  DC=com))"
  

  I found it in LDAP Query for group members on a ColdFusion community's site. I'm more or less sure the filter can easily be applied to your query. I'm sorry, but I cannot test it, because I have no AD around here.

  This one could also be a bit (but less) interesting:

  http://forge.novell.com/pipermail/cldap-dev/2004-April/000042.html

  Hope this helps, cheers,

  Matthias

  Eddie : The filter described in this answer is the best way to return all users within an Active Directory Security Group that I have found.
  From Mudu

• I'm having the same issue. I need to query users from our internal domain and users from our external domain. The OUs and Groups that I use for my application are in the external domain. The SQL Server is in the internal domain. I can query the internal domain just fine but get the following error when I try the external domain from SQL Server:

  Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "(null)".

  I'm using OPENROWSET as follows:

  select * FROM OPENROWSET('ADSDSOObject', 'User ID=domain\User;Password=XXX;adsdatasource;', 'SELECT cn, mail, co, distinguishedName, displayName FROM ''LDAP://DC=XX,DC=XX'' where objectClass = ''User'' ')

  This works on our internal domain. Any pointers would be greatly appreciated.

  Thanks, Mike

  From