Query AD using T-SQL

So I’m working on a small interface that needs to check certain computers in our enterprise for file changes, and fortunately these specific computers are kept in a group nested inside our AD structure. My thought was, if I can use a SQL query to go get that list of machines, put the results in a table, then have the interface read from that table it would always have a current list of these machines, with no maintenance required for adding and removing these PCs. Here’s what I came up with:

First, the database needs to have ad hoc queries enabled. This is done in the Surface Area Configuration for Features section of Surface Area Configuration on the affected DB server. You’ll need this in order to use commands such as OPENROWSET and OPENDATASOURCE. In this case, we’re going to use OPENROWSET. Set this first, then use the following query:

SELECT distinguishedName
FROM 
OPENROWSET('ADsDSOObject','adsdatasource'; 'DOMAINusername';'password', 'SELECT distinguishedName FROM ''LDAP://server'' WHERE memberOf=''cn=groupname,ou=OU_Container_Group_Lives_In,dc=server,dc=com'' AND objectCategory=''computer''')

Obviously you’ll want to substitute the DOMAIN\username and password, as well as the LDAP://server and the memberOf sections for your network’s info, but this structure will work. Feel free to substitute distinguishedName for another property if you want different results.

Eric

Eric Oszakiewski is a professional software developer based in Scottsdale, AZ with over 35 years of IT experience, and 19 years Native American Gaming experience. He is currently working as a Sr .Net/SharePoint Developer for General Motors, and also as a consultant.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube