65.9K
CodeProject 正在变化。 阅读更多。
Home

构建混合 Active Directory 和 SQL 表数据源视图

noahhath
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.40/5 (3投票s)

2009年6月8日

CPOL

11分钟阅读

viewsIcon

33427

构建混合式 Active Directory 和 SQL 表数据源视图。

入门

前段时间，我被要求提供一个 SQL Server 2000 员工信息数据库视图，供多个 Web 应用程序管理我们部门的员工。其中一些应用程序用于显示员工信息，另一些则根据员工的职称计算充电费率或邮箱存储空间。大部分信息存储在我们的本地 Active Directory 中，但并非全部。某些信息，例如充电费率或员工照片，存储在本地员工数据库表中。员工数据库表和 Active Directory 通过员工用户名进行链接，该用户名在两个数据源中都是唯一值。因此，我构建的 SQL Server 2000 数据库视图将从这两个必须以某种方式链接在一起的数据库源获取数据。

要求

对于这个项目，我使用 SQL Server 2005 客户端访问 SQL Server 2000 数据库。

实施步骤

因为这是 SQL Server 2000 数据库，我面临的挑战是将所有 ASP.NET C# 代码转换为纯 SQL，以从 Active Directory 和数据库表中接收和链接数据。为了开始时保持简单，并为我后面更详细的阐述提供参考，我想向您展示我为实现目标所经历的所有步骤

  1. 在 SQL Server 2000 中安装链接的 ADSI 服务器，以便通过存储过程从 Active Directory 接收数据。
  2. 创建一个存储过程，返回我们想要在视图中显示的 Active Directory 和员工表数据。
  3. 编写一个 SQL 查询，将员工数据库表中的一条员工记录与相应的 Active Directory 用户条目链接起来，并返回一个混合数据源用户信息记录。
  4. 通过员工数据库表中的每个用户，循环执行在 a. 中编写的 SQL 查询。
  5. 在 SQL Server 2000 中安装链接的 LocalServer，以便在数据库视图中使用存储过程（在步骤 3 中创建）。
  6. 在视图中执行混合数据源存储过程（在步骤 3 中创建）。

安装链接的 ADSI 服务器

在您能够查看 Active Directory 数据之前，您需要在您的机器上安装一个链接的 ADSI 服务器。为此，只需执行以下查询

sp_addlinkedserver 'ADSI', 'Active Directory 
Service Interfaces', 'ADSDSOObject', 'adsdatasource'

如果您使用 SQL Server 2005 客户端，您可以在“对象资源管理器”中看到链接的 SQL Server。导航到“服务器对象”文件夹并打开“链接服务器”。从那里，您也可以删除一个链接服务器。

查询 Active Directory

现在，无论您在哪里安装了 ADSI 服务器，您都将能够在 SQL Server 中查询 Active Directory。执行此操作的语法是

