Windows 2008 R2 + SQL 2008 R2 + Password Policy = Security Event Log Out of Control

I was asked to troubleshoot an interesting problem today where the Windows Security event log was being "flooded" by one particular sort of event.  By flooded, I mean about 20 duplicate entries logged per second.  The biggest problem with this is that it was making the Security log on that server useless, as the log would fill up within 45 minutes at that rate.  Click to enlarge the screenshot below:

event log ss*Names were changed to protect the guilty*

 The operating system is Windows 2008 R2. The server is a domain member. The server also runs SQL Server 2008 R2.  The server is a cluster node in a failover cluster.  I started Googling and Binging the event ID and description, and I didn't get much at first.

As an aside, I can't believe I just used the word "Binging," and I much prefer Google for almost everything, but if you want to search Technet, MSDN, and other Microsoft sites, the built-in Bing search on those sites actually does tend to produce better results on those sites than a general Google search. For me anyway. Maybe a "site:technet.com xyz" search on Google would do just as well. Anyway... onward:

So the only solid clue I found in my searching was this Technet article. The Windows Password Policy Checking API was being called at a staggering rate, but why?  By whom?  How do I make it stop?  The same SQL service account was being named in the events, so it obviously must have something to do with SQL.  Well, I could turn off the auditing of "Other Account Management Events," either by way of domain GPO or local security policy on that server... but that would only suppress the logging of that behavior.  It does nothing to stop the actual behavior.  Plus I would also lose any other events of that same category on the system.

I also knew that there was an "Enforce Password Policy" option that can be configured on each SQL account.  So I fired up SQL Management Studio on that server and did some testing, and as it turns out, that option was enabled on several accounts, including service accounts that are designed to hit the database rapidly.  It appears that every time an authentication attempt is made by one of those SQL accounts that has that Enforce Policy option checked, the SQL service makes a call to that Windows API to do some password policy checking, and that event is logged.

I tactically identified a few key service accounts that I knew to be very active on that database, and I disabled the "Enforce Password Policy" option on those accounts one by one.  I confirmed that with each account I changed, the rate at which those Security event 4793's were coming in decreased. Until finally, they stopped completely.

 

That's all for today.  On one final note, I wish we did cool things like this in the United States, especially as a resident of a state that cuts science funding.

From the "No Not All My Stuff Is Patched Yet" Dept.

This is a public service announcement.

If you have a Windows 2008 R2 server without SP1 or without this hotfix, and you also have a monitoring application or script that uses WMI to query for service information from the Win32_Service WMI class on a regular basis, you might just get burned really badly by a memory leak that will have the WMI service gobbling up >500MB of memory until it crashes.  You might then have to roll back hours of work and hope the Microsoft bug didn't cause any cluster failovers or dropped Exchange mailbox stores across a couple dozen servers.

Transitioning From VB Script to Powershell

VB Script is still around and will be for quite a while yet.  But current Windows technology is all about Powershell.  As well it should be, as PS is vastly superior in many, many ways.

However, a lot of us still have old VB scripts hanging around, probably doing production work... and what I’m about to show you may be the trickiest part of porting those old scripts over into Powershell. 

As you probably know, Powershell fully harnesses the power and flexibility of .NET, while VB Script was only capable of working with COM objects.  Almost everything that can be done with COM objects can be done faster and easier with .NET.  (For the foreseeable future at least - I hear COM is making a bit of a comeback in Windows 8...)  However, Powershell is still fully capable of working with COM objects too.  What that means is that those of you who are still more comfortable with VB script or have a lot of script to port over in a hurry, well, you don’t have to worry about finding .NET equivalents for those COM objects. (Even if there might be a better, more Powershell-native way of doing it.) 

Let’s take Microsoft Cluster Services for example.  Here’s what you would see in a VB script that deals with cluster resources: 

 

Set oCluster = CreateObject("MSCluster.Cluster")
oCluster.Open("")

 

 In Powershell it’d be something like this: 

 

$cluster = New-Object –COMObject MSCluster.Cluster
$cluster.Open("")

 

 Now  you have your cluster object.  Want to see what all members it has?  (The properties of it + its methods/what all it can do?) 

 

