Building a Hybrid Active Directory and SQL Table DataSource View

Getting Started

A while ago, I was asked to provide a SQL Server 2000 employee information database view that could be used by several web applications to manage our department employees. Some of those applications are used to display employees information, others are for calculation of the recharge rate or mailbox storage space based on the employee’s title. Most of the information is stored in our local Active Directory, but not all. Some information, such as recharge rate or a picture of the employee, is stored in the local employee database table. The employee database table and the Active Directory are linked by the employee’s username, which is a unique value in both data sources. So, the SQL Server 2000 database view I had built would be fed from both database sources that somehow had to be linked together.

Requirements

For this project, I used the SQL Server 2005 client to access the SQL Server 2000 database.

Steps of Implementation

Because this is a SQL Server 2000 database, I was challenged by having to translate all my ASP.NET C# code to receive and link data from the Active Directory and a database table in pure SQL. To keep it simple in the beginning and to provide a reference to my following more detailed elucidations, I want to show you all the steps I had to go through to approach my goal:

1. Install a linked ADSI Server within SQL Server 2000 to be able to receive data from the Active Directory via a Stored Procedure.
2. Create a Stored Procedure that returns the data of the Active Directory and the employee table as we want to display it in the view.
3. Write a SQL query that links one employee record from the employee database table with the corresponding Active Directory user entry and return a hybrid DataSource user information record.
4. Loop the SQL query composed in a. through each user in the employee database table.
5. Install a linked LocalServer within SQL Server 2000 to be able to use the Stored Procedure (created in step 3) in a database view.
6. Execute the hybrid DataSource Stored Procedure (created in step 3) within the view.

Installing a linked ADSI Server

Before you can even see the Active Directory data, you need to install a linked ADSI Server on your machine. For this, just execute the following query:

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

If you use the SQL Sever 2005 client, you can see the linked SQL Server in the “Object Explorer”. Navigate to the “Server Objects” folder and open “Linked Servers”. From there, you can also delete a linked server.

Query the Active Directory

Now, wherever you have installed the ADSI Server, you will be able to query the Active Directory within SQL Server. The syntax for doing this is:

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

Now, we have to write the query which returns for one user name the hybrid data record of the Active Directory which is joined with a user record from the employee database table.

Before I go into the details, we have to keep in mind:

• The query we are writing needs to be able to process dynamic username values because it is later used to loop through each single user record stored in the employee database table.
• The OpenQuery command accessing the Active Directory can return a maximum of 1000 user records. This is a setting in the Active Directory server, and we have to deal with the fact that in most cases, this setting can’t be changed.

Using dynamic username in OpenQuery

All right, now we want write a query that returns from the Active Directory a single user entry but also allows us to pass a dynamic username along. Unfortunately, if you write the query like this, it will just fail:

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

Here is the bad news: you can’t use dynamic values in OpenQuery. To getaround this issue, you have to create a dynamic SQL query. That means you have to create a query string and assign it to a variable. The variable, then, has to be executed.

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

Yes, I agree, that makes it uglier, and even more difficult to keep the overview, especially later when we start joining the Active Directory with the employee database table. But, this is the only one way to keep the user name value dynamic so we have to stick with it.

Joining the Active Directory with the employee database table

Previously, we learned how to create write a Transact-SQL script to display the Active Directory entry of a user. Now, we want write a transaction-SQL which joins the Active Directory DataSource with the employee database table and returns the employee information from the Active Directory and the employee database table in one record. Remember, the Active Directory only displays 1000 records maximum even if there are more users stored. But, with the Transaction-SQL script, this is already taken care of, because we pass as a filter value: the username. So, joining the Active Directory DataSource with the employee database table looks like this:

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)

Great, half of the goal is actually approached. We have a Transaction-SQL script that returns for one user a user information record which contains data from the Active Directory and the employee database table. Looking at the “SELECT” statement section in the example above, you can see that username, picture_data, and recharge_rate come from the database while fullname, first_name, last_name, and city were delivered by the Active Directory programming interface. Let’s put everything in a functional Stored Procedure:

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

Voila. To execute the Stored Procedure:

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

Getting all the employees entries

In the last section, I wrote a Transaction SQL script that would allow displaying the employee information data for only one employee. Now, we want to show the data of all employees which are stored in the employee database table so we can apply all filter logic later to a view that is generated out of this Transaction-SQL script. The easiest way would be to loop the Stored Procedure I created above through all usernames in your employee database table. This would work as long as you don’t have to display the employee’s manager information in your view as well. Unfortunately, I had to, so I had to deal with temporary tables. The main problem is that the only manager’s information stored in the user’s Active Directory is his or her LDAP domain. So, you have to use the manager’s LDAP domain to query the manager’s information separately and then add them it the employee’s information. Although, the only manager value I store along the employee’s information is the manager's username as it is the most important value for querying the database later, if required by the application, using the view I’m creating here.