SELECT * FROM OPENQUERY (ADSI, 'SELECT givenName, sn, mail FROM
''LDAP://DC=<domain>,DC=com'' WHERE objectclass= ''person'' ANDobjectClass = ''user''')

现在，我们必须编写查询，该查询为一个用户名返回 Active Directory 的混合数据记录，该记录与员工数据库表中的用户记录连接。

在深入细节之前，我们必须记住

  • 我们编写的查询需要能够处理动态用户名值，因为它稍后将用于遍历存储在员工数据库表中的每个单个用户记录。
  • 访问 Active Directory 的 OpenQuery 命令最多只能返回 1000 条用户记录。这是 Active Directory 服务器中的一个设置，我们必须处理这样一个事实：在大多数情况下，此设置无法更改。

在 OpenQuery 中使用动态用户名

好的，现在我们想编写一个查询，它从 Active Directory 返回单个用户条目，但也允许我们传递动态用户名。不幸的是，如果你像这样编写查询，它将直接失败

SELECT * FROM OPENQUERY (ADSI, 
'SELECT givenName, sn, mail FROM 
''LDAP://DC=<domain>,DC=com'' 
WHERE objectclass=''person'' AND 
objectClass = ''user'' and SAMAccountName=’+@username+’')

这里有个坏消息：您不能在 OpenQuery 中使用动态值。为了解决这个问题，您必须创建一个动态 SQL 查询。这意味着您必须创建一个查询字符串并将其分配给一个变量。然后，该变量必须被执行。

set @adsiquery='(
SELECT * FROM openquery 
     (ADSI, ''SELECT displayName, SAMAccountName 
              FROM '''''+@adspath+''''' 
              WHERE objectclass=''''person''''
                 AND objectClass=''''user''''  
                 AND SAMAccountName='''''+@userName+''''' 
       '') 
)' 
EXEC (@adsiquery)

是的，我同意，这使得它更丑陋，甚至更难保持概览，尤其是在我们稍后开始将 Active Directory 与员工数据库表连接时。但是，这是保持用户名值动态的唯一方法，所以我们必须坚持下去。

将 Active Directory 与员工数据库表连接

之前，我们学习了如何创建 Transact-SQL 脚本来显示用户的 Active Directory 条目。现在，我们想编写一个事务-SQL 脚本，它将 Active Directory 数据源与员工数据库表连接起来，并在一条记录中返回 Active Directory 和员工数据库表中的员工信息。请记住，即使存储了更多用户，Active Directory 最多也只显示 1000 条记录。但是，通过事务-SQL 脚本，这已经得到了处理，因为我们传递了过滤值：用户名。因此，将 Active Directory 数据源与员工数据库表连接起来看起来像这样

set @adsiquery='(
SELECT
     employees.username      AS username
    ,employees.picture_data  AS picture_data 
    ,employees.recharge_rate AS recharge_rate 
    ,displayName             AS full_name
    ,givenname               AS first_name
    ,sn                      AS last_name
    ,l                       AS city
FROM openquery
   (ADSI, ''SELECT displayName, SAMAccountName
       FROM '''''+@adspath+''''' 
       WHERE  
          objectclass=''''person''''
          AND objectClass=''''user'''' 
          AND SAMAccountName='''''+@userName+''''' 
    '')  
    ,employees
    WHERE  
    employees.username = SAMAccountName 
)'
EXEC (@adsiquery)

太棒了，目标已经完成了一半。我们有一个 Transaction-SQL 脚本，它为一名用户返回一条用户信息记录，其中包含来自 Active Directory 和员工数据库表的数据。查看上面示例中的“SELECT”语句部分，您可以看到 username、picture_data 和 recharge_rate 来自数据库，而 fullname、first_name、last_name 和 city 则由 Active Directory 编程接口提供。让我们把所有东西都放在一个功能性的存储过程中

ALTER PROCEDURE [dbo].[ITS_selectLdapUser]
      @userName AS nvarchar(50)  
AS

BEGIN 

declare @adsiquery as nvarchar(4000)
declare @adspath as nvarchar(1000) 

select @adspath = 'LDAP://ldapdomain'

set
@adsiquery='(
SELECT  
employees.username AS username
     ,employees.picture_data       AS picture_data
     ,employees.recharge_rate      AS recharge_rate
      ,displayName                  AS full_name
      ,givenname                    AS first_name
     ,sn                       AS last_name
     ,l                        AS city
FROM openquery 
(ADSI, ''SELECT displayName, SAMAccountName
            FROM '''''+@adspath+''''' 
            WHERE 
objectclass=''''person''''

AND objectClass=''''user'''' 
AND SAMAccountName='''''+@userName+''''' 
'') 

,employees
WHERE 

employees.username = SAMAccountName
)'
EXEC (@adsiquery)
END

瞧。要执行存储过程

EXEC @return_value = [dbo].[ITS_selectLdapUser]
@userName = N'kupfernagelh'

获取所有员工条目

在上一节中，我编写了一个事务 SQL 脚本，该脚本只允许显示一个员工的员工信息数据。现在，我们希望显示员工数据库表中存储的所有员工的数据，以便稍后将所有过滤逻辑应用于从该事务 SQL 脚本生成的视图。最简单的方法是遍历员工数据库表中所有用户名的存储过程。只要您不需要在视图中显示员工经理的信息，这种方法就能奏效。不幸的是，我需要，所以我不得不处理临时表。主要问题是，用户 Active Directory 中存储的唯一经理信息是其 LDAP 域。因此，您必须使用经理的 LDAP 域来单独查询经理的信息，然后将其添加到员工的信息中。尽管，我与员工信息一起存储的唯一经理值是经理的用户名，因为它是以后在应用程序需要时使用我在此处创建的视图查询数据库的最重要值。

