1 Introduction

Active Directory is the LDAP based directory, first introduced in Windows 2000. It provides a hierarchical, container based, organization of objects representing various domain entities, such as users, groups, printers, or computers (as well as their inherent properties). Its internal replication mechanism and multimaster capabilities make it scalable and robust. It also has a number of features that every directory should provide, such as extensive search capabilities and extensibility.

There are two ways in which SQL-Server & Active Directory can interact:

• Register SQL Server as part of Active Directory
• Use SQL Server features to access Active Directory Information

This document explains the interaction where SQL Server features will be used to access Active Directory Information

1.1 Prerequisites

Following are the prerequisites for accessing active directory from SQL Server

• Windows 2000 or 2003 server
• Active Directory
• SQL Server 2000/2005

2 Accessing Active Directory from SQLServer 2000

Active Directory can be accessed from SQLSERVER by either using Linked Server or with OpenRowSet.

2.1 Using Linked Server

Microsoft's object linking and embedding for database (OLE DB) providers can create connections to non-SQL Server data sources, as well as to other SQL servers. OLE DB can also connect to some non-relational data sources. The OLE DB providers make the non-relational data appear relational. As a result SQL Server can also query the contents of a domain's Active Directory, which is structured like a tree, not a table. Following are the steps to access an Active Directory:

• Create a linked server definition
• Look up the LDAP names of the directory elements you want to query
• Write a SELECT statement to get them

Security Issues

When a linked server is created, it is automatically set up to use self-naming; that is, the users' own Windows login credentials are passed to Active Directory to see if they are authorized to use it. Each object in Active Directory carries with it a security descriptor that indicates which actions are allowed for each user. If the user trying to select from the directory doesn't have the right permissions, access is denied.

2.1.1 Creating Linked Server

There are two ways to create Linked Server to Active Directory in SQL Server.

2.1.1.1 Using System Stored Procedure

System stored procedure sp_addlinkedserver can be used to create a linked server. The syntax to use is:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

The first parameter, ADSI, is the name you'll use in querying the linked server. Active Directory Services 2.5 is the product name, ADSDSOObject is the provider, and adsdatasource is the built-in data source name

2.1.1.2 Using Enterprise Manager

Following are steps to use Enterprise Manager to set up a linked server to Active Directory:

• Expand the Security container
• Right-click on Linked Servers
• Select New Linked Server

2.1.2 Finding LDAP Names

Lightweight Directory Access Protocol (LDAP) is a query language for directory services such as Active Directory. Each data element that you see in Active Directory has two names: the display name that appears on property pages, such as Active Directory Users and Computers, and an internal LDAP name. You need to use the latter name to query Active Directory. Following are the steps to find the same:

• The easiest way to locate internal LDAP names is to use a Microsoft Management Console snap-in called Active Directory Schema. It's not installed by default on Windows 2000, although its DLL is present. To register this DLL (and thus make it visible in the Add Snap-in dialog box), open a console window on the database server to which you have local administrator rights and type:

Regsvr32 schmmgmt.dll

The message: "DllRegisterServer in schmmgmt.dll succeeded." Will be displayed.

• Open a new, empty MMC console by typing the command mmc in the Run command's text box.
• From the Console menu, select Add Snap-in and the Add button.
• From the list, click on Active Directory Schema and click OK.
• A hierarchical (tree) display in the navigator pane will be visible. By opening the various levels, it will be possible to browse the structure, or schema, of Active Directory
• If you expand the Classes container and click on the Users class, the display panel on the right will fill up with all the elements that make up a User definition. The names listed are the LDAP names that you need to use in querying Active Directory from SQL. There's also a description for each element. For example, if you want to display a user's first and last names, you'll find them in the list under the LDAP names givenName and sn (short for surname), respectively.

Tip : If you can't use the Active Directory Schema snap-in, all is not lost. A number of these elements are documented in the article "User Object User Interface Mapping" on the MSDN Web site

2.1.3 Querying Active Directory

The usual four-part naming convention used with linked servers to databases (for example, OTHERSERV.MyDatabase.dbo.Employees) can not be used. Instead, write a pass-through query using the OPENQUERY function. In addition, only SELECT statements can be sent; INSERT, UPDATE, and DELETE statements are not allowed through ADSI.

For example, to get the first and last names of users, write the following:

