将Active Directory数据导入SQL表

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

如何提取Active Directory信息(用户名,名字,姓氏)并使用结果填充SQL表?

Many thanks

Scott

4 个解决方案

#1

The way we do this for a LARGE AD environment:

我们为大型AD环境执行此操作的方式:

Nightly batch process that runs AdFind (freeware tool) to execute an LDAP query and dump it out to CSV files

每晚批处理运行AdFind(免费软件工具)以执行LDAP查询并将其转储到CSV文件

BCP (built-in SQL command line tool) to bulk import the CSV files into import tables in the SQL database

BCP(内置SQL命令行工具)将CSV文件批量导入SQL数据库中的导入表

Stored procedure (executed with osql) to take the data from the import table and add/update records in the main tables

存储过程(使用osql执行)从导入表中获取数据并在主表中添加/更新记录

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.

我们从开始到结束的大约2个小时内从10个域中抽取了145k用户,80k组,130k台计算机。这包括为需要您命中每个域控制器的用户和计算机提取准确的LastLogon信息。没有它,这个过程大约需要30分钟。

#2

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

如果您只需要在SQL中使用它,我正在使用下面的代码

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

其中ADSI是基于此创建的链接服务器:http://msdn2.microsoft.com/en-us/library/aa772380(VS.85).aspx

#3

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

如果您使用的是.NET 3.5,我会使用新的System.DirectoryServices.AccountManagement命名空间。

Learn about it here:

在这里了解它:

Managing Directory Security Principals in the .NET Framework 3.5

管理.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.

基本上,您需要设置一个容器(PrincipalContext),然后枚举您想要处理的用户。循环遍历这些并提取所需的信息,并将其提供给SQL Server。

#4

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.

有不同的方法可以做到这一点。我使用PHP从Active Directory中获取数据。请查看PHP文档中的“轻量级目录访问协议”一章。使用PHP填充数据库也很容易,例如MySQL或Microsoft SQL Server。

#1