Active Directory 数据导入 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4885337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Active Directory data into SQL table
提问by Scott Hymanson
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
斯科特
回答by David Archer
The way we do this for a LARGEAD environment:
我们为大型广告环境执行此操作的方式：
- Nightly batch process that runs AdFind(freeware tool) to execute an LDAP query and dump it out to CSV files
- BCP(built-in SQL command line tool) to bulk import the CSV files into import tables in the SQL database
- Stored procedure (executed with osql) to take the data from the import table and add/update records in the main tables
- 夜间批处理运行AdFind（免费软件工具）以执行 LDAP 查询并将其转储到 CSV 文件
- BCP（内置 SQL 命令行工具）将 CSV 文件批量导入 SQL 数据库中的导入表
- 存储过程（使用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 分钟。
回答by mslliviu
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
回答by marc_s
If you're on .NET 3.5, I would use the new System.DirectoryServices.AccountManagementnamespace 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.
基本上，您需要设置一个容器 (a PrincipalContext)，然后枚举您想要处理的用户。遍历这些并提取您需要的信息，并将其提供给 SQL Server。
回答by Nick
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. MySQLor Microsoft SQL Server.
有不同的方法可以做到这一点。我使用 PHP 从我们的 Active Directory 中获取数据。请查看PHP 文档中的“轻量级目录访问协议”一章。使用 PHP 填充数据库也很容易，例如MySQL或Microsoft SQL Server。
