在现代IT环境中，Active Directory (AD) 是一个关键的组件，用于管理用户、计算机、组和其他对象。随着企业对数据的依赖不断增加，将AD中的数据集成到SQL Server数据仓库中变得尤为重要。本文将介绍如何使用CLR存储过程从AD获取数据，并将其更新到SQL Server数据仓库中。
CLR存储过程是.NET程序集，可以在SQL查询中的T-SQL代码内调用。这些程序集可以执行几乎所有C#和.NET在常规Windows服务中可以执行的操作。在本文中，将使用Active Directory.NET程序集来访问AD数据。提供了两个存储过程：
此外，还包含了一个可选的数据仓库数据库AD_DW的代码。
这个项目起源于需要更新一个包含来自Active Directory信息的SQL数据仓库。一直在用那个数据库（AD_DW）来生成各种报告。问题是使用的Powershell脚本更新AD_DW数据库相当笨拙。在寻找更好的解决方案后，没有找到任何免费的、认为更好的解决方案，所以决定创建这个解决方案，并在本文中介绍。
在SQL服务器（SQL 2012或更高版本）上安装这个相当简单。只需下载GetADobjects_release.zip文件，并按照Deploy GetADobjects.pdf文档中的说明操作。假设SQL服务器已经安装了所需的.NET程序集：
这些程序集位于C:\Windows\Microsoft.NET\Framework64\v4.0.30319\文件夹中。如果需要，这些将在服务器上安装Active Directory Module for Windows Powershell功能时安装。
请注意，假设SQL服务器服务正在以域用户身份运行，这是因为当CLR SP被调用时，它将以SQL服务帐户身份运行。在域中查询Active Directory需要一个域用户。
安装代码后，可以使用提供的测试脚本来尝试这个。TestScriptUsingTempTables.sql将获取AD中的所有对象到临时表中，这对于查看表结构非常有用。ExportPhotosToFiles.sql可以用来将所有照片导出到文件中，这对于验证照片数据是否可用非常有用。
本文提供了两个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对象GUID
[Width] [int] NULL, -- 照片宽度，以像素为单位
[Height] [int] NULL, -- 照片高度，以像素为单位
[Format] [nvarchar](6), -- 照片格式 - jpg, png等
[Photo] [varbinary](max) NULL -- 照片二进制数据
请注意，返回的是用户的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函数。