SQL Server 2000 was designed to operate as an integral part of the Windows 2000 domain environment. Unfortunately, the new features that best exemplify this integration are rarely taken advantage of. The purpose of this article is to present their overview.
Active Directory is the LDAP based directory, first introduced in Windows 2000 (its revamped version will be shortly available as part of the Windows 2003 platform, due to be released on April 24, 2003). 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.
Integration of SQL Server 2000 with Active Directory means essentially that SQL servers, their components and attributes (such as replication publications, cubes, and data mining models) can be registered in active directory. This not only facilitates organization-wide searches for features that clients, developers, or applications might be interested in, but also allows functional transparency, which makes features independent of their physical location (particularly useful when specific functionality needs to be transferred to another computer).
We will start with registering a SQL Server in Active Directory. This can be accomplished using either Enterprise manager or sp_ActiveDirectory_SCP stored procedure:
- To register SQL Server in the Active Directory, right click on the server node in the SQL Server Enterprise Manager and select the Properties item from the popup menu. Next, in the Properties dialog box, select Active Directory tab. This will present you with three buttons controlling adding, removing, and refreshing the information about the SQL Server in Active Directory. Only the first one will be initially available. After clicking on it, you should receive the confirmation indicating that Active Directory was updated successfully.
- You can accomplish the same goal with Query Analyzer by running sp_ActiveDirectory_SCP stored procedure. The stored procedure takes the single argument @Action, which can be set to “create”, “update”, or “delete”, equivalent to Add, Refresh, and Remove buttons of the Active Directory tab of the server properties in Enterprise Manager.
Once the server has been added, its representation can be found in Active Directory Users and Computers, which is the primary tool used for administering Active Directory domains. Note, however, that in order to be able to see the new SQL Server object, you will need to select the “Users, Groups, and Computers as containers” and “Advanced Features” options from the View menu of Active Directory Users and Computers. The new object will be displayed as a subfolder (labeled MSSQLSERVER) of the node representing the Windows 2000 server hosting the SQL 2000 server instance.
Unfortunately, the Active Directory Users and Computers interface does not display any of the properties of SQL server. In order to view them (e.g. SQL server sort order, TCP/IP port, etc.) you will need to use one of more advanced tools, such as ADSI Edit. ADSI Edit is not part of the standard Administrative Tools, but needs to be installed as part of Windows 2000 Support Tools from the Support folder on the Windows 2000 installation CD.
ADSI Edit presents the hierarchical view of the Active Directory partitions – schema (equivalent to SQL database schema), configuration (representing physical structure of Active Directory), and domain (where user, group, computer, and computer component objects – including MSSQLSERVER – reside). By following the path to the computer object where SQL Server is installed, you will be able to find a folder labeled “CN=MSSQLSERVER”. By right clicking on it and selecting Properties menu item, you will be able to browse through the list of SQL server properties. The MSSQLSERVER object will have a number of Active Directory and SQL server specific properties. The properties pertaining to SQL server are can be easily distinguished since they start with the “mS-SQL-” prefix.
Once SQL server has been published in Active Directory, you can also publish its databases. This is done by checking the “List this database in Active Directory” checkbox on the Options tab of the database Properties dialog box. Alternatively, you can use sp_ActiveDirectory_Obj stored procedure to register a database in Active Directory. Either of these methods will create a new object (named the same as the database) in the MSSQLSERVER folder in the Active Directory Users and Computers. Once the database is published in Active Directory, you can use the ADSI Edit tool to query database properties, (as before, easily distinguishable by “mS-SQL-” prefix).
Another type of SQL Server 2000 component that can be registered in Active Directory is a replication publication. This simplifies creating subscriptions, since search for publications does not require knowledge of the name of SQL Server instance where they were created. In addition, users can base their searches on a number of criteria.
To publish a publication in Active Directory, make sure that you select the “List the publication in the Active Directory checkbox on the “Select Publication Name and Description” page of the Create Publication Wizard (for existing publications, you can simply check the same checkbox on the General page of the publication Properties dialog box). The publication will appear in Active Directory Users and Computers as an item within the MSSQLSERVER folder labeled in the format [DatabaseName]:[PublicationName]. As before, you can check its properties using the ADSI Edit tool.
You can also publish a publication in Active Directory for both new and existing publications using stored procedures such as sp_addpublication, sp_addmergepublication, sp_changepublication, or sp_changemergepublication. For each of them, publishing in Active Directory requires adding @add_to_active_directory parameter with the value set to ‘TRUE’.
To subscribe to a publication published in Active Directory, from the Tools menu of the SQL 2000 Enterprise Manager first select “Replication” option and then choose “Pull Subscription to ‘ServerName’” (where ServerName is the server you have the focus on in the Enterprise Manager window where the subscription will be created). Clicking on “Pull New Subscription” button will launch the Pull Subscription Wizard. On the second page of the wizard, you will be given a choice between looking for a publication from registered servers or looking for it in Active Directory. If you select the second option, on the next page you will have an option to browse the Active Directory for all or some (based on criteria you specify) of the publications. Once you select the publication, the rest of the process is identical to crating a subscription in the traditional manner.
Finally, you can also publish analysis servers in Active Directory (obviously this requires that Analysis Services are installed). Select Start->Programs->Microsoft SQL Server->Analysis Services->Analysis Manager menu option. From the Properties dialog box of the server (displayed in the Analysis Servers container), choose the Active Directory tab, and click on the “Enable Active Directory Registration” checkbox. You will also have a chance to specify additional information about the server, such as server descriptive name (alternative to the server actual name that might help users to locate it), contact info (typically, a person responsible for server operations), set of keywords (e.g. role, location, or type of the server), and URL pointing to the server’s Web page. Registration of Analysis Services will be reflected in Active Directory Users and Computers by an extra subfolder called MSSQLServerOLAPService under the node representing the server hosting the Analysis Services.
As you can see, with SQL Server 2000, Microsoft took first steps in Active Directory integration. Even though the set of Active Directory-aware features built into the product is still limited (the biggest benefit at this point is simplification of subscription process – there is no interface within SQL Server 2000 to browse for published databases or analysis services), developers can easily take advantage of ADSI or LDAP functionality to retrieve information about all SQL 2000 published components.
