Delphi ADO query - how to search for AD users with specific bits in userAccountControl

Question

I am using delphi 10.4 and using TADOConnection and TADOCommand component to access Active Directory data. I need to search for user accounts with a specific bit set in userAccountControl filed (i.e. "must change password on next logon" or "disabled" etc).

I have a working query that will search for user accounts where userAccountControl has a specific value, but I need a query that returns ONLY the users with a specific bit set.

For example, "select sAMAccountName,userAccountControl from 'LDAP://DC=AD,DC=LOCAL' where userAccountControl='514' " will return all normal disabled users but will miss users who are disabled, but have other bits set in "userAccountControl". I'd like to be able to construct a query that can ONLY return user IDs with a certain bit set in userAccountControl.

I saw example code for Powershell, vbscript etc that is supposed to achieve something similar: "(&(objectCategory=computer)(userAccountControl:1.2.840.113556.1.4.803:=8192))"" but it does not work in Delphi.

Any pointers are greatly appreciated Thank you

In SQL the bitwise AND is `&`. So `userAccountControl & 2 = 2` for example would check only the disabled flag. The `&` part masks out all other bits but the one in question and `=` makes sure desired bit was set. — Brian, Jun 13 '22 at 14:14
Thank you Brian, you are correct, and as you can see from my question, the syntax of my SQL statement is using & and the statement works fine in Powershell. However it does NOT work in Delphi - and that's what I'd like to resolve. Thank you — Vlad, Jul 08 '22 at 13:57

fduron · Answer 1 · 2023-02-20T21:05:25.053

I use a TADOQuery instead of a TADOCommand, adding a calculated TStringField (CalcUserAccountControl) of length 200, and declaring the following type and constants: (you can check the values of every flag at UserAccountControl property flags)

    type
  TControlFlags = (  SCRIPT, ACCOUNTDISABLE, HOMEDIR_REQUIRED, LOCKOUT, PASSWD_NOTREQD,
                     PASSWD_CANT_CHANGE, ENCRYPTED_TEXT_PWD_ALLOWED, TEMP_DUPLICATE_ACCOUNT,
                     NORMAL_ACCOUNT, INTERDOMAIN_TRUST_ACCOUNT, WORKSTATION_TRUST_ACCOUNT,
                     SERVER_TRUST_ACCOUNT, DONT_EXPIRE_PASSWORD, MNS_LOGON_ACCOUNT,
                     SMARTCARD_REQUIRED, TRUSTED_FOR_DELEGATION, NOT_DELEGATED,
                     USE_DES_KEY_ONLY, DONT_REQ_PREAUTH, PASSWORD_EXPIRED,
                     TRUSTED_TO_AUTH_FOR_DELEGATION, PARTIAL_SECRETS_ACCOUNT);
...
const
  //Bit position of every flag
  BitControlFlag : Array [TControlFlags] of Integer =
  (0, 1, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26);
  //Description of every flag
  StrControlFlag : Array [TControlFlags] of string =
  ('|SCRIPT', '|ACCOUNTDISABLE', '|HOMEDIR_REQUIRED', '|LOCKOUT', '|PASSWD_NOTREQD',
   '|PASSWD_CANT_CHANGE', '|ENCRYPTED_TEXT_PWD_ALLOWED', '|TEMP_DUPLICATE_ACCOUNT',
   '|NORMAL_ACCOUNT', '|INTERDOMAIN_TRUST_ACCOUNT', '|WORKSTATION_TRUST_ACCOUNT',
   '|SERVER_TRUST_ACCOUNT', '|DONT_EXPIRE_PASSWORD', '|MNS_LOGON_ACCOUNT',
   '|SMARTCARD_REQUIRED', '|TRUSTED_FOR_DELEGATION', '|NOT_DELEGATED',
   '|USE_DES_KEY_ONLY', '|DONT_REQ_PREAUTH', '|PASSWORD_EXPIRED',
   '|TRUSTED_TO_AUTH_FOR_DELEGATION', '|PARTIAL_SECRETS_ACCOUNT');

...

After that, at the OnCalcFields method of the TADOQuery, just check the bit at the UserAccountControl field from the query,

procedure TForm2.TADOQuery1CalcFields(DataSet: TDataSet);
var
  CurrentUAC : Integer;
  StrUAC : String;
  ControlFlags : TControlFlags;
begin
  CurrentUAC := TADOQuery1.FieldByName('userAccountControl').AsInteger;

  for var aControlFlag := low(ControlFlags) to high(ControlFlags) do
    if CurrentUAC and (1 shl BitControlFlag[aControlFlag]) <> 0 then
      strUAC := strUAC + StrControlFlag[aControlFlag];

  TADOQuery1.FieldByName('CalcUserAccountControl').Value := strUAC;
end;

At the calculated fiel I got a string with every flag of the UserAccountControl, something like:

|PASSWD_NOTREQD|WORKSTATION_TRUST_ACCOUNT
|WORKSTATION_TRUST_ACCOUNT|DONT_EXPIRE_PASSWORD
|ACCOUNTDISABLE|PASSWD_NOTREQD|WORKSTATION_TRUST_ACCOUNT

With that in mind you can use the CalculatedField value in order to filter the result set to show only those UserAccountControl flags you require.

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Feb 18 '23 at 05:33

score 0 · Answer 2 · edited Feb 16 '23 at 12:41

Using TADOQuery you can set Filtered := True and add an OnFilterRecord method in order to get only those records with a specific bit at the UserAcountControl field, like the following code:

procedure TForm2.TADOQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
var
  CurrentUAC : Integer;
const
  ACCOUNTDISABLE = 1; //the bit 1 has the ACCOUNTDISABLE flag 
begin
  CurrentUAC := TADOQuery1.FieldByName('userAccountControl').AsInteger;
  //Accept only those records without the ACCOUNTDISABLE flag
  Accept := CurrentUAC and (1 shl ACCOUNTDISABLE) = 0; 
end;

Delphi ADO query - how to search for AD users with specific bits in userAccountControl

2 Answers2