How To Resolve Active Directory Account Lockouts With PowerShell

[Originally posted by]:,2-848.html

Active Directory (AD) is a wonderful service. You can log on from anywhere on the network using the same username and password. It couldn’t be easier — that is, until you forget to close a remote desktop session, or a worm spreads across the network, or you forget you’re running a scheduled task as your user account, or… Well, you get the point.AD is an extremely useful product; this is why its adoption rate is so high. The problem is when an account begins to lock out for no reason whatsoever.Or so you think.

MORE: Essential PowerShell Cmdlets for Active Directory

AD Account Lockout Policies

Many organizations have (or should have) account lockout policies. This policy is a securitymeasure to prevent unauthorized parties from trying to guess the password continuously or brute force a password.Account lockout policies are commonplace in Active Directory and consist of a simple approach to combating a major security issue.Attempt the wrong password a certain number of times, and the account is unusable until an administrator manually re-enables it again. The intention is true, but in some instances, the implementation is not.

In some situations, especially when a password is changed, an account can suddenly start getting locked out consistently for no apparent reason. A user calls the help desk, the help desk re-enables the account, and a little bit later, the account is locked out again. It’s a frustrating experience for both the user and the help desk. Sometimes the problem is exacerbated by the unknown origin of the lockouts. Somewhere, somehow there’s a person, a script, or a process continually trying the same wrong password over and over again, but no one knows where.

So how do you track down these annoying lockouts? One way is by using a PowerShell script. But first, let’s go over what happens when an account is locked out.
Resolving A Locked AD Account

In a Windows Server 2008 or later environment, there is a short back and forth between the client system, the client system’s domain controller, and the domain controller holding the Primary Domain Controller (PDC) emulator role. This occurs as follows:

Whenever a user account authentication is attempted, the credentials are sent up to the appropriate domain controller for the client system’s subnet.
If the password is wrong, the client system’s domain controller forwards the request to the domain controller holding the PDC emulator role. This is because the client system’s domain controller might not have the most current password, and as a design feature of Active Directory, the domain controller holding the PDC emulator role always will.
The PDC emulator tries the password again, and if it is still found to be wrong, the PDC emulator increments the badPwdCount attribute on the user account.
An event ID 4740 is generated on the PDC emulator with the client system IP address that initiated the original request and with the user account.
The PDC emulator then informs the client system’s domain controller that the password is, in fact, wrong.
The client system’s domain controller then notifies the client system that the password was wrong.
Where would be the best place to find the source? The answer is at the PDC emulator. The reason for that is because every account lockout is recorded there in the security event log. The PDC emulator is a central place that can be queried for all account lockout events.

Using PowerShell To Track Down The Source Of AD Account Lockouts

To query the PDC emulator, we’ll use PowerShell’s Get-WinEvent cmdlet. This is an extremely useful cmdlet for quickly parsing through one or more event logs on a server. We’re looking for an event ID of 4740. First, we need to find the domain controller that holds the PDC emulator role. One way to do this is by using the Get-AdDomain cmdlet.

Once we know the PDC emulator, then it’s just a matter of querying its security event log for event ID 4740.

I have an account called abertram that is locked out. Let’s see how to track down the culprit.

We’ve got the PDC emulator now, so let’s query its security log with a PowerShell script.

