Saturday, February 12, 2011

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?

  • 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

0 comments:

Post a Comment