那么，让我们一步一步地看看我需要做些什么才能将所有内容整合起来

  • 为通过上述 Transact-SQL 脚本接收到的员工信息创建一个临时员工信息表。
  • 循环遍历员工数据库表中所有员工的脚本，并用员工信息以及经理 LDAP 域填充临时表。
  • 从刚刚添加到临时员工表中的当前用户，根据经理的域在 Active Directory 中查找经理的用户名。
  • 将经理的用户名添加到临时员工表。
  • 遍历员工表后，我们拥有了包含 Active Directory 和员工数据库表中所有员工信息的完整临时员工表。将此表刷新到视图中，我们就完成了。

让我们将最终脚本分成几部分，以便更容易理解。首先，我们要声明一些需要在脚本不同部分传递的变量

ALTER PROCEDURE [dbo].[ITS_selectLdapUser]

AS
BEGIN
      DECLARE @userName             AS NVARCHAR(50)
     DECLRE @managerDomain        AS NVARCHAR(1000)
      DECLARE @managerUserName      AS NVARCHAR(1000)
      DECLARE @adsiquery            AS nvarchar(4000)
     DECLARE @adspath             AS nvarchar(1000)

select @adspath = 'LDAP://myLdap'

接下来，我们创建一个临时员工表，我们稍后也希望将其刷新到最终视图中。在创建此临时表之前，我们要确保如果此临时表是之前数据库视图调用的残留，则将其清除。

  1. 为用户信息创建临时表。
    2. IF OBJECT_ID('tmp_employee_members')
IS NOT NULL  
      DROP TABLE tmp_employee_members 

CREATE TABLE tmp_employee_members( 
      username                nvarchar(50) 
      ,picture_data           image  
      ,recharge_rate          int  
      ,full_name              nvarchar(100) 
      ,first_name             nvarchar(50)  
      ,last_name              nvarchar(50)  
      ,manager_domain         nvarchar(100)  
      ,city                   nvarchar(50)  
      ,manager_username       nvarchar(50) 
)

    现在，是时候打开一个游标，遍历员工数据库表中的所有用户了。对于每个员工，我们然后访问 Active Directory 和员工数据库表，收集员工信息并将其插入到我们的临时员工数据库中。重要的是要知道我们的临时员工数据库还保存着经理的 LDAP 域（如果存在于 Active Directory 中）。经理的 LDAP 域稍后将用于查找经理信息，因为它需要成为视图的一部分。

  2. 获取所有员工。
    3. DECLARE rs CURSOR FOR 
      SELECT username FROM employee 

OPEN rs
  3. 获取第一个值。
    4. FETCH NEXT FROM rs INTO @userName
  4. 开始所有员工部门成员的循环。
    5. WHILE @@FETCH_STATUS = 0 
      BEGIN  
      -- 4.1 Read data from LDAP and local DB 
      set @adsiquery='( 
            SELECT  
                  employee.username             AS username 
                  ,employee.picture_data        AS picture_data 
                  ,employee.recharge_rate       AS recharge_rate 
                  ,displayName                  AS full_name 
                  ,givenname                    AS first_name 
                  ,sn                           AS last_name 
                  ,l                            AS city 
                  ,manager                      AS manager_domain 

            FROM openquery 
(ADSI, ''SELECT displayName, SAMAccountName,  
givenname, sn, manager, l 
                  FROM '''''+@adspath+'''''   
                  WHERE objectclass=''''person''''  
AND objectClass= ''''user''''

AND SAMAccountName='''''+@userName+''''' '') 
                  ,employee  
                  WHERE 
                  employee.username = SAMAccountName 
            )' 

      -- 4.2 Insert data into gobal temp database 
      INSERT INTO tmp_employee_members ( 
                  username 
                  ,picture_data 
                  ,recharge_rate 
                  ,full_name 
                  ,first_name 
                  ,last_name 
                  ,manager_domain 
                  ,city 
      )  
      EXEC (@adsiquery)

    此时，我们正在用 Active Directory 中相应的员工条目以及员工数据库表中的员工信息填充我们的临时员工数据库表。在此过程中（或多或少即时地），我们现在还可以查找额外的经理信息并将其添加到临时员工表中。为此，我们必须将经理信息存储在另一个单独的临时经理信息表中。我们之前遇到过同样的问题，需要一个动态的 ADSI 查询来传递 Active Directory 查找的用户名；这使我们能够对经理执行相同的操作。正如您在此案例中看到的，我们将动态经理域传递给查询，这足以在 Active Directory 中识别正确的经理用户条目。然后，再次，为了访问经理的 Active Directory 信息，我们必须将值（在这种情况下，它只是经理的用户名）存储在一个我们希望保持临时的表中。然后，我们从临时经理表中读取该值，并将其与员工信息一起存储在临时员工表中。

    -- 4.3 Get the manager’s username if exist from employee