## Define the username that’s locked out
$Username = ‘abertram’
## Find the domain controller PDCe role
$Pdce = (Get-AdDomain).PDCEmulator
## Build the parameters to pass to Get-WinEvent
$GweParams = @{
‘Computername’ = $Pdce
‘LogName’ = ‘Security’
‘FilterXPath’ = "*[System[EventID=4740] and EventData[Data[@Name='TargetUserName']='$Username']]"
## Query the security event log
$Events = Get-WinEvent @GweParams

This gives us the lockout event.

But we don’t have the originating client system yet. To get that, we’ll have to dig a little deeper. The actual username is buried in each event’s Properties value. To find the username in each event, we can simply use this line.


This finds the username in the first event and in the first instance of the Properties value. Luckily, the client system is just in the second instance of Properties.


Once you know where the client system name is located, it’s just a matter of inserting it into a loop, and we’ve found the culprit.
Now you’re armed and ready to go the next time the help desk rings you with that incessant AD user account that keeps getting locked out.


Adding Subquery in a Select Statement in SQL Server 2012

[Originally posted by]:

Adding Subquery in a Select Statement in SQL Server 2012

By Rohatash Kumar on Aug 17, 2012

Today, I have provided an article showing you how to add a subquery to a select statement in SQL Server 2012. A subquery is also called an inner query. The Transact-SQL language offers the ability to compare a column value to the result of another select statement. Such an inner select statement nested in the where clause of an outer SELECT Statement is also called a subquery. The statement which contains the subquery is called the outer query. A subquery is a query that is nested inside a select, insert, update, or delete statement, or inside another subquery. Here, we will see how to use a subquery with the select statement.

Properties of Sub-Query

  1. A sub-query must be enclosed in parenthesis.
  2. A sub-query must be put in the right hand of the comparison operator.
  3. A sub-query cannot contain an ORDER-BY clause.
  4. A query can contain more than one sub-query.

Now create a table named EmployeeDetail with the columns emp_fname, emp_lname, emp_no, emp_add. The table looks as in the following:


Subquery in SELECT Statement

You can construct a SELECT statement with a subquery.

    SELECT SUM(emp_no)
    FROM EmployeeDetail   
  ) as Totalemp_no
  EmployeeDetail where emp_fname='Copper'

In the preceding query I have inserted a fourth column as the subquery in the SELECT statement and named the column Totalemp_no. The  sum statement returns the total number. The preceding table shows the result set returned by the outer SELECT statement. Now press F5 to see the result:


Subquery in FROM Clause

You can construct a FROM Clause with a subquery.

SELECT * FROM (SELECT *  FROM EmployeeDetail WHERE emp_no = '39') EmployeeDetail whereemp_fname='Copper';

The following query also produces the same result:

SELECT * FROM  EmployeeDetail where emp_no = '39' ;

Now press F5 to see the result:


Subquery in WHERE Clause

You can construct a WHERE Clause with a subquery. We can use the following operators with Subquery.

  • comparison operator
  • IN operator
  • ANY or All operator
  • EXISTS function

Creating another table:

Now create a table named dept with the columns dept_name, dept_add. The table looks as in the following:


Subquery and Comparison operator

This example shows the simple subquery that is used with the operator =.

The following query determines the first name and last name of the employee with the operator =.

select emp_fname,emp_lname from EmployeeDetail where emp_add =(select dept_add  from dept wheredept_name ='finance');

Now press F5 to see the result:


Subquery and in operator

This example shows the simple subquery that is used with the IN operator:

--subquery and in operator
select * from EmployeeDetail where emp_add IN(select dept_add from dept where dept_name='finance');

Now press F5 to see the result:


Subquery and ANY operator

The ANY operator evaluates to true if the result is an inner query containing at least one row that satisfies the comparison. This example shows the simple subquery that is used with the ANY operator.

--subquery and any operator
select emp_fname,emp_no from EmployeeDetail where emp_add >any(select emp_add from EmployeeDetail );

Now press F5 to see the result:


The “ALL” Operator evaluates to true if the evaluation of the table column in the inner query returns all the values of that column. This example shows the simple subquery that is used with the ALL operator.

--subquery and all operator
select emp_fname, emp_no from EmployeeDetail where emp_add <=all(select emp_add from EmployeeDetail);

Now press F5 to see the result:


Integrating a persisted WF4.0 Workflow with MVC

[Originally posted on]:

Integrating a persisted WF4.0 Workflow with MVC


Writing this as lately I’ve been interested in WF4.0 (it’s always pronounced “dub-F” in my head) and saw a post about using WF with MVC to handle a site registration wizard. Thetutorial it linked to used a state machine (WF only supports Sequential OOB) workflow without persistence, which made me think that you could easily use a normal workflow with persistence to do a lot of other things inside a web application. WF which is part of .NET4.0 allows you to easily create and call workflow processes, small or large, from anywhere inside .NET code and it’s been drastically improved since .NET3.5. That ease makes it practical to use workflow in places which it was previously too complicated to integrate, places where we normally manually code a process into business or web logic making it difficult to maintain and add standard features like tracing/auditing.

