Publishing SQL Server in Active Directory

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.

Related: Querying Active Directory Records

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?") 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, shows.

Now that you've added a SQL Server and a database to AD, you can browse these classes in AD by using ADSI Edit, which is included with Win2K Server. To open ADSI Edit, run the Microsoft Management Console (MMC) from the Start menu, select Console, Add/Remove Snap-in, and add the ADSI Edit snap-in.

After registering ADSI Edit, you need to select the domain to connect to by right-clicking the ADSI icon and entering a domain server name. You should be able to locate your server under the COMPUTER container. You should see a new mS-SQL-SQLServer object under the Server's AD Class instance. If you're running a default instance of SQL Server, the object will be called MSSQLSERVER.

If you expand the mS-SQL-SQLServer object, you should see a child object called mS-SQL-SQLDatabase. Each of these objects or classes has attributes. One attribute of mS-SQL-SQLDatabase is mS-SQL-Name, which lists the database name—in this case, Northwind, as Figure 3, shows.

Is It Secure?

You might wonder whether publishing your SQL Server and databases in AD is secure. Actually, doing so is more secure than the existing method of locating SQL Servers on the network—Named Pipe broadcasts. By default, a SQL Server announces itself as a service over Named Pipes to all clients. To illustrate this point, try running osql ­L on your network to discover all the computers running SQL Server. Named Pipe broadcasts let clients find SQL Server dynamically; however, you can't assign permissions to these broadcasts—they go out to everyone connected to your network.

In AD, you can assign permissions to restrict who can see which AD objects. For example, if I had a SQL Server for payroll data, I'd assign read permissions only to the AD payroll group. Once I publish my SQL Server and databases in AD, I can disable Named Pipe broadcasts and assign permissions to the AD objects. See SQL Server 2000 Books Online—BOL—(Updated-SP3) under "Revealing SQL Server on a Network" for information about how to disable Named Pipe broadcasts. Note that hiding SQL Server switches the port that SQL Server uses to 2433 regardless of whether you've already set SQL Server to use an alternate port.

Service Publication

A service publication helps client applications locate the appropriate database server. As Figure 4 shows, first a SQL Server publishes to AD its name and location, including information such as server name and the port number SQL Server is running on. Next, a client application looks up the location of a SQL Server by database name. Finally, the application uses the retrieved location information to connect to the SQL Server and to begin using it. When you publish SQL Server and its databases in AD, clients can dynamically locate database servers by database name. If you need to move a database to a different server, you need only to update AD.

The concept of a service publication for database management systems (DBMSs) has existed in Oracle since version 8i, and you can list Oracle databases in AD. In Oracle, if you've listed the Oracle databases in AD, you can simply specify the database's system identifier (SID) as the Host String, and the application will search AD to locate the server's network address and port number to make a connection. For example, Oracle's SQL*Plus can log on to an Oracle server by using the database's SID. Although SQL Server's Query Analyzer doesn't include this functionality, you can build client applications that search AD based on a database name and locate the appropriate SQL Server to connect to in much the same way as you can with Oracle.

Using SQL Server AD Data

Now that you've verified that your SQL Server and the Northwind database are registered in AD, let's look at some techniques for programmatically retrieving and manipulating SQL Server AD data. Although you can use ADSI exclusively, I find using a combination of ADO and ADSI's SQL dialect more natural for searching in AD. The VBScript code in Listing 1 uses the ADO provider for AD to retrieve the instance names registered in AD for all computers running SQL Server.

You can also query AD and retrieve a list of all registered databases by selecting the attribute mS-SQL-Name, as Listing 2 shows, again using the ADO provider for AD. For a complete list of AD attributes, see the Microsoft Developer Network (MSDN) documentation of the Win2K AD schema. Specifically, look at the mS-SQL-SQLServer and mS-SQL-SQLDatabase classes.

Using the ADSI SQL dialect to query AD has a few limitations. For example, you can't use the SELECT * syntax; you must specify the columns in your SELECT list. Also, you can't use ADSI SQL to update AD; you must use ADSI instead. The latter restriction also applies to ADSI linked servers. For more information, see SQL Server 2000 BOL (Updated-SP3) under "OLE DB Provider for Microsoft Directory Services." Finally, because AD is hierarchical, using ADSI functions to return information about parent containers is easier than using strictly the ADSI SQL dialect.

The VBScript code in Listing 3 uses a combination of ADO, ADSI SQL, and ADSI functions to retrieve the name of the server on which the database is located. First, the code uses an ADSI SQL query to obtain the ADsPath for the mS-SQL-SQLDatabase container, which holds the Northwind database. After obtaining the ADsPath, the code sets ObjADSI to that path. The code then uses the ADSI Parent property to get the mS-SQL-SQLDatabase parent container MSSQLSERVER. After obtaining the ADsPath for mS-SQL-SQLServer, the code again uses the ADSI Parent property to obtain the server name or Common-Name, which is stored as the cn attribute.

Finding an Environment

Suppose you want to register three environments (production, development, and quality assurance—QA) of a particular database. For example, you might have a production version, a development version, and a QA version of the Northwind database with the same database name across all environments. How do you determine whether the database you're looking at is the production or development version? You could store this information in one of the mS-SQL-SQLDatabase attributes. Note that not all AD attributes are user-updateable; some attributes can be updated only by the Directory System Agent (the system process that provides access to AD). To determine whether a particular attribute is user-updateable, see the documentation about the AD class attribute.

The built-in attribute Flags is updateable, so you can use this attribute to store environment indicator information. For this example, I'll use an indicator code to represent different environments, where 1 is production, 2 is development, and 3 is QA. The VBScript code in Listing 4, illustrates how you can set the mS-SQL-SQLDatabase attribute Flags by using the ADSI properties Put and SetInfo. The code retrieves the new values from AD. You can then register multiple instances of the same database for each environment; you can differentiate among them by modifying the Flags attribute. For example, the flag attribute for my development environment would be 2, and the flag for my QA environment would be 3.

Locate and Connect to SQL Server

You can modify the code from Listing 3 to look for the correct database environment for the Northwind database and dynamically make a connection to a SQL Server based solely on a database name in the database environment. The code in Listing 5 uses an ADSI SQL query to obtain the ADsPath for the Northwind database that has the Flags attribute set to 1 (denoting the production environment). Then, the code uses the ADSI Parent property to get the mS-SQL-SQLDatabase parent container MSSQLSERVER. After obtaining the ADsPath for the mS-SQL-SQLServer object, the code retrieves the port number SQL Server is running on, then uses the ADSI Parent property to obtain the server name or dNSHostname. Having obtained the SQL Server name and port number, the code makes a trusted connection to the SQL Server. The download that accompanies this article includes a Visual Basic (VB) 6.0 COM DLL and VBScript code, which perform the four tasks I've demonstrated: get SQL Servers, get databases, get data source, and set flag attribute. Using a DLL overcomes some of the slow performance you'll notice when you use VBScript exclusively.

Publishing a service is one of AD's most powerful features. When you publish a SQL Server as a service in AD, you can design client applications to dynamically locate databases through Service Publication and lookup, thereby eliminating manual client-configuration changes. In turn, by simply updating the database location in AD, DBAs can more easily move databases to another server without any effect on applications that use AD to dynamically locate a database.

By publishing a SQL Server in AD and developing applications that use Service Publication, you can eliminate the need to store static configuration data on each client. And you have an increased flexibility to reconfigure database locations and reduce downtime when migrating databases between server environments.