Executive Summary:
There are several ways to query Microsoft Active Directory (AD) from Microsoft SQL Server. One common method involves using the sp_add_linked_server stored procedure to create a linked server, then using T-SQL's OPENQUERY clause to directly query Active Directory. Another common method is to use an ADO.NET connection manager in a SQL Server Integration Services (SSIS) package so that you can create an ADO.NET connection to Active Directory, then query it. A third common method is to use a Script component in a SQL Server Integration Services package so that you can include a Visual Basic .NET script that uses the Directory Services .NET assembly to access and query Active Directory. This article explains how to perform each method and the results you can expect. Surprisingly, the results can differ, depending on the method you use and your environment. |
Querying Active Directory (AD) from SQL Server can provide useful data. Three common methods to query AD are
- Using a linked server
- Using a ADO.NET connection manager in a SQL Server Integration Services (SSIS) package
- Using a Script component in an SSIS package
However, these three methods don't always provide the same results. Let's walk through how to perform each of them and compare their results.
Linked Server Method
To query AD using a linked server, you first need to use the sp_add_linked_server stored procedure to create that server. You then need to use an OPENQUERY clause to query AD directly. You shouldn't import AD data into a table and query that table because the data might end up being outdated. For example, suppose you import AD data into a table for querying and shortly afterward the network administrator changes the groups that a user belongs to. The data in your table will be outdated and inaccurate. For accurate, up-to-date data, you need to directly query AD with OPENQUERY.
To query a linked server, the SQL Server service startup account must be running under a domain account and not a local system account. In addition, the account must have read access privileges to query AD. To verify the account settings, select Administrative Tools in the Control Panel, select Services, and double-click SQL Server (MSSQLSERVER) Service. Account settings are on the Log on tab. If you need to change the account, the service needs to be restarted in order for the changes to take effect.
After the account settings are in order, you can use the code in Listing 1 to create a linked server and query AD for user account information (e.g., first name, last name, distinguished name, username).
Listing 1: Querying AD Using a Linked Server |
 |
To use this code, you need to replace LDAP://DC=MyDomain,DC=com in callout A with the LDAP path to your AD domain. When I ran this code against my AD environment, the query returned 2,500 records.
ADO.NET Connection Manager Method
An ADO.NET connection manager allows an SSIS package to access data sources through a .NET provider. One of those providers is the OLE DB Provider for Microsoft Directory Services. You can use this provider to establish an ADO.NET connection to AD so that you can query AD for data. Here are the steps to create an SSIS package that uses an ADO.NET connection manager for this purpose:
- Start the SQL Server Business Intelligence Development Studio (BIDS). On the File menu, select New, then Project. After making sure the project type is Business Intelligence Projects, select the Integration Services Project template. Enter your project's name.
- Add a data flow. Click Toolbox on the View menu. Drag and drop the Data Flow Task icon from the Toolbox's Control Flow Items section to the Control Flow design surface. Double-click the newly added data flow to switch to the Data Flow tab.