Monthly Archives: February 2017

How do I: Create An Advanced SQL Database Backup Monitor In Visual Studio?

My favorite part of my job is getting to work with customers and understand exactly how they use, and also need a product to work. Some of the time this just means listening closely, answering questions, and relaying information back to the product group. But often there are those tiny changes and use cases that fall outside the realm of things the product group is likely to address.

A change that might be hugely valuable for Customer A, won’t happen if it breaks backwards compatibility for Customers B-Z.

One of my customers has a SCOM environment for SQL that monitors over 20,000 databases. Due to their size there are often cases where the out-of-box monitoring provided by the SQL Product Groups management packs isn’t able to completely meet their needs. While the pack provides fantastic monitoring for SQL, there are times where they need a more granular view of the health of their environment.

An instance of this from the past year was monitoring SQL Database backups. The SQL Product Group’s pack gives you the ability to alert if a database hasn’t been backed up in a certain number of days. By default it is set to 7 days, but you can override that to any integer value of days.

For my customer this wasn’t really good enough. They wanted to be able to drill down and alert on hours since last backup. They also wanted multiple severities so if it had been 20 hours since a backup, an e-mail could go out, but at 30 hours we would generate a page. The 20 & 30 hours would be customizable at the individual database level, and they also wanted some added logic that would check database backups for databases that had a status of “ONLINE”. We have other monitors that look at DB Status in general so in this case if a database was OFFLINE they either knew about it from the other monitors and were fixing it, or it was intentional in which case they didn’t want a backup alert.

The basic logic behind the SQL PG’s MP is a simple T-SQL query wrapped in a fairly complex vbscript. The unwrapped T-SQL is below:

The T-SQL modifications we need to make are relatively simple swap DAY to HOUR, and add in a line to only return database backup info for databases with a status of ONLINE.

To get this into a working Management Pack is a little bit more complex and requires isolating and cloning the Product Groups Database Backup Monitor in Visual Studio, and then making a few changes to the XML for our custom iteration.  To prevent screenshot overload I did a quick step-by-step walkthrough of the process. For this video I opted to leave out three-state severity request, and will show how to add that functionality in a follow up video.

If you have any questions or need any help, just leave a comment.

Tagged , , , , , ,

Better Practices: Orchestrator Automation/ Sometimes that blog post that seems too good to be true…

This will be a quick post. I kept seeing this pop up on Automation/PowerShell MVP blogs and as answers on TechNet over the past few years.  System Center Orchestrator 2012-2016 is 32-bit application that leverages .NET such that the PowerShell .NET Script Activity natively executes scripts in PowerShell Version 2. At some point someone started suggesting that you could modify a regkey and instantly get Orchestrator to use the latest version of PowerShell without any complicated workarounds:

HKLM\SOFTWARE\Wow6432Node\Microsoft\.NETFramework

Reg_DWORD: OnlyUseLatestCLR

Value: 1

Being that I am generally mistrustful of things that seem to be too good to be true  I have avoided this solution, and have used alternate slightly more involved workarounds to mitigate this issue. Recently a colleague asked on behalf of a customer if there was any reason they shouldn’t implement the regkey fix. After some quick searching the answer would be a resounding yea this could be a really bad/unsupported idea.

The regkey exists for .NET compatibility testing and debugging. It is not intended to ever be set in a production environment. While it does work insofar as it forces Orchestrator to use the latest version of PowerShell it also forces every single other 32-bit application on that server to use the latest version of .NET CLR. If one of those applications only supports some earlier version of .NET you will break it, and not necessarily in an obvious way. Turning that setting on in production is a ticking time bomb. It’s possible as more and more applications have shifted to 64-bit that the number of applications affected by this setting will continue to decrease and those who have implemented this change might have gotten lucky and haven’t seen obvious issues, but I would recommend against ever playing with this setting in a production environment.

Some examples of this setting unexpectedly breaking things:

https://blogs.msdn.microsoft.com/selvar/2012/07/14/reporting-services-unexpectedly-loads-net-framework-4-0-by-default-and-fails-with-http-500-while-browsing-report-server-and-report-manager-url/

