Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 291
Scott Jackson Avatar asked Feb 03 '11 11:02

Scott Jackson


People also ask

Can you query Active Directory from SQL?

The first method to query Active Directory from SQL Server is by using OpenRowSet. If you want to know more about openrowset please read this article. You can access information from Active directory by executing the following query.

Can SQL be used with network database?

By default, the use of network database files that are stored on a networked server or a Network Attached Storage (NAS) server is not enabled for SQL Server. However, you can configure SQL Server to store a database on a networked server or NAS server.


2 Answers

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.

like image 132
David Archer Avatar answered Oct 27 '22 16:10

David Archer


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

like image 30
mslliviu Avatar answered Oct 27 '22 14:10

mslliviu