SET @managerDomain = (select manager_domain FROM tmp_employee_members  
WHERE username = @userName)

IF (@managerDomain <> '') 
BEGIN 
-- 4.3.1 Compose user ldap domain to right format           
SET @managerDomain = @adspath+'/'+@managerDomain 
 

-- 4.3.2 To make the manager domain dynamic, do LDAP call this way 
SET @adsiquery='(SELECT SAMAccountName FROM openquery 
                  (ADSI, ''SELECT SAMAccountName FROM  
'''''+@managerDomain+''''' '')'     

 

-- 4.3.3 Store manager information in other temporary table 
IF OBJECT_ID('tmp_manager') IS NOT NULL DROP TABLE tmp_manager          
CREATE TABLE tmp_manager (manager_username nvarchar(50)) 

INSERT INTO tmp_manager 
EXEC (@adsiquery) 

-- 4.3.4 Get the manager user name 
SET @managerUserName = (SELECT manager_username FROM tmp_manager) 

-- 4.3.5 Add username to the table 'tmp_employee_members' row 
UPDATE
tmp_employee_members SET manager_username =  
@managerUserName WHERE username = @username 

END

    现在，脚本差不多完成了。我们需要做的最后一件事是关闭仍在遍历员工部门数据库表的游标，然后刷新临时员工部门数据库表。

    FROM rs INTO @userName
END

CLOSE rs
DEALLOCATE rs
  5. 从临时员工部门表中读取所有数据。
    6. SELECT * FROM tmp_staff_members
END

这是所有合并的 Transact-SQL

ALTER PROCEDURE [dbo].[ITS_selectLdapUsers]
 
AS 
BEGIN 
      DECLARE @userName             AS NVARCHAR(50) 
      DECLARE @managerDomain        AS NVARCHAR(1000) 
      DECLARE @managerUserName      AS NVARCHAR(1000) 
      DECLARE @adsiquery            AS nvarchar(4000) 
      DECLARE @adspath              AS nvarchar(1000) 

select @adspath = 'LDAP://myLdap' 

SET NOCOUNT ON;

-- 1. Create the temporary table for the user info 
IF OBJECT_ID('tmp_employee_members') IS NOT NULL 
      DROP TABLE tmp_employee_members 
 
CREATE TABLE tmp_employee_members( 
      username                nvarchar(50) 
      ,picture_data           image 
      ,recharge_rate          int 
      ,full_name              nvarchar(100) 
      ,first_name             nvarchar(50)  
      ,last_name              nvarchar(50)  
      ,manager_domain         nvarchar(100)  
      ,city                   nvarchar(50)  
      ,manager_username       nvarchar(50)  
)     

-- 2. Get all the staff members 
DECLARE rs CURSOR FOR 
      SELECT username FROM employee 

OPEN rs

-- 3. Get first value 
FETCH NEXT FROM rs INTO @userName 


 
-- 4. Start the loop for all employee department members 
WHILE @@FETCH_STATUS = 0 
BEGIN 
      -- 4.1 Read data from LDAP and local DB 
      set @adsiquery='( 
            SELECT  
                  employee.username             AS username 
                  ,employee.picture_data        AS picture_data 
                  ,employee.recharge_rate       AS recharge_rate 
                  ,displayName                  AS full_name 
                  ,givenname                    AS first_name 
                  ,sn                           AS last_name 
                  ,l                            AS city 
                  ,manager                      AS manager_domain  

            FROM openquery 
(ADSI, ''SELECT displayName, SAMAccountName,  
givenname, sn, manager, l 
                  FROM '''''+@adspath+'''''  
                  WHERE objectclass=''''person''''  
AND objectClass= ''''user'''' 

AND SAMAccountName='''''+@userName+''''' '') 
                  ,employee 

                  WHERE 
                  employee.username = SAMAccountName 
            )' 

      -- 4.2 Insert data into gobal temp database 
      INSERT INTO tmp_employee_members ( 
                  username 
                  ,picture_data 
                  ,recharge_rate 
                  ,full_name 
                  ,first_name 
                  ,last_name 
                  ,manager_domain 
                  ,city 
      )  
      EXEC (@adsiquery)  

 
