Getting Enabled / Disabled Active Directory Accounts in ColdFusion and T-SQL

While working on recent telephone directory project based on Active Directory, it occurred to me that I am listing all users/accounts in Active Directory regardless of the fact that they might be disabled. Usually, my workplace disables a user's account once he/she is no longer working for the company. So here is how to get a list of only enabled or only disabled accounts from Active Directory. The examples cover doing this through ColdFusion's built in <cfldap> and using T-SQL syntax by leveraging Active Directory as a linked server. I have covered setting up Active Directory as a SQL Server linked server at Querying Active Directory Through SQL Server Here is how this is done with a simple cfldap query. Before you run this code below, please replace the values in the <> This query gets the "distinguishedName", "givenName" and "sn" attributes from Active Directory. The complicated part is the filter it uses. Here is the breakdown:

• (objectCategory=Person) tells cfldap to get only objects that match the category of "person"
• (objectClass=User) tells cfldap to get only objects that match the class of "user"
• (!(userAccountControl:1.2.840.113556.1.4.803:=2)) tells cfldap to get only objects for witch when a bitwise AND is applied to the "userAccountControl" attribute and the number 2, the result does not equal to 0. This is a bit complicated but what it means is that if the bitwise operations does not return 0, the account is not disabled. To specify that only disabled accounts should be returned instead, we would use "(userAccountControl:1.2.840.113556.1.4.803:=2)" instead of "(!(userAccountControl:1.2.840.113556.1.4.803:=2))". I did not come up with this :-) Here is Microsoft's explanation on how to use bitwise filter.

<cfldap
 action="query"
 name="enabledADUsers"
 start="<replaceWithBaseDN>"
 server="<replaceWithActiveDirectoryServer>"
 username="<replaceWithDomainAccount>"
 password="<replaceWithDomainAccountPassword>"
 scope="subtree"
 filter="(&(objectCategory=Person)(objectClass=User)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))"
 attributes="distinguishedName,givenName,sn" timeout="0">

<cfdump var="#enabledADUsers#" />

Here is the same thing but done through a SQL Server linked server. The filter is the same but written with the T-SQL syntax. The differencs is in how the filter on the "userAccountControl" attribute is applied through T-SQL. In the code below, the line "userAccountControl & 2 = 0" is equvalent to the cfldap filter "(!(userAccountControl:1.2.840.113556.1.4.803:=2))" which specifies that only accounts that are not disabled should be returned in the query. To specify that only disabled accounts should be returned instead, we would use "userAccountControl & 2 <> 0".

select userAccountControl,
 distinguishedName,
 givenName as firstName, sn as lastName,
from openquery(<replaceWithNameOfLinkedServer>, '
 select userAccountControl, distinguishedName, givenName, sn
 from ''LDAP://<replaceWithBaseDN>''
 where objectCategory = ''Person''
 and
 objectClass = ''user''
 order by sn
 ')
where userAccountControl & 2 = 0

Here are some links on the subject:
How to query Active Directory by using a bitwise filter
How to use the UserAccountControl flags to manipulate user account properties
How Can I Get a List of All the Disabled User Accounts in Active Directory?
Retrieving Basic Active Directory Account Settings in C#