To run the sample or try this yourself you will need .NET4.0 and Visual Studio 2010, the instructions below are a simple overview and do not include all code or references.

1. Creating the WF SQL Persistence store

WF4.0 supports using a default SQL Persistence store, using a DB created using scripts in the .NET4.0 framework directory. Simply create a new DB (call it whatever you like) on your server and run the following scripts:

  1. C:WindowsMicrosoft.NETFrameworkv4.0.21006SQLenSqlWorkflowInstanceStoreSchema.sql
  2. C:WindowsMicrosoft.NETFrameworkv4.0.21006SQLenSqlWorkflowInstanceStoreLogic.sql

Multiple workflows can share the same DB or you can separate them out if you want.

2. Creating the MVC site and Workflow Activity Library


In Visual Studio, create a new MVC Web Application and add another project of type “Activity Library” (under templates Workflow) to hold your workflow. I also added a console application to test the workflow independently but this is not necessary.

3. Creating the Workflow


Delete the existing workflow activity and create a new one with the name WizardActivity, in the activity drag and drop a Flowchart from the toolbox onto the workflow (using this instead of a state machine) and add a variable “Outcome” to the Flow chart scope (used to store the outcome Next/Back of the steps). In the Activtiy project add a new Class, WizardStep, which will be generic custom activity used for all the steps in the workflow. This activity contains the bookmark code which is used to halt and persist the workflow at the steps. Add the code and compile the project, this will update the workflow toolbox adding WizardStep and allowing you to drag and drop it onto the workflow. Add three Wizard steps to the workflow, setting the Name to “StepN“, Bookmark Name to “StepN” and Input to “Outcome” (this is an output parameter which will set Outcome to the value supplied when resuming the Bookmark in the custom activity). Add a custom Complete activity (which does nothing) and link up the workflow using Decision controls (set condition to “Outcome.Equals(“Next”)”) and arrows as per image. Lastly add a new Class WizardHostHelper (copy code from sample), which acts as the host for the workflow; creating the Workflow application object, setting instance store values, starting/resuming the workflow, actioning the bookmarks.

4. Update the MVC application to use the Workflow

Now to integrate the workflow into the website.

  1. Add a new class WizardModels, to hold the model logic for the wizard steps and workflow service (wrapper to WizardHostHelper).
  2. Add a new controller WizardController, for controller logic used in the wizard.
  3. Add four new views under Wizard, Step1-3 and final, for the views used in the Wizard.
  4. Update AccountModel, add a Guid parameter workflowKey to the MembershipService.CreateUser method, this will store the Workflow Instance key in the standard ASP user security providerUserKey. Also add a method GetUserWorkflowKey to return the key for a username.
  5. Update AccountController, changing Register method to start a workflow instance for the user using the WizardModels.WorkflowService and supply the workflow key into CreateUser. Update both Register and Logon to redirect action to the Wizard index.
  6. Update the web.config to set the instance store connection string.

And that should be it, registering a new user will now start the workflow process and redirect to the first step. Next/Back buttons on the step views cause the controller to action the steps, progressing the workflow. The workflow is persisted to the DB at each step, so even if the website goes down the current step of the user in the workflow is maintained. The workflow, rather than the controller, decides which step the website should display, separating the process logic from the controller logic.

There is very little code involved, just small service/host classes used to access the workflow and integrate with the website. To add persistence it only really took two lines of code in the host class (setting the workflow application instance store and telling it to persist when idle at the bookmarks), compared to the tutorial sample host. The workflow used could be much more complicated, branching to different steps depending on user response (e.g. redirecting to a different address screen if the user isn’t based in the UK) without adding complexity to the controller logic. As the workflow is separated from the website it can be tested independently (no website needed! automated testing!) and updated without large changes needed in the website. It’s also a very light weight (and cheap) way to add workflow, which is something we may need going forward. I’m currently looking at ways now to use WF with persistence in Azure cloud (without the upcoming Azure AppFabric) for simple workflow solutions, as right now we can’t use our current workflow applications like K2.

