Skip to content

The Data Queen

Explorations in Business Intelligence with Microsoft

  • Home
  • About Me
  • Privacy Policy

Categories

  • Analysis Services
  • Integration Services
    • Active Directory
    • SharePoint List attachments
  • Reporting Services
    • Cube Datasources
  • SharePoint
    • Attachments
    • InfoPath
    • Nintex Workflow
    • Report Services
  • SQL Database
  • Uncategorised
  • Windows

Recent Posts

  • How to Migrate from SQL 2005 to 2014
  • Moving Your SQL Databases to Azure – Things to Know
  • Setting up Hyper-V and creating a VM
  • Enable Hardware Virtualization in Firmware
  • Creating a Proxy User to run an SSIS package in SQL Server Agent

Tags

  • Active Directory
  • AD
  • ADO.NET
  • Aggregate
  • Analysis Services
  • Available Values
  • best practice
  • C#
  • Calculation
  • cascade
  • database
  • data connection
  • Dataset
  • Data Source
  • deploy
  • document library
  • drop down
  • Filter
  • import
  • InfoPath
  • look up
  • Microsoft
  • Multi-value
  • multivalue
  • Nest aggregate
  • Nintex
  • Package Configuration
  • Parameter
  • pick list
  • Report Paramter Properties
  • Script Task
  • SharePoint
  • SharePoint 2010
  • SharePoint List
  • SQL
  • SQL BI
  • SQL Server
  • SSAS
  • SSAS query
  • SSIS
  • SSIS 2008 R2
  • SSRS
  • subscription
  • Sum IIF
  • user

Recent Comments

  • Cynthia Watkins on Get around Active Directory Paging on SSIS import
  • Chris Lesnar on SSIS: Connect to PostgreSQL
  • Mahesh Kulkarni on How to Access a Specific RDP Session
  • Daniel Adeniji on How to Enable Custom Logging for an SSIS Script Task
  • Leon on How to Migrate from SQL 2005 to 2014

Archives

  • November 2015
  • September 2015
  • June 2015
  • May 2015
  • February 2015
  • January 2015
  • November 2014
  • September 2014
  • August 2014
  • May 2014
  • January 2014
  • November 2013
  • October 2013
  • September 2013
  • June 2013
  • May 2013
  • February 2013
  • January 2013
  • December 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • June 2011
  • May 2011

Interpreting Active Directory UserAccountControl

After importing users from Active Directory into a data warehouse for a client, there was a need to filter the list based only on Enabled users.  The functionality in Active Directory to indicate Enabled or Disabled is to right click on the user and select Disable Account.

image

This information does not get stored as a simple ‘Enabled’ or ‘Disabled’ flag, but is stored as part of a bitmask with a combination of various attributes, including whether the account in disabled.  You could wade through this Microsoft Support article How to use the UserAccountControl flags to manipulate user account properties, but at a high level what you need to know is that the flags are cumulative.  For example to disable a user’s account the  UserAccountControl attribute is set to to 0x0202 (0x002 + 0x0200). In decimal, which is how it will be imported into a SQL table, this is 514 (2 + 512).  For a Workstation Trust Account it’s 4098 (4096 + 2).  This means that Disabled accounts can have a multitude of values, depending on the type of account and what other attributes have been set. 

Simply importing the UserAccountControl would not allow an easy way to identify Enabled vs Disabled accounts.  One would have to build a lookup table of all possible combinations of attributes to determine which values in the UserAccountControl field indicate Disabled accounts.  For example 514 and 4098 are both Disabled accounts.

I wanted a way of importing ALL the users and mapping the value for Enabled/Disabled.  The simplest way to do this is to import first the Enabled users and then the Disabled users.  I will build on the C# script task from previous posts, the latest version of which you can find here: Importing Empty Fields from Active Directory.

Simply add this to the filter in the script:

ENABLED USERS:

    //Where useraccountcontrol <>2 means account is enabled
    ds.Filter = "(&(objectClass=user)(!useraccountcontrol:1.2.840.113556.1.4.803:=2))";

DISABLED USERS:

    //Where useraccountcontrol = 2 means account is enabled
    ds.Filter = "(&(objectClass=user)(useraccountcontrol:1.2.840.113556.1.4.803:=2))";

You can duplicate the C# code and have one script for Enabled users and one for Disabled users.  Add an IsEnabled field to the the table and alter the INSERT statement in the code to populate the field.

 

Helpful References:

http://social.technet.microsoft.com/Forums/en-US/connectors/thread/467a8c66-4473-4074-8ecc-900b4fc51b19

http://rajnishbhatia19.blogspot.ca/2008/11/active-directory-useraccountcontrol.html

How to use the UserAccountControl flags to manipulate user account properties

Posted on January 3, 2013Author Martina WhiteCategories Active DirectoryTags Active Directory, AD, Disabled, Disabled Account, Enabled, import, SQL BI, SSIS, UserAccountControl

Post navigation

Previous Previous post: Importing Empty Fields from Active Directory
Next Next post: Moving SharePoint List Attachments to the File System
Proudly powered by WordPress