Query Active Directory from SQL Server (again)

Unlike my last post on the issue, instead of simply pointing to another blog for instructions on how to query Active Directory from SQL Server, I’ll provide some useful tips on getting things all working.

Firstly, make sure CLR is enabled:

use master
sp_configure 'clr enabled', 1
reconfigure with override

Next, extract and open up the .sln file included in MSADHelper2.rar and build the project (Ctrl+Shift+B). Drill down to the MSADHelper2binRelease directory and copy the resulting MSADHelper2.dll file to a location of your choice – I dropped it right in C:\Program Files\Microsoft SQL Server. Note the location, you’ll need it soon.

Now, copy the script from Igor’s post and place it in a new query window in SSMS, but DON’T RUN IT YET! For the installation section, I recommend that you run each statement by itself, to catch problems early. I’ll step through each statement with you.

• Lines 1-4: Set connection settings
• Line 14: Update the path to point to the location of MSADHelper2.dll and run to create one key
  • If you receive an error here, especially about memory or unable to load/register, restart the  SQL Server service and start over
• Line 16: Run to create another key
  • The path may need to be updated if a .NET 2.0 version change occurs. If you get an error about not locating the file, verify the path. Likely, the framework version you have installed is different from 2.0.50727
• Line 22: Create a login based off the first key
• Line 24: Create another login based off the second key
• Line 28: Tell SQL Server it’s okay to run unsafe assemblies from the first login
  • Why UNSAFE? Because the assembly accesses a resource that SQL Server cannot make guarantees about
• Line 30: Tell SQL Server it’s okay to run unsafe assemblies from the second login
• Line 39: Update to reflect the database where you want the CLR UDFs to reside, and run it
• Line 42-44: Run this, but keep in mind the note about .NET versions made in Line 16
• Line 46-48: Update the to the same provided in Line 14
• Line 59-116: These simply define the UDFs, If you want to change the schema or naming convention, this is the time to do it.
  • The reason why I recommend running these lines as a batching instead of individually, is that if everything else above this worked, these should also work without a problem
• Line 121: Run just to test everything out… That’s it, you’re done

A couple caveats:

• Figuring out the correct provider path is always a problem for me. Be sure to check with your local AD admin and straighten that out – more on this in the next bullet point
• Locating the correct OU or property
  • You can try using these functions to locate the correct OU or property, but to be completely honest, you’re much better off installing the Windows Server 2003 Service Pack 1 Administration Tools Pack. With this, you can open the MMC snap-in for Active Directory Users and Groups (located inside the Start Menu, Administrative Tools folder).
    1. Once installed, open up that ADUG snap-in and drill down to a user (any user you want).
    2. Bring up their properties (double-click), select the “Member Of” tab, and you can see the correct DC and OU settings.
    3. For example, Active Directory Folder, “DOMAIN.COM/Users/Awesome” would translate to something like this:

       LDAP://OU=Awesome,OU=Users,DC=DOMAIN,DC=COM

• Once you have the correct provider information, try out Line 125-127. If all is well, you should get a Status = Passed.
• Memory errors
  • I am dealing with this issue on one specific server, receiving messages like, “Could not load file or assembly ‘System.DirectoryServices”. I haven’t figured out a solution yet, but will be sure to update this post when I do.
• Speed
  • Not sure what the bottleneck is, but I would not recommend trying to perform lookups live. You’re much better off caching a copy in a local table and refreshing it nightly. Just create the destination table and

    TRUNCATE dbo.ADUsers
    INSERT INTO dbo.ADUsers EXEC dbo.usp_GetUserList

• Properties
  • There is often quite a bit of information stored in Active Directory. Be sure to grab important things like sAMAccountName and displayName and remove things like memberOf, depending on your specific needs.

That’s all for now. Got any comments or questions? I’d be happy to assist, just drop a comment in below.