Attached sample inside word doc as Posterous won’t accept zips.


SQL Server 2008 Row Insert and Update timestamps

[Originally posted by] :

I need to add two columns to a database table in SQL Server 2008 R2:

  • createTS – date and time when row is inserted
  • updateTS – date and time when row is updated

I have a few questions:

  1. What column data type should I employ for each of these?
  2. createTS needs to be set only once, when the row is inserted. When I tried the datetime type for this column and added a Default Value or Binding of getdate(), the column value was appropriately set. Is this the best way to fulfill the purpose of this column? I considered timestamp data type, but that is, in my opinion, nearly a misnomer!
  3. updateTS needs to be set to the date and time of the moment when the row is updated. In SQL Server, there is no ON UPDATE CURRENT_TIMESTAMP (as in MySQL), so it looks like I have to resort to using a trigger. Is this right and how would I go about doing that?

So there is a starting point for anyone who would like to answer this question, here is the create table script:

CREATE TABLE [dbo].[names]
    [name] [nvarchar](64) NOT NULL,
    [createTS] [datetime] NOT NULL CONSTRAINT [DF_names_createTS]  DEFAULT (getdate()),
    [updateTS] [datetime] NOT NULL,
        [name] ASC



CREATE TABLE [dbo].[Names]
    [Name] [nvarchar](64) NOT NULL,
    [UpdateTS] [smalldatetime] NOT NULL


PS I think a smalldatetime is good enough. You may decide differently.

Can you not do this at the “moment of impact” ?

In Sql Server, this is common:

Update dbo.MyTable 

ColA = @SomeValue , 

Sql Server has a “timestamp” datatype.

But it may not be what you think.

Here is a reference:

Here is a little RowVersion (timestamp) example:

CREATE TABLE [dbo].[Names]
    [Name] [nvarchar](64) NOT NULL,
    RowVers rowversion ,
    [UpdateTS] [datetime] NOT NULL


INSERT INTO dbo.Names (Name,UpdateTS)

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]

Update dbo.Names Set Name = Name

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]
Maybe a complete working example:

DROP TABLE [dbo].[Names]

CREATE TABLE [dbo].[Names]
    [Name] [nvarchar](64) NOT NULL,
    RowVers rowversion ,
    [UpdateTS] [datetime] NOT NULL



CREATE TRIGGER dbo.trgKeepUpdateDateInSync_ByeByeBye ON dbo.Names


Update dbo.Names Set UpdateTS = CURRENT_TIMESTAMP from dbo.Names myAlias , inserted triggerInsertedTable where 
triggerInsertedTable.Name = myAlias.Name



INSERT INTO dbo.Names (Name,UpdateTS)

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]

Update dbo.Names Set Name = Name , UpdateTS = '03/03/2003' /* notice that even though I set it to 2003, the trigger takes over */

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]
Matching on the "Name" value is probably not wise.

Try this more mainstream example with a SurrogateKey

DROP TABLE [dbo].[Names]

CREATE TABLE [dbo].[Names]
    SurrogateKey int not null Primary Key Identity (1001,1),
    [Name] [nvarchar](64) NOT NULL,
    RowVers rowversion ,
    [UpdateTS] [datetime] NOT NULL



CREATE TRIGGER dbo.trgKeepUpdateDateInSync_ByeByeBye ON dbo.Names


   UPDATE dbo.Names
    From  dbo.Names myAlias
    WHERE exists ( select null from inserted triggerInsertedTable where myAlias.SurrogateKey = triggerInsertedTable.SurrogateKey)



INSERT INTO dbo.Names (Name,UpdateTS)

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]

Update dbo.Names Set Name = Name , UpdateTS = '03/03/2003' /* notice that even though I set it to 2003, the trigger takes over */

select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]

SSIS and Stored Procedure OUTPUT Parameters

[Originally posted by] :

SSIS and Stored Procedure OUTPUT Parameters