SELECT * 
FROM OPENQUERY (ADSI, 
'SELECT givenName, sn FROM LDAP:// DC=yourcompany,DC=com'

Notice that this query just uses SELECT * to return all columns from the OPENQUERY function, using their original names. You can also alias the returning columns to provide more readable names. The LDAP query itself can be in the form above (called a serverless connection), or it can name the exact server to connect to:

LDAP://LAXPDC01/ DC=yourcompany,DC=com

Alternatively, it can use the domain to have any domain controller respond:

LDAP://MYCORP/ DC=yourcompany,DC=com

The part after the last slash is called the relative distinguished name or RDN of the Active Directory object you want to search. It reads from right to left, so additional qualifications are added on the left. For instance, if you want to list users in the Finance organizational unit (OU), you'd write:

LDAP://MYCORP/ OU=Finance,DC=yourcompany,DC=com

It is also possible to access the Active Directory from SQL Server 2000 directly, without creating Link Server:

SELECT *
FROM OPENROWSET('AdsDsoObject',
                'User ID= UserID; Password= Pwd; ADSI Flag=0x11;Page Size=10000',
                'SELECT givenName,sn 
                 FROM ''LDAP://addevdc01/OU=Users,OU=PerotSystems,DC=devtenethealth,DC=net''')
where sn like 'user1'

2.1.4 A Sample Application

Let's say that you use third-party human resources software that contains a table of employee information. Because it's a purchased package, you aren't able to change the layout of the Employee table, and it has no column for an employee Web page. However, many of your employees have started their own Weblogs (blogs), and you want to be able to create a report that contains some information from the HR package, as well as Web addresses for those employees who have blogs.

The user definition in Active Directory contains a field for this. In Active Directory Users and Computers, it's on the General tab as the display name Web page. Looking in the Active Directory Schema snap-in, you find that the LDAP name of this attribute is wWWHomePage. After updating the User records in Active Directory, you can create a SELECT statement to query them, storing it as a view.

CREATE VIEW bloggers_view
AS
SELECT givenName AS FirstName, sn AS LastName, wWWHomePage AS Weblog 
FROM OPENQUERY (ADSI, 
'SELECT givenName,sn,wWWHomePage FROM LDAP:// DC=yourcompany,DC=com')
WHERE wWWHomePage IS NOT NULL

Now you can join the Active Directory data with the Employee table:

SELECT e.FirstName, e.LastName, e.StartDate, bv.Weblog
FROM Employee AS e LEFT OUTER JOIN blogger_view AS bv
ON e.LastName = bv.Lastname AND e.FirstName = bv.LastName

2.2 Using OpenROWSET (without linked server)

We can also access the Active Directory without using the link server. For this we have to use the OpenRowSet.

OpenRowSet includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB.

OPENROWSET ( 'provider_name' , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } ) 

OPENROWSET permissions are determined by the permissions of the username being passed to the OLE DB provider.

SELECT * FROM OPENROWSET( 'AdsDsoObject' , 'User ID=user1;Password=pwd1;ADSI Flag=0x11;Page Size=10000', 'SELECT givenName,sn FROM ''LDAP://addevdc01/OU=Users,OU=account1,DC=dev,DC=net''') where sn like 'Mike'

3 Accessing Active Directory from SQLServer 2005

Active Directory can be accessed from SQLSERVER 2005 by using:

• Linked Server
• OpenRowSet
• Managed Stored Procedure

3.1 Using Managed Stored Procedure

SQL Server 2005's integration with the .NET CLR makes it possible for developers to author stored procedures, triggers, user defined functions, and create other database objects using a managed language such as VB.NET or C#.

Following is the sequence of tasks that needs to be performed to access active directory from SQL Server using .Net code:

• Create a .NET class and implement the functionality of the stored procedure within that class
• Compile that class to produce a .NET assembly
• Register that assembly in SQL Server using the Create Assembly statement
• Create stored procedure definitions. As part of this, you also associate the stored procedure with the actual methods in the assembly.

After completing these steps, the stored procedures are configured and you can execute them just like any other stored procedure

3.1.1 Implementing .Net Class-Based Stored Procedure

There are many ways to programmatically access Active Directory using .Net like COM objects, ADSI etc. The simplest one is to use the classes present in System.DirectoryServices namespace in the .NET Framework, it is built on the Active Directory Service Interfaces (ADSI) API.

The System.DirectoryServices namespaces provides two important classes DirectoryEntry and DirectorySearcher to work with the Active Directory. The DirectoryEntry class represents a resource in the Active Directory and the DirectorySearcher class is used to query the Active Directory.

Most of the methods in the above classes require the same parameters.

• friendlyDomainName: the non qualified domain name (contoso - NOT contoso.com)
• ldapDomain: the fully qualified domain such as contoso.com or dc=contoso,dc=com
• objectPath: the fully qualified path to the object: CN=user,OU=USERS,DC=contoso,DC=com (same as objectDn)
• objectDn: the distinguishedName of the object: CN=group,OU=GROUPS,DC=contoso,DC=com
• userDn: the distinguishedName of the user: CN=user,OU=USERS,DC=contoso,DC=com
• groupDn: the distinguishedName of the group: CN=group,OU=GROUPS,DC=contoso,DC=com

• Designed completely within common language runtime parameters. System.DirectoryServices leverages common language runtime features, such as garbage collection, custom indexer, and dictionaries (hashtables). It also offers other common language runtime features such as automatic memory management, efficient deployment, an object-oriented framework, evidence-based security and exception handling.
• Simple to use. Although ADSI scripting was effective for many tasks, C++ applications for ADSI are sometimes difficult to develop. System.DirectoryServices implements some basic ADSI tasks to enable more efficient and effective application development.

Using System.Data
Using System.Data.Sql
Using System.Data.SqlServer
Using System.DirectoryServices

Public Class ActiveDirectoryFunctions
{
  Public AuthenticateUser( String _userName, String _password, String _domain)
  { 
    Bool authentic = false;
    try
    {
      DirectoryEntry entry = new DirectoryEntry("LDAP://" + domain,userName, password);
      object nativeObject = entry.NativeObject;
      authentic = true;
      SqlPipe sp = SqlContext.GetPipe();
      Sp.send(authentic.toString());
    }
    catch (DirectoryServicesCOMException) { }
    return;
  }
} 

The code snippet to do (almost) all the operations on Active Directory can be found at: everythingInAD.asp

To execute .NET code in SQL server, you need to reference the System.Data.Sql and System.Data.SqlServer namespaces. The AddressType class contains one method, GetAddressTypeDetails, which uses the ID passed as a parameter to retrieve a row from the AddressType table in the AdventureWorks database that ships with SQL Server 2005. The code gets a reference to the SqlPipe object by invoking the GetPipe method of the SqlContext class. You then use that SqlPipe reference to return tabular results and messages to the client using the SqlPipe.Send method. By calling the various overloads of the Send method, you can transmit data through the pipe to the calling application. Some of the overloaded versions of the Send method are:

• Send(ISqlDataReader)— Allows us to send the tabular results in the form of a SqlDataReader object
• Send(ISqlDataRecord)—Allows us to send the results in the form of a SqlDataRecord object
• Send(ISqlError)—Makes it possible for us to send error information in the form of a SqlError object. \
• Send(msg As String)—Using this method, we can send messages to the calling application.

3.1.2 Register .Net assembly in SQLServer 2005

The deployment unit for managed code is called an assembly. An assembly is packaged as a DLL or executable (EXE) file. While an executable can run on its own, a DLL must be hosted in an existing application. SQL Server can load and host managed DLL assemblies. To load an assembly into SQL Server, you need to use the Create Assembly statement.

CREATE ASSEMBLY AddressType
FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\' +
'MSSQL\Binn\Test\CLRProcedures\CSharp\ ActiveDirectoryFunctions.dll'

When you execute the above statement in the SQL Server Workbench, it will load the assembly into SQL Server. The FROM clause specifies the pathname of the assembly to load. This path can either be a UNC path or a physical file path that is local to the machine. The above statement will register the assembly with the SQL Server (the assembly name should be unique within a database). Running the Create Assembly statement causes SQL Server to load a copy of the assembly. Subsequently, if you want to change the assembly code, you need to first drop the assembly, then change the code, recompile the assembly, and finally, register the assembly with SQL Server again. To drop an assembly from SQL Server, you use the Drop Assembly statement. For example, to drop the assembly created earlier, use the following command.

DROP ASSEMBLY ActiveDirectoryFunctions

3.1.3 Create Stored Procedure Definitions

After loading the assembly into SQL Server, you need to associate a stored procedure to a specific method of the class contained in the assembly. You create a stored procedure using the Create Procedure statement. SQL Server 2005 supports a new clause named External Name that lets you reference a method in a registered assembly (an external method). Referencing an external method hooks the stored procedure to that method in the assembly.

CREATE PROCEDURE [dbo].[ AuthenticateUser]
@userId varchar(30),
@password varchar(30),
@domain varchar(30)
AS
EXTERNAL NAME 
[ActiveDirectoryFunctions]:[ ActiveDirectoryFunctions]::
[AuthenticateUser] 

The preceding code uses the AuthenticateUser method shown earlier in the ActiveDirectoryFunctions class. The External Name clause uses the following syntax.

[Assembly Name]:[Class Name]::[Method Name]

After creating the stored procedure, you can test it using the following statement:

exec AuthenticateUser 'User1', 'Password1',' addevdc01/OU=Users,OU=Account1,DC=devDomain,DC=net'