https://support.microsoft.com/en-us/help/2616444/-onlyuselatestclr-breaks-exchange-on-sbs-2011-standard

Sidenote/Tangent: C Programming & Key Loggers

One of my side projects over the past few months has been to teach myself the C programming language. It has been going pretty well, but it’s easy to get bored with the typical practice problems.To combat this I gave myself a slightly more real-world practice problem. I decided to write a Key Logger in native C using only the Win32 API. There are a few good examples online of the mechanics of how to do this, but what I quickly found after getting a working prototype up and running was that Windows Defender is actually pretty good in some cases at detecting/tagging key loggers as Trojans. I tried different example code from online, and in each case Windows Defender instantly caught it upon execution.

So the next challenge was how to write a Key logger in native C with the Win32 API which Windows Defender could not detect.

After a few modifications I thought I had it figured out, but what I came to realize is that Defender is built such that it can tell if you are repeatedly trying to vet code past it and it will temporarily stop intercepting it so you can’t tell if your modifications are working.

It is actually a fairly interesting problem in that Defender has to be able to recognize certain patterns common to Key Loggers, but at the same time it has to be able ignore programs that function like Notepad/Word/Games which have a completely legitimate reason for listening and responding instantly to keystrokes. This balance is where the room for vulnerability lies. It would be easy to alert anytime a new program was listening for keystrokes via the Win32 API’s, but you would have so many non-actionable events it would be pointless. So it becomes a game of whack-a-mole where the patterns for detecting key loggers need to be  permissive enough not to piss off users trying to run non-malicious code, but restrictive enough to hopefully catch/deter a less than determined adversary.

I have no insider knowledge into how Defender works (nor would I share any if I did), but after figuring out how to force Defender to try to quarantine my program every time it ran, I could slowly comment out aspects of my Key Logger code until I zeroed in on the characteristics it was using to make its diagnosis. Once I had that, the problem was solved.

It took a few hours, but I now have a basic functional Key Logger that doesn’t need to run as administrator and is completely invisible to Windows Defender. I also learned more about C and the Win32 API in the past few hours than from three weeks worth of reading.  As much as possible I am going to try to add in programming assignments like this one that have a bit more real-world application as they are able to maintain my interest better than writing Celsius to Fahrenheit temperature converters and the other example problems that are universal to almost every learn programming language X book.

I had debated not publishing the code, but a routine Bing/Google search will reveal plenty of similar alternate examples, and with C source code for programs like mimikatz readily available this would fall into the tamer categories of code available for aspiring white hats who want to learn more about programming and security.

The code is provided without warranty of any kind and is for educational purposes only, and in this case I am only including screenshots without the details of the work to vet the code against an antivirus application. If you want to create a working copy you have to type it out yourself which is in the end the best way to learn.

To start off launch Visual Studio.  (For my example I am using Visual Studio 2013)

File New Project (Ctrl — Shift — N)

Select Visual C++ (Win32 Console Application) -Even though the code we are writing is almost entirely C the C++ compiler is backwards compatible with any of our C code.

Select Empty project

Right-Click Source File — Add — New Item (Ctrl-Shift-A)

Give you .cpp files a name click Add

The code itself is pretty simple. The ShowWindow() function controls the visibility of the command prompt window.

While loop continues indefinitely until the End Key is pressed or the Console Window/associated process is closed/stopped. For loop cycles between keys 8-255.

GetAsyncKeyState() function let’s us know if the key we are cycling through in the for loop is down. http://www.cplusplus.com/forum/general/141404/ (Nice explanation of why to use & 0x0001)

Use if statements to determine what keys get written directly to file. Shift letters A-Z by 32 making all letters lowercase via ASCII character set. http://www.asciitable.com/

To map other keys you need to use win32 API Virtual-Key Codes:

https://msdn.microsoft.com/en-us/library/windows/desktop/dd375731(v=vs.85).aspx

If you have any suggestions on good C Programming resources let me know. I have exhausted K&R and K.N.King’s C Programming a Modern approach and am currently working my way through Robert Sedgewick’s Algorithms in C, but I am always happy to get new reading suggestions.

Tagged , ,