I was recently asked to help configure an execute SQL task that would call a stored procedure and capture an output parameter in an SSIS variable.  Although I had done this before and it seems straight forward it took me quite some time, as well as opening up most every package I have ever created to find an example, to complete this.  Hopefully this post will save others time, as well as myself.

To demonstrate this first create a stored procedure in the AdventureWorks2012 database that uses an OUTPUT parameter:


USE AdventureWorks2012;

CREATE PROC ssisoutput
SELECT @count = COUNT(*)
FROM Person.Person;


This is obviously a simplified example so to provide a better context let me describe the task I was confronted with.  The stored procedure that was called was running several transactions, inserts and updates, and the output parameters were recording the number of records that were affected.  The output parameters were then captured in SSIS parameters that were used to dynamically generate a send mail message that notified all operators of the number of records.


Create a package called OutPutParameters.  Within the package create a variable called countout with a data type of Int16 and a package level scope


From within an SSIS package drag and drop an Execute SQL task onto the control flow design pane.  In the Execute SQL task configure the connection to use the instance where the AdventureWorks2012 database containing the stored proc resides:

imageLeave the SQLSourceType set to DirectInput and in the SQLStatement type the following query:

DECLARE @countout INT
EXEC ssisoutput @count = @countout OUTPUT
SELECT ? = @countout


The above T-SQL statement first declares a variable, @countout, that will be used to hold the output parameter value.  The stored procedure is executed and the output parameter value, @count, is passed to the variable @countout specifying OUTPUT.

With the connection and statement being configured now configure the Parameter Mappings of the task mapping the countout variable to the output parameter:


imageNotice that the “?” in the SQLStatement acts a a placeholder for the parameter.  The Parameter Name uses the 0 based indexed value of the parameter placeholder.  Since there is only one one parameter placeholder in the query, “?”, the value is set to 0.


This method is almost identical except that the SQLStatement does not declare a variable, but rather directly assigns the output parameter to the parameter placeholder.  The ONLY difference is the SQLStatement:

EXEC ssisoutput @count = ? OUTPUT

imageThis is obviously a bit more efficient as there is no need for variable declaration and assignment.


The question often comes up in regards to being able to call a parameter by name rather than indexed ordinal position as it appears in the SQLStatement property.  Unfortunately this is only possible using an ADO.NET connection manager.

Drag and drop another execute SQL task onto the control flow design pane and configure it to use a new ADO.NET connection manager.

imageIn the SQLStatement property put in ONLY the stored procedure name ssisoutput, DO NOT INCLUDE EXEC, and configure the IsQueryStoreProcedure property to True.


In the Parameter Mappings window map the countout variable to the output parameter and use the Parameter Name @count, the name that is defined in the stored procedure.


Additional information on parameters in Execute SQL tasks can be found on Technet.  The sample package reference in this post can be downloaded here.

How to delete Thumbs.db (it is being used by another process)

[Originally posted by] :

I have a simple console application that I’m trying to delete a folder:


I got the exception of the annoying Thumbs.db

The process cannot access the file 'Thumbs.db' because it is being used by another process.

I can’t change the registry to avoid Thumbnail to process in folder

What are my options here to be able to delete the folder with everything in it?



You can find out which process is blocking it with Unlocker. If you can’t kill that process you can mark this file or folder to be deleted right after the next boot with MoveFileEx.

public static extern bool MoveFileEx(string lpExistingFileName, string lpNewFileName, int dwFlags);

public const int MOVEFILE_DELAY_UNTIL_REBOOT = 0x4;

MoveFileEx(fileName, null, MOVEFILE_DELAY_UNTIL_REBOOT);

If you want to disable the creation of “Thumbs.db”-Files completely you can switch it off in the registry. Open the registry editor, navigate to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer and set the value of NoThumbnailCache to 1. If this entry doesnt exist you simply can create it (DWORD 32).

For Win7 Professional / Ultimate the path to the entry is HKEY_CURRENT_USER\Software\Microsoft\ Windows\CurrentVersion\Explorer\Advanced and it’s name is DisableThumbnailCache.