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

Question

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?

Answer 1

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)

Answer 2

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.

Answer 3

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

Answer 4

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