CODEWITHSUNDEEP
sqlactive-directory
Scott Jackson
Scott Jackson

Reputation: 101

Active Directory data into SQL table

How would I extract Active Directory info (Username, first name, surname) and populate an SQL table with the results?

Many thanks

Scott

Upvotes: 10

Views: 31258

Answers (4)

mslliviu
mslliviu

Reputation: 1138

If you just need it in SQL, I'm using the code below

INSERT...
SELECT A.SAMAccountName, A.Mail,  A.displayName  FROM
    (SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn, userAccountControl,mail  
    FROM ''LDAP://domain.ro/DC=domain,DC=ro'' where objectClass = ''User''')
    WHERE (sn is not null) and (givenName is not null) and (mail is not null) )A

where ADSI is a linked server created based on this: http://msdn2.microsoft.com/en-us/library/aa772380(VS.85).aspx

Upvotes: 4

David Archer
David Archer

Reputation: 2121

The way we do this for a LARGE AD environment:

  1. Nightly batch process that runs AdFind (freeware tool) to execute an LDAP query and dump it out to CSV files
  2. BCP (built-in SQL command line tool) to bulk import the CSV files into import tables in the SQL database
  3. Stored procedure (executed with osql) to take the data from the import table and add/update records in the main tables

We pull 145k users, 80k groups, 130k computers from 10 domains in about 2 hours from start to finish. This includes pulling accurate LastLogon information for the users and computers which requires you to hit each domain controller. Without that, the process takes about 30 minutes.

Upvotes: 8

Niki
Niki

Reputation: 1924

There are different ways to do that. I use PHP to get data out of our Active Directory.Take a look at the chapter "Lightweight Directory Access Protocol" in the PHP Documentation. It's also easy to populate a database using PHP, e.g. MySQL or Microsoft SQL Server.

Upvotes: 1

marc_s
marc_s

Reputation: 754973

If you're on .NET 3.5, I would use the new System.DirectoryServices.AccountManagement namespace for this.

Learn about it here:

Managing Directory Security Principals in the .NET Framework 3.5

Basically, you'd set up a container (a PrincipalContext) and then enumerate the users you want to deal with. Loop over those and extract the info you need, and feed that into SQL Server.

Upvotes: 3

Related Questions