Microsoft

SolutionBase: Search Active Directory data from SQL Server 2000

SQL Server 2000's linked server feature enables you to search Active Directory as easily as regular SQL Server tables.

As a Microsoft Certified Trainer, I often show students how to connect from SQL Server 2000 to other databases such as Microsoft Access and Oracle. 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. This is similar to the way ODBC data sources are used in Microsoft Access.

Unlike the older ODBC standard, however (which could access relational data only), OLE DB can also connect to some non-relational data sources. The OLE DB providers make the non-relational data appear relational. This means that SQL Server can also query the contents of a domain's Active Directory, which is structured like a tree, not a table. In fact, the data retrieved from Active Directory can be made into a view or even joined with existing SQL Server data. As I'll explain, the process is relatively straightforward:

  • 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

Creating a linked server

You can use either the system stored procedure sp_addlinkedserver or Enterprise Manager to set up a linked server to Active Directory. As documented in SQL Server 2000 Books Online, the call to sp_addlinkedserver 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.

Alternatively, when using Enterprise Manager, simply expand the Security container, right-click on Linked Servers, and select New Linked Server. The General tab will have the items listed above; leave the others blank (the provider appears in a drop-down list as OLE DB Provider for Microsoft Directory Services). On the Security tab, be sure the third option, To Be Made Using The Login's Current Security Context, is selected.

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. How do you find it?

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

You should see the message: "DllRegisterServer in schmmgmt.dll succeeded."

Next, 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. You now have a hierarchical (tree) display in the navigator pane. By opening the various levels, you can 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.


Querying Active Directory

Now you're ready to actually write the query. You can't use the usual four-part naming convention used with linked servers to databases (for example, OTHERSERV.MyDatabase.dbo.Employees). Instead, you must 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.

OPENQUERY is a rowset function: It returns a set of rows from a linked server as its function value. We can use it with our new linked server definition to query Active Directory. 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

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

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.

Editor's Picks

Free Newsletters, In your Inbox