So, let’s see in steps what I have to do, to get everything together:

• Create a temporary employee information table for the employees information received through the Transact-SQL script described above.
• Loop the script through all the employees present in the employee database table and fill the temporary table with the employee information along with the manager LDAP domain.
• From the current user just added to the temporary employee table, look up the manager’s username in the Active Directory on behalf of the manager’s domain.
• Add the manager’s username to the temporary employee table.
• After looping through the employee table, we have the complete temporary employee table with all the employees’ information from the Active Directory and the employee database table. Flush this table into a view, we are done.

Let’s split the final script in several pieces to make it easier to understand. First, we want to declare some variables we need to pass through the different sections in our script:

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'

Next, we create a temporary employee table which we also want to flush out later into our final view. Before creating this temporary table, we want to make sure this temporary table is wiped out if it is a left over from a previous database view call.

1. Create the temporary table for the user info.
SQL

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) 
)

Now, it’s time to open a curser to loop through all the users in our employee database table. For each single employee, we then hit the Active Directory and employee database table to gather the employee information to insert this into our temporary employee database. It is important to know that our temporary employee database is also holding the manager’s LDAP domain (if present in the Active Directory). The manager’s LDAP domain is used later to look up the manager’s information as it is required to be a part of the view.

2. Get all the staff members.
SQL

DECLARE rs CURSOR FOR 
      SELECT username FROM employee 

OPEN rs

3. Get the first value.
SQL

FETCH NEXT FROM rs INTO @userName

4. Start the loop for all employee department members.
SQL

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)

At this point, we are filling our temporary employee database table with employee information from the corresponding Active Directory employee entries as well as the employee information from the employee database table. While doing this (more or less on the fly), we can now also look up the additional manager information and add it to the temporary employee table. To do this, we have to store the manager’s information in another separate temporary manager information table. We faced the same problem earlier where we needed a dynamic ADSI query to pass the username for the Active Directory lookup; this allows us to do the same for the manager. As you can see in this case, we pass the dynamic manager domain to the query which is good enough to identify the correct manager user entry in the Active Directory. Then again, to gain access to the manager’s Active Directory information, we have to store the values (in this case, it is only the manager’s username) in a table which we want to keep temporary. Then, we read that value back from the temporary manager table and store it along with the employee information in the temporary employee table.

SQL

-- 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

Now, we are almost through with the script. The last thing we need to do is close the curser which is still looping through the employee’s department database table, and then flush the temporary employee department database table.

SQL

FROM rs INTO @userName
END

CLOSE rs
DEALLOCATE rs

5. Read all the data out of the temporary employee department table.
SQL

SELECT * FROM tmp_staff_members
END

Here is all the Transcript-SQL combined:

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

Congratulations, you now have a Stored Procedure, which returns all the employees information from your depart out of two DataSource, the Active Directory and the employee database table.

Getting the view

Having this Stored Procedure to display employee information from your department is already a very powerful tool. For third party tools accessing this data, a database view would be useful. A view also would allow you to filter your Stored Procedure data with a simple SQL “Select ... FROM.. WHERE” statement. As I found out, you can not just execute a Stored Procedure from you SQL Server database. But there is a way around by installing a linked “LocalServer” the same way as we installed a linked “ADSI” server to access the Active Directory within SQL Server 2000. To install the LocalServer, use the following Transcript-SQL:

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

After you have installed the server, create a new view within SQL Server 2000 and use the OPENQUERY command the same way as you used it in your Stored Procedure to read the Active Directory employee entries.

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')

That’s all you need. You have now a view that can be used as handy as a database table to access employee information.

Limits

Unfortunately, there are some Active Directory values that can’t be read with the ADSI linked servers. Those values are called “Array Values”. Array values in the Active Directory are fields that allow to insert unlimited multiple values. For example, if you store multiple phone numbers in the Active Directory General tab under “Telephone numbers/Other…”, you won’t be able to read those via ADSI into your SQL Server. There is one more bad news: I found out that “Zip/Postal Code” under the Address tab is apparently stored as an “Array Value” in the Active Directory”. Why this is so, I don’t know, because there is only one value you can store within fields. For this view, it also means the “Zip/Postal Code” can’t be displayed in the SQL Server database view. There might be some other fields I haven’t found out yet, that might be stored as an “Array Value”. So, if you try to execute the following Transcript-SQL, it will fail because you try to read the “Zip/Postal Code” (postOfficeBox) array value from the ADSI interface:

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

Also, with increasing number of entries in your employee’s database, you also increase the number of hits on the Active Directory. So, be aware that with a large number of employees, you might cause quiet some traffic on the Active Directory.

Finishing up

I hope this article was helpful to you and could save you a lot of research time. It took me about two weeks to get all these little things together to make it functional for something I needed.