Query Active directory form SQL server

A couple of weeks ago we had the needed to get some users data and our best option was querying directly to Active directory from an SQL server, this could be the best option in some cases, taking in mind that a healthy AD with a good maintainment is allways our best shot. Microsoft Active directory is a secure database with a lot of fields which are possible to fill with all user and employees information, and at the same time can provide us with some other important data, like creation time, last logon, expiration date….

In this first example we are going to get complete name of all users, email address and phone number.

Ok, let’s start, first of all we’ll create an interface between our SQL server and active directory this is known as ASDI and is the right way to link this two systems

You must change @rmtuser and @rmtpassword values with a valid user credentials which is allowed to read AD

After this we can start to query directly to AD in this way. Remember to change the LDAP link with your complete OU Link

If you want to learn more, the is an Excel sheet about the AD properties, how they map to the “Active Directory Users & Computers” tool and others at  Richard Mueller’s site

Hope this was useful


Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.