使用 CLR 存储过程查询 Active Directory

Snorri Kristjansson

4.80/5 (4投票s)

2016年9月17日

CPOL

4分钟阅读

23247

1011

一个 SQL Server CLR 存储过程，可以在 T-SQL 代码中直接查询 Active Directory

也在 GitHub 上 - https://github.com/snorrikris/GetADobjects 和 https://github.com/snorrikris/UpdateAD_datawarehouse

引言

CLR 存储过程是一个 .NET 程序集，可以在 SQL 查询的 T-SQL 代码中调用 - 该程序集几乎可以做任何 C# 和 .NET 在普通 Windows 服务中可以做的事情。在这种情况下，通过 Active Directory .NET 程序集访问 AD 中的数据。提供了两个存储过程：clr_GetADobjects 和clr_GetADusersPhotos。还包括（可选的）数据仓库数据库 AD_DW 代码。

例如 - 从 Active Directory 获取所有用户

DECLARE @ADpath nvarchar(64) = 'LDAP://DC=contoso,DC=com';
DECLARE @ADfilter nvarchar(64) = '(&(objectCategory=person)(objectClass=user))';
DECLARE @Members XML;
EXEC clr_GetADobjects @ADpath, @ADfilter, @Members OUTPUT;

背景

这个项目源于我需要更新一个包含 Active Directory 信息的数据 SQL 数据仓库。我一直使用那个数据库 (AD_DW) 来生成各种报告。问题是我当时使用的 PowerShell 脚本更新 AD_DW 数据库相当笨拙。我寻找一个更好的解决方案，在找不到（免费的）我认为更好的方案后，我决定创建我在这里这篇文章中介绍的东西。

安装 CLR 存储过程

在 SQL 服务器上安装此程序 (SQL 2012 或更高版本) 相当简单。只需要几分钟。只需下载 GetADobjects_release.zip 文件并按照 Deploy GetADobjects.pdf 文档中的说明操作。假定 SQL 服务器已在 C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ 文件夹中包含所需的 .NET 程序集；System.DirectoryServices 和 System.DirectoryServices.AccountManagement。

如果需要，当您在服务器上安装 **Windows PowerShell 的 Active Directory 模块** 功能时，它们将被安装。

请注意，说明假定您将为此创建一个数据库 - 但如果您愿意，可以使用您自己的任何数据库来包含程序集。

重要的是要注意，假定 SQL 服务器服务是以域用户身份运行的 - 这是因为当调用 CLR SP 时，它将以 SQL 服务账户身份运行。域用户需要查询您域中的 Active Directory。

安装代码后，您可以使用提供的测试脚本进行尝试。

TestScriptUsingTempTables.sql 将把 AD 中的所有对象获取到临时表中 - 非常有助于查看表结构。

ExportPhotosToFiles.sql 可用于将所有照片导出到文件 - 有助于验证照片数据是否可用。

可以使用 UpdateAD_DataWarehouse_Create(modified).sql 脚本创建 Active Directory 对象数据库的数据仓库，它将创建该数据仓库所需的所有表和其他对象。Create_SQL_Agent_job.sql 脚本将创建一个 SQL Agent 作业，您可以使用它来更新 AD_DW 数据库。如果 SQL Agent 不可用，您可以在计划任务中运行 UpdateAD_DW.ps1 PowerShell 脚本来更新 AD_DW。

Using the Code

本文档提供了两个 Visual Studio 2015 解决方案。GetADobjects 包含 CLR 存储过程的源代码，UpdateAD_datawarehouse 包含 AD_DW 数据库的 SQL 源代码。

代码归结为两个 CLR 存储过程；clr_GetADobjects 和 clr_GetADusersPhotos。两者都接受 LDAP 格式的 AD 路径和 AD 筛选器 [^] 作为参数。clr_GetADobjects 接受一个额外的参数 @MemberList，当查询 AD 组时，此参数将组的成员资格数据作为 XML 数据返回。

clr_GetADusersPhotos SP 返回一个表，其中包含 ADfilter 参数中指定的所有用户。表格式始终相同。

[ObjectGUID] [uniqueidentifier] NOT NULL, -- AD object GUID
[Width] [int] NULL,                       -- photo width in pixels
[Height] [int] NULL,                      -- photo height in pixels
[Format] [nvarchar](6),                   -- photo format - jpg, png etc
[Photo] [varbinary](max) NULL             -- photo binary data

请注意，返回的是用户的 ObjectGUID - 而不是您可能期望的用户名 - GUID 用作 AD 中所有对象的（主）键（请记住 Active Directory 是一个数据库）。查看 TestScriptUsingTempTables.sql 脚本，了解如何在查询中连接用户表。

clr_GetADobjects SP 返回的表格式取决于预期的 AD 对象：用户、联系人、计算机、组或 WellKnownSIDs （注意 - 每种 AD 对象类型的表格式不同）。表格式在 ADtableDefinitions.cs 代码文件中的 ADcolsTable 类中生成。例如，用户表类型有 69 列。我使用 Excel 文档来帮助我跟踪所有表和列 - 如果您需要修改代码，Excel 文档就在源代码文件中。

重要的是要注意 clr_GetADobject 使用 @ADfilter 参数来确定返回哪种类型的表。即使 AD 筛选器可以指定多种类型，也假定只返回一种类型的 AD 对象。不支持此操作。

已投入大量工作以使代码运行速度尽可能快，并提取尽可能多的 AD 属性。特别有趣的是所谓的计算属性，这些属性被证明是最耗时的，其中 UserCannotChangePassword 标志是最麻烦的。事实证明它不是一个标志，而是 AD 对象（用户）上的一个权限，如果您有兴趣，可以查看 IsUserCannotChangePassword 函数。

是的，我知道我可以使用 .NET AD 程序集来获取这些属性，但事实证明这种方法非常慢 - 例如，获取 500 多个用户花了 2 分钟多 - 但使用我的代码只花了 18 秒。

历史

版本 1 - 2016 年 9 月