Microsoft's object linking and embedding for database (OLE DB) providers can create connections to non-SQL Server data sources.
Unlike the older ODBC standard, OLE DB can also connect to some non-relational data sources, such as Active Directory.
The OLE DB providers make the non-relational data appear relational. This means that SQL Server can query the contents of an
Active Directory domain, which is structured like a tree, and not a flat table. Data retrieved from Active Directory can be made
into a view or even joined with existing SQL Server data. There are three components to querying your Active Directory.
- Create a linked server definition which is defined in SQL.
- Look up the LDAP names of the directory elements you want to query
- Write a SELECT statement to get them
Let’s start with the first step. You can use either the system stored procedure sp_addlinkedserver or the SQL Management Studio
to set up a linked server to Active Directory. Let’s simply use the stored procedure to create and configure the linked server.
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
The first parameter, ADSI, is the name you'll use in querying the linked server. Active Directory Services Interfaces is
the product name, ADSDSOObject is the provider, and adsdatasource is the built-in data source name. Once the object is created,
access the linked server’s properties, ADSI, and click on the security tab. Click the radio button for “Be made using this security
context”. Provide a user name and password for an account that has access to query the domain.
The next step is to figure out what the scope and elements that you wish to include in your query. For example, you may want to
query user objects in the Sales Organizational Unit for in your domain.
Here is an example:
LDAP://ou=ou=Sales,dc=mydomain,dc=com where objectclass = user
Finally, the last step is to create the SQL query. You must use the OPENQUERY function to access the data. 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. Let’s look at a sample query that can be used to
get the Display Name and Email addresses for the user’s in the Sales OU for a domain called mycompany.com.
SELECT * FROM OpenQuery(ADSI, 'SELECT displayName, mail FROM
"LDAP:// ou=Sales,dc=myCompany,DC=com" where objectclass = '"user" ')
You can find a list of common attributes for the user class by visiting this link:
Once you get the hang of it, you will probably find that it is fairly easy to get the most current information about your
users, computer, and other objects in real time, and straight from the source.
Recommended Books & Training Resources