If you've noticed the Active Directory tab under SQL Server Properties in Enterprise Manager, you might have wondered how Active Directory (AD) relates to SQL Server and what the benefits are of adding SQL Server and its databases to AD. Network services such as file and printer servers use AD to publish and store network resource information. AD contains a list of user accounts and a directory of available network resources.
Because AD considers SQL Servers and SQL Server databases to be network resources, you can list them in AD. In SQL Server 2000, Microsoft introduced the concept of publishing (i.e., listing) both SQL Server and its databases in AD on a Windows Server 2003 or Windows 2000 domain.
Some Microsoft products such as Microsoft Exchange 2000 are so tightly integrated with AD that they require AD just to function. SQL Server doesn't require AD (at least not yet), and simply listing SQL Server in AD doesn't produce any immediate benefits. Listing SQL Server databases in AD is useful only if you develop applications that can take advantage of the AD published databases. One main benefit of listing any resource in AD is AD's Service Publication feature. Service Publication enables applications to list the name and locations of services they provide, so clients can locate the applications' services dynamically. Service Publication and lookup gives administrators the flexibility to reconfigure servers without having to update clients.
This article describes how to register a SQL Server instance and a database in AD. In addition, it provides some practical examples that use ADO, SQL, and Active Directory Service Interface (ADSI) to query and update the AD catalog. (For more information about ADSI, see the sidebar "What Is ADSI?" on page 16.) We'll walk through an example of how to build a service publication for a SQL Server database so that clients can dynamically locate and connect to a SQL Server based solely on a database name.
Listing a SQL Server Database in AD
You can list a SQL Server database in AD either through Query Analyzer by using the system stored procedures sp_ActiveDirectory_SCP and sp_ActiveDirectory_Obj or through Enterprise Manager. To add SQL Server from Query Analyzer, run
EXEC sp_ActiveDirectory_SCP
To add SQL Server from Enterprise Manager, open Server Properties, select the Active Directory tab, and click Add, as Figure 1 shows.
After registering the SQL Server instance in AD, you can add databases by using the sp_ActiveDirectory_Obj procedure. For example, to add the Northwind database to AD, you'd run
EXEC sp_ActiveDirectory_Obj @Action = N'create',
@ObjType = N'database',
@ObjName = 'Northwind'
To add the Northwind database from Enterprise Manager, open Northwind Properties, select the Options tab, and click the List this database in Active Directory check box at the bottom of the screen, as Figure 2, page 16, shows.