$cluster | Get-Member

 

 Alright well I see that $cluster is basically an object collection that has, among other things, a ResourceGroups object in it, so let’s open that up: 

 

$ResourceGroups = $cluster.ResourceGroups

 

 And then do a $ResourceGroups | Get-Member to see what we can do with that: 

 

PS C:\Users\ryan> $resourceGroups | Get-Member
 

   TypeName: System.__ComObject#{f2e60706-2631-11d1-89f1-00a0c90d061e}
 

Name                MemberType Definition
----                ---------- ----------
Delete              Method     void Delete ()
Move                Method     Variant Move (Variant, Variant)
Offline             Method     Variant Offline (Variant)
Online              Method     Variant Online (Variant, Variant)
Cluster             Property   ISCluster Cluster () {get}
CommonProperties    Property   ISClusProperties CommonProperties () {get}
CommonROProperties  Property   ISClusProperties CommonROProperties () {get}
Handle              Property   ULONG_PTR Handle () {get}
Name                Property   string Name () {get} {set}
OwnerNode           Property   ISClusNode OwnerNode () {get}
PreferredOwnerNodes Property   ISClusResGroupPreferredOwnerNodes PreferredOwnerNodes () {get}
PrivateProperties   Property   ISClusProperties PrivateProperties () {get}
PrivateROProperties Property   ISClusProperties PrivateROProperties () {get}
Resources           Property   ISClusResGroupResources Resources () {get}
State               Property   CLUSTER_GROUP_STATE State () {get}

So hopefully this is starting to pique your interest.  With this sort of information you could easily script out whether all the cluster resource groups were on the correct nodes, and even move them if need be.  Pretty neat stuff.

I leave you with this - don't you hate it when this happens?

F'ed up log

2012 Scripting Games Post-Game

So I finished up my participation in the 2012 Scripting Games Advanced category a few days ago. They haven't finished all the grading yet, but all the events have been completed. (10 total scripts in 10 business days.) Here are few of my takeaways:

  • It was 100% Powershell, so it really should have been called the Powershell Games, but I realize Ed's blog used to have a lot of VB Script on it too before PS really came into the spotlight, so I guess the name is sort of legacy. His blog is not known as "Hey, Powershell Guy!" after all. Besides, I don't know of anyone else holding a similar event, so I guess he gets to use whatever name he wants.
  • I don't think there's any chance of me winning first place in the Advanced category, but I should (hopefully) finish in the top 10. Which, I guess isn't all that bad considering how many participants there were from all over the world. Leaderboards should be viewable here, but like I said the grading is not finished yet and so the leaderboards are still going to be changing.
  • The Games were reasonably challenging, and I did learn a few new tricks and best practices along the way. For instance, creating my own custom objects, and adding those to a collection of objects, has become much more natural for me. I will probably post all of the scripts I wrote and some commentary about them in a later post - I want to make sure the deadlines for the Games are completely passed before I do that.
  • Even though several days were given to complete each event, I turned in my submission for each event on the same day it was released. I have a pretty single-track mind when it comes to things like finishing code. It's often all I can think about or concentrate on until I finish, especially if there's any sort of deadline involved. Not only that, but I have other things like a job which also demand my time and energy -- unlike those damn Germans with their 6 days off for Easter holiday and 2 months a year of vacation. (Just teasing, Germany.)
  • I felt like a couple of the scenarios were not very well-defined. One could start scripting for the scenario given, but then several hours later go back and see several confused reader's posts, asking for Ed to clarify a certain piece of the scenario, and then after reading Ed's responses, do something differently in your own script. Even worse, I saw some inconsistency in the way different judges judged people's scripts. For instance, Ed posted the official rules and grading criteria before the games began. One of those grading criteria was "avoid using aliases." I think that's perfectly reasonable, as aliases are good for quick, interactive commands, but when writing a long, complex script, aliases often make it even harder for someone else to follow. (Aliases are things like "?" instead of "Where-Object" or "gci" instead of "Get-ChildItem.") But, browsing the judge's comments of other people's scripts, I would see a judge commenting on the participant's "excellent use of aliases!" So in that regard I don't feel like all the judges were on the same page, which is unfortunate, because it seems like only 1, and maybe sometimes 2, of the ~35 total judges ever grade any one script, so depending on exactly which judge you get will significantly impact your score.
  • I don't like a judge giving me a score on my script, but not leaving any comments at all. (Especially if it's a crappy score like 3/5.) That said, I understand that the judges are all just volunteers that have their own lives, and there are hundreds of participants, so the judges are overworked and probably in a hurry.

