Querying Active Directory from SQL Server

SQL Server provides some pretty flexible integration with Active Directory through the ADSI Linked Server provider, something that’s present by default when you install SQL Server. If you’ve never used it before, it allows you to connect to a domain controller and query AD the same way you’d query any other linked server. For example, it gives you the option to:

  • Identify when logins to SQL Servers or databases that support financial applications exist, but have no matching AD account (either direct integrated logins, or if SQL logins or rows in a “User” table have been set up to match the AD login)
  • Kick off alerts to provision the user in various systems based on their AD group membership
  • Automatically trigger an action when a new account appears in active directory (for example, we auto-provision security badges and send an email alert to our head of security to assign the appropriate rights)

While much of this could also be done from Powershell as well, we use the SQL Server Agent to manage many of our scheduled job (because it’s so handy to have the agent remotely accessible), as well as sometimes just needing data from AD in a query. To support a number of processes we have in place, we run a synchronization job every so often throughout the day that pulls about two dozen fields for all users and synchronizes them into a table if anything has changed.

Setting up the linked server itself is pretty straightforward (courtesy of http://community.spiceworks.com/how_to/show/27494-create-a-sql-linked-server-to-adsi):

  1. Create the linked server itself
  2. Set the security context (if you want to query AD as something other than the SQL Server Service account – by default, all domain users can do this and it’s only required if the domain is remote or if, for some reason, your SQL Service account’s AD rights have been restricted, like if you’re running as “LOCAL SERVICE”)
  3. Enable OPENQUERY (Ad Hoc Distributed Queries)

You’ll notice that setting up the linked server itself doesn’t actually specify where Active Directory is located or what domain/forest you’ll be querying – that’s actually done in the query itself. In each query, you’ll need to specify the FQDN (Fully-qualified domain name) of the domain (or OU) of the domain you’re querying. For example, we’d get all users from a domain by issuing the following query (in this example, “ADLinkedServerName” is the linked server we just created, and our domain is “corp.mycompany.local”):

SELECT EmployeeNumber, Name AS FullName, givenName as FirstName, sn as LastName,
L AS Location, samAccountName as ADAccount
FROM OPENQUERY(ADLinkedServerName,'SELECT Name, L, givenName, sn,
EmployeeNumber, EmployeeID,samAccountName,createtimestamp
FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
WHERE objectClass =''user''') ad

This query will search that OU (“Users”, in this case) and everything below it, so changing the FROM to “LDAP://DC=corp,DC=mycompany,DC=local” would fetch the entire directory (for all the “user” objects), regardless of what folder they appeared it – if your directory puts users in another OU (like “Associates”, for example), you should adjust the query accordingly.

For column names, you can pull any AD properties at all that you’re looking for – even custom ones that aren’t part of a standard AD configuration. To get an easy list of AD properties to choose from, I like using ADSIEDIT (part of Microsoft’s Remote Server Administration Tools – download RSAT for Windows 7 or RSAT for Windows 8.1) – just drill down all the way down to an object, like a user, right click on them and select “Properties”, and you can see a list of all the properties on that account. If you’ve got Domain Admin rights, this tool can be used to modify these values too, but for querying, you only need to be a domain user or somebody who has rights to browse AD. Make a note of the names of particular properties that you’re interested in – also note that AD queries are case-sensitive, so you’ll need to note the casing of these properties as well.

One potential gotcha that I’ve run into is that maximum result size that AD will return in a single query can be set as part of domain policy – by default it’s 1000 records at once, and can be configured by setting or adjusting the “PageSize” property on your domain controllers (see https://support.microsoft.com/kb/315071/en-us). Also, there’s a “MaxResultSetSize” property as well that’s set to 256KB by default, but I’ve never hit it – unless you’re pulling every single property back, you’d likely hit the PageSize row limit before you hit the ResultSize byte limit, but remember that both are there. If you do hit the AD result count limit, it will return the rows up to the limit, but then execution stops with a kind of cryptic error:

Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "YOURDOMAIN".

If your domain is larger than the PageSize limit, you’ll need to cut your query into multiple return sets of data so you don’t exceed the limit on any single query. Since our domain contains about 2400 users, we were able to do it in two queries, broken up like this:

SELECT samAccountName
  FROM OPENQUERY(ADLinkedServerName,'SELECT samAccountName
                                       FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
                                      WHERE objectClass =''user''
                                        AND givenName<''L''') as c
UNION ALL
SELECT samAccountName
  FROM OPENQUERY(ADLinkedServerName,'SELECT samAccountName
                                       FROM ''LDAP://OU=Users,DC=corp,DC=mycompany,DC=local''
                                      WHERE objectClass =''user''
                                        AND givenName>=''L''') as c

By dividing the names on L, this cut the directory roughly in half – if yours was larger, you could divide it by querying each OU separately, or by looping through letters of the alphabet, or whatever makes sense in your setting. You could even do something dynamic like pull as many records as you can, then grab the value from the last record you pulled and use it as the baseline to pull the next set as far as you can, and then repeat until you run out of records. Linked servers don’t allow you to dynamically assemble your query at run-time – it has to be hard-coded in the query – but there are some ways around that (like building your OPENQUERY as a string and then executing it via sp_executesql, for example).

Now that you have your AD records stored in a temp table, you can identify new/changed records and merge them into a SQL table you already have ready using an INSERT/UPDATE/DELETE or MERGE statement, or possibly trigger notifications or some other business process.

I hope this is helpful – if you’d like some more detail, please leave a comment and I’m happy to elaborate where it’s necessary!

Logon Failure: The user has not been granted the requested logon type at this computer

The printer I use to connected to a domain computer, but the computer I print from is not on the domain, so I ran into this ugly error when I tried to map the printer:

Logon Failure: The user has not been granted the requested logon type at this computer

This occurs because the workgroup computer is trying to pre-connect to the domain computer, and since it’s un-authenticated, it connects as “Guest”. Under normal circumstances, the guest account is disabled or, at least, it has been denied rights to connect over the network. Here’s how you remove this restriction:

  1. Download and install the Windows Resource Kit Tools (http://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe). On Vista, you may receive an error that these tools are not compatible, buy you can ignore it – at least in this case, it doesn’t cause any problems, and currently. There’s no new version of the tools available.
  2. From the start menu, click “All Programs” -> “Windows Resource Kit Tools” -> “Command Shell” Run the following commands, in this order (and they’re case sensitive):
    1. net user guest /active:yes
    2. ntrights +r SeNetworkLogonRight -u Guest
    3. ntrights -r SeDenyNetworkLogonRight -u Guest

That’s it! You’ve enabled guest access, so non-domain computers will at least have the ability to connect, though they’ll be restricted to the permissions granted to the local “Guest” account, so you’re not opening up much of a security risk.

Join an Active Directory domain and keep your local profile intact

Recently, I had to join a number of computers in a small office to a domain, but the users all had local profiles that they wanted to keep. Things were a mess – some people’s usernames were the Last name, first initial (the username format I’d chosen for the domain), some were using their full names, and some were using the local administrator account. When I added these computers to the domain, their “domain user” would log in, and would create a new, empty profile. To avoid this took a few extra domain-joining steps, so I wanted to detail them here.

A note: On the computers that were using the local Administrator account as their main login, I had to create a new user and make them a local admin. I just called this user “Transition”, and deleted it once the process was over.

And now for the steps:

  1. Join the computer to your domain, and grant the new domain user local administrator rights before rebooting. Reboot, and log in using the new domain user. This will create a new, empty profile with that user’s domain login.
  2. While still logged in as the new domain user, take ownership of the old, local profile folder. To do this, right-click on the folder, select “Properties”, go to the “Security” tab, click “Advanced”, and then the “Owner” tab. You can set the owner to either the local admins group or the current user – set it to the current user. You must be a local administrator to take ownership (from step 1).
  3. Log out, and log in using either the local administrator account or the transition account you created.
  4. At this point, you can revoke local admin rights from the domain user if they won’t need them. They were only needed to take ownership in step 2.
  5. Open REGEDIT, select the “HKEY_Users” branch, and select “Load Hive…” from the file menu. In the user’s profile folder, there’s a hidden file called “NTUSER.DAT” – that’s the one you want to load. Make sure you’re loading the file from the old profile, not the one that was created in step 1. You can call it whatever you want when you load it – it doesn’t matter. Also, make sure you’ve made a backup of this file before you edit it.
  6. Right-click the user branch you just loaded, and click “Export…”. Export it as a REG file on your desktop.
  7. Open the registry file you just created in either Notepad or Wordpad (I find Wordpad faster for the this step, but it doesn’t really matter). Search for occurrences of “\OLDPROFILEFOLDERNAME\” and replace them with “\NEWPROFILEFOLDERNAME\”. I’ve generally found about 100 references, but it depends on the size of your registry. Also, make sure you convert 8.3 folder names as well – “\OLDPRO~1\” should become “\NEWPRO~1\”!
  8. Save the file after your found/replaced all the occurrences. Double-click the REG file to load it back into your registry (into the user’s hive). You’ll get a warning that not all data was loaded because some keys were in use – that’s fine.
  9. Since not all keys were imported, we’ll need to fix a few folders by hand. Select the user’s hive, and “Find” any occurrences of the old profile path, replacing them with the new path.
  10. With the main user hive folder selected, go to the “File” menu and select “Unload Hive”. The changes are saved automatically, which is why it’s important that you made a backup in step 5. Close REGEDIT.
  11. Rename the domain user’s profile folder to “Username.Empty” (since it’s essentially a blank profile), and rename the user’s local profile to “Username”, which matches the folder name of the profile that was created in step 1.
  12. Log out, and log in as your domain user, enjoying your old profile just as you left it!

This process can be repeated for as many users as you’d like to transition to the new profiles, and you should maintain every one of the settings for your programs. In fact, I’ve never had a program even realize something is afoot, though I’ve only done this on a half-dozen computers.

Please let me know if you have any feedback, and I’d be interested to know of any experiences you have trying this out!