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
LDAPnames of the directory elements you want to query - Write a
SELECTstatement 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 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 in your domain. Here is an example:
LDAP://ou=ou=Sales,dc=mydomain,dc=com where objectclass = userFinally, 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.