-- 4.3 Get the manager’s username if exist from employee 
SET
@managerDomain = (select manager_domain FROM tmp_employee_members  
WHERE username = @userName)

IF (@managerDomain <> '') 
BEGIN 
-- 4.3.1 Compose user ldap domain to right format           
      SET @managerDomain = @adspath+'/'+@managerDomain 


-- 4.3.2 To make the manager domain dynamic, do LDAP call this way 
SET @adsiquery='(SELECT SAMAccountName FROM openquery

                  (ADSI, ''SELECT SAMAccountName FROM  
'''''+@managerDomain+''''' '')'     

-- 4.3.3 Store manager information in other temporary table 
IF OBJECT_ID('tmp_manager') IS NOT NULL DROP TABLE tmp_manager          

CREATE TABLE tmp_manager (manager_username nvarchar(50))  
INSERT INTO tmp_manager 
      EXEC (@adsiquery)  


-- 4.3.4 Get the manager user name 
SET @managerUserName = (SELECT manager_username FROM tmp_manager)
            
-- 4.3.5 Add username to the table 'tmp_employee_members' row 
UPDATE  tmp_employee_members 
SET manager_username = @managerUserName 
WHERE username = @username 
END 

FETCH NEXT FROM rs INTO @userName 
END

CLOSE rs 
DEALLOCATE rs 

-- 5. Read all the data out of the temporary table 
SELECT * FROM tmp_staff_members

 
END

恭喜您，现在您拥有一个存储过程，它从两个数据源（Active Directory 和员工数据库表）返回您部门所有员工的信息。

获取视图

拥有这个显示您部门员工信息的存储过程已经是一个非常强大的工具。对于访问此数据的第三方工具，数据库视图会很有用。视图还可以让您使用简单的 SQL “Select ... FROM.. WHERE” 语句过滤存储过程数据。正如我发现的，您不能直接从 SQL Server 数据库执行存储过程。但是有一种解决方法，就是安装一个链接的“LocalServer”，就像我们安装链接的“ADSI”服务器以在 SQL Server 2000 中访问 Active Directory 一样。要安装 LocalServer，请使用以下 Transact-SQL

sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                  @provider = 'SQLOLEDB', @datasrc = @@servername

安装服务器后，在 SQL Server 2000 中创建一个新视图，并像在存储过程中读取 Active Directory 员工条目一样使用 OPENQUERY 命令。

SELECT  
username, picture_data, recharge_rate, full_name, first_name, 
last_name, manager_domain, city, manager_username 

FROM  
OPENQUERY(LOCALSERVER, 'EXEC ItStaffStage.dbo.ITS_selectLdapUsers')

这就是您所需要的一切。您现在拥有了一个视图，可以像数据库表一样方便地访问员工信息。

限制

不幸的是，有些 Active Directory 值无法通过 ADSI 链接服务器读取。这些值被称为“数组值”。Active Directory 中的数组值是指允许插入无限多个值的字段。例如，如果您在 Active Directory 的“常规”选项卡下，在“电话号码/其他…”中存储多个电话号码，您将无法通过 ADSI 将这些号码读入 SQL Server。还有一个坏消息：我发现“地址”选项卡下的“邮政编码”在 Active Directory 中显然是作为“数组值”存储的。为什么会这样，我不知道，因为字段中只能存储一个值。对于这个视图来说，这也意味着“邮政编码”无法在 SQL Server 数据库视图中显示。可能还有一些我尚未发现的其他字段，可能也存储为“数组值”。因此，如果您尝试执行以下 Transact-SQL，它将失败，因为您尝试从 ADSI 接口读取“邮政编码”（postOfficeBox）数组值

SELECT *
FROM OPENQUERY (ADSI,
'SELECT givenName, postOfficeBox FROM ''LDAP://DC=<domain>,DC=com'' 
   WHERE objectclass= ''person'' AND objectClass = ''user''')

此外，随着员工数据库中条目数量的增加，您对 Active Directory 的访问次数也会增加。因此，请注意，如果员工数量众多，您可能会在 Active Directory 上造成相当大的流量。

总结

我希望本文对您有所帮助，并能为您节省大量研究时间。我花了大约两周的时间才把所有这些小细节整合起来，使其能够满足我的需求。

© . All rights reserved.