At times, you need to do a quick query against LDAP, but if your access is quite restricted, sometimes instead of using SQL Server Management Studio, you can also use SSIS Project within Visual Studio to do it.
Steps
-
Launch Visual Studio.
-
Create a new project and double-click on Integration Services Project.
-
Name your Project name (e.g. ITNotaQueryLDAP), then click Create button.
-
For this exercise, we use the default Package.dtsx. If it’s not opened by default, just double click it on the right pane window.
-
Drag Data Flow Task from SSIS Toolbox window to the Control Flow window.
-
Now we want to add a Connection Manager by right-clicking on a blank area under Connection Managers pane and select New ADO.NET Connection….
A smaller window titled Configure ADO.NET Manager will pop up, click on the New button.
-
In a Connection Manager window, for the Provider, select OLE DB Provider for Microsoft Directory Services (under .Net Providers for OleDb). Then, click OK.
-
Once you’re back in Connection Manager window, click on the Test Connection button. Make sure you see the Test connection succeeded screen. Click both OK buttons.
-
After you clicked OK, you can see the configuration summary of your SSIS connection. Click OK again.
-
At this point, you have a Data Connection in your Connection Managers which you can rename to LDAP Data Connection.
-
Next, you can either double-click on the Data Flow Task or click on the Data Flow tab and drag ADO NET Source from the left pane, under Other Sources.
Double click on the ADO NET Source box, and select the LDAP Data Connection for ADO.NET connection manager:, SQL command for Data access mode: and use the following SQL command as an example under SQL Command text:.
LDAP://DC=ITNota,DC=Corp,DC=com;(&(objectCategory=Person) (SN=Smith));SN,givenName,displayName, mailNickName,sAMAccountName,mail,msExchHideFromAddressLists, homeMDB;subtree
This query specifically looks for all records for anyone with a last name of “Smith” in the domain. You need to replace the values of the DCs to your own domain and also the search string (Smith) to make it work in your situation. Click Preview button.
-
Once you clicked Preview button, You should see the first 200 rows.
That’s all there is to it.
Of course, there’s a caveat of using this as you can only view the first 200 rows of your result. This is not intended to be used as a solution of any deployment other than a workaround when you are limited with what you can access and you need to find a few records from your LDAP/Domain Services.
Randy Erickson says
This is fantastic! I have been trying to figure out how to query our AD and this is the solution that worked. As a follow-up, how can I find out the field names in our AD in order to add them to this query? If I go to Active Directory users and Computers, Right Click on Users, Select Find, Type in a name to search for and Click Find Now. Then, under View, Choose Columns, the names do not necessarily work in the query. For example, the column named Logon Name(pre-Windows 2000) seems to be the same as samaccountname in your query. Also, would it be possible to use one of the Loop Containers to loop through the alphabet and export all to Excel? Basically I am trying to export a user list from AD to Excel to be used as a lookup table.
Thanks in advance!
Randy
platt says
You can always create a complete package to query everything and dump the data to a CSV (or Excel).