So all in all, even if my comments above sound negative, I'm really meaning them to be constructive. I did enjoy the 2012 Scripting Games and I'm really happy that Ed put forth the time and effort (which I know must have been substantial) to organize them!

C# applications with "install" or "setup" in the name and Application Compatibility

Hello ladies and gents.

I've been busy the past few days creating a very complex (for me) .NET application in C#. I want to talk about an issue that I banged my head on for a few hours, because the solution is out there and easy to find on the internet, but every person who posted the answer on a forum or MSDN article was making an assumption that I knew something already that I didn't know in order to arrive at the solution, and so that missing piece of knowledge was keeping me from joining the ranks of those who knew how to solve this problem. What can I say, I'm a novice programmer.

I'm using Visual Studio 2010, and I have created a C# project. I'm using Windows 7 64bit. This project that I have created has the word "install" in the name. Apparently, any time that Windows Vista or 7 see the user executing a program that has the word "setup" or "install" in it, it triggers something deep within the bowels of Windows to cough up this incisive message:

appcompat*Thank you that's very helpful now go away*

I don't like the principle behind this behavior. I have read that this box will only pop up if you have the magic word in your executable name and that executable does not add an entry to the Add/Remove Programs list by the time it exits, which could indicate a failed installation. Or it could indicate nothing went wrong at all, which from my experience is the case 100% of the time. In fact I'd be scared to hit that "Reinstall using recommended settings" button, because I definitely have no idea what that might do. Furthermore I've read that some install applications do put a new entry in the Add/Remove Programs list and yet it still generates this popup.

I've been using Windows Vista and Windows 7 for a long time, and I have never once found this to be helpful in any way. But now this behavior has really gone and annoyed me by popping up every time I test during my development.

So I started researching the problem and I was immediately led to believe that this could be easily solved by editing the manifest file in my application's project in Visual Studio. Here is what you add to your manifest file to make it not show that Program Compatibility popup:

<compatibility xmlns="urn:schemas-microsoft-com:compatibility.v1">
   <application>
      <!--The ID below indicates application support for Windows Vista -->
      <supportedOS Id="{e2011457-1546-43c5-a5fe-008deee3d3f0}"/>
      <!--The ID below indicates application support for Windows 7 -->
      <supportedOS Id="{35138b9a-5d96-4fbd-8e2d-a2440225f93a}"/>
   </application>
</compatibility>

OK... I can't find my manifest file. I've looked through all the menus and tabs and buttons here in Visual Studio... hrm... maybe I just need to edit this installer.manifest file that's sitting in the same output directory as my application and then rebuild it! Nope, that didn't work. It just seems to wipe out my changes and revert the file to default every time I build the solution... my project settings were set to "Embed manifest with default settings."  So even though the default manifest was there in the output directory, any modifications you make to it just get wiped out.

Finally, I realized that I just need to generate a new manifest file for myself. In the Solution Explorer pane, right-click your project and choose "Add --> New Item..." and add an "Application Manifest File." It should pop up under the Resources folder in your project, whether you already had one or not. Now you can edit that manifest to your heart's content, and the changes will not get wiped out. You'll notice that the section of XML I pasted above sort of already exists in your new manifest file, so just use your head and paste in only the couple of relevant lines in the right places. ;)  You will also notice that if you go back to your Project's properties, you should see it pointing at the manifest file you just created, instead of saying "Embed manifest with default settings."

No more "Program Compatibility Assistant" popup!  I feel like an idiot sometimes...

Now  that you know about creating and editing your own application manifest file, you are well on your way to doing other things with it, such as having the application request its own administrator access from UAC, instead of, say, simply checking to see whether the current user is an administrator or not, and then having the application say "Sorry, you're not an administrator so I won't run.  Please try executing the program again by right-clicking on my icon and choosing 'Run as administrator...'" Which unfortunately I have seen too many times. Follow up with this article.