Tips For Managing Microsoft SQL 2012 Always On Availability Groups

Recently I have been spending a significant amount of time working with high availability and disaster recovery solutions. A large amount of this work has been around the deployment and use of SQL 2012 AlwaysOn Availability Groups.

Availability Groups should not be confused with SQL Clusters, whilst both technologies make use of Windows Failover Clustering; they achieve their goals in some very different ways.

I am not going to go into specifics around the design and deployment of an AlwaysOn Availability Group solution; however, I have some design tips that should help you suffer any serious setbacks.

Firstly, confirm vendor application support. This might seem obvious but it is extremely important. There are a number of applications that do not support AlwaysOn AGs, the list includes Microsoft SCCM 2012R2 (and other products in the System Centre Family) as well as some products from Citrix (patches on their way). If you are running any of the products that do not support these features, then you will need to revert back to a normal Failover Cluster (or in my case, a geo-cluster).

Secondly, find a reliable place for a File Share based Witness, preferably a second side. This tip is of particular importance if you are running across multiple sites. You should be also aware that the server hosting the share must to be in the same domain as the SQL servers.

Next, check the implications of using AlwaysOn AGs on your Microsoft Licensing. All of the new features come at a cost; you need ensure that you do not affect your overall solution costs. Remember, with AlwaysOn AGs, SQL is actually running on all of your nodes, all the time and this could affect you license requirements.

One annoying thing, you cannot easily create our listeners if you do not have any databases. My tip to bypass this is to simply create an empty database, and then create the AlwaysOn AG and its associated listener based on that database. Once you have that done, then your applications installers can be run against the listener. REMEMBER though, that if an application creates databases via the listener name, they are not configured as part of the AlwaysOn Group, you will need to go and do that manually! I was caught with this one.

I had some issues when creating AlwaysOn AG and the associated listener. These two articles really helped me out:


Sending SYSLOG messages from PowerShell

I had this need to send some SYSLOG messages from PowerShell, and there are many reasons why you might want to do this one, from notifications to logging, SYSLOG can be very handy.

I looked around online and could not find a really simple and easy to use piece of code. There were some examples out there, but they were all a little rough around the edges, and I knew I could clean them up and improve upon their design.

Before we get to the code, let us take a quick look at the SYSLOG protocol. According to Wikipedia, the SYSLOG protocol was originally developed in the 1980s by Eric Allman as part of Sendmail and is now standardized by IETF in RFC5424.

A standard SYSLOG message consists of four things:

  1. A priority - how bad is it?
  2. A Timestamp - when is this occurring
  3. A hostname  - who is sending the message
  4. A Message  - kind of obvious

    One thing to note is that the priority is not that simple. The priority in the message is actually made of two things: the severity and the facility, or what the application or subsystem generating the message is. The levels are defined in the tables below:

    Facility Number Keyword Facility Description
    0 kern kernel messages
    1 user user-level messages
    2 mail mail system
    3 daemon system daemons
    4 auth security/authorization messages
    5 syslog messages generated internally by syslogd
    6 lpr line printer subsystem
    7 news network news subsystem
    8 uucp UUCP subsystem
    clock daemon
    10 authpriv security/authorization messages
    11 ftp FTP daemon
    12 - NTP subsystem
    13 - log audit
    14 - log alert
    15 cron clock daemon
    16 local0 local use 0 (local0)
    17 local1 local use 1 (local1)
    18 local2 local use 2 (local2)
    19 local3 local use 3 (local3)
    20 local4 local use 4 (local4)
    21 local5 local use 5 (local5)
    22 local6 local use 6 (local6)
    23 local7 local use 7 (local7)



    Code Severity Keyword Description General Description
    0 Emergency emerg (panic) System is unusable. A "panic" condition usually affecting multiple apps/servers/sites. At this level it would usually notify all tech staff on call.
    1 Alert alert Action must be taken immediately. Should be corrected immediately, therefore notify staff who can fix the problem. An example would be the loss of a primary ISP connection.
    2 Critical crit Critical conditions. Should be corrected immediately, but indicates failure in a secondary system, an example is a loss of a backup ISP connection.
    3 Error err (error) Error conditions. Non-urgent failures, these should be relayed to developers or admins; each item must be resolved within a given time.
    4 Warning warning (warn) Warning conditions. Warning messages, not an error, but indication that an error will occur if action is not taken, e.g. file system 85% full - each item must be resolved within a given time.
    5 Notice notice Normal but significant condition. Events that are unusual but not error conditions - might be summarized in an email to developers or admins to spot potential problems - no immediate action required.
    6 Informational info Informational messages. Normal operational messages - may be harvested for reporting, measuring throughput, etc. - no action required.
    7 Debug debug Debug-level messages. Info useful to developers for debugging the application, not useful during operations.


    There are 7 severity levels, and 24 facilities, however we only send a single number, the priority, and this is simply calculated as – (Facility Number * 8) + Severity Number.

    Here are a few examples:

    1. Emergency Message from Kernel = (0 * 8) + 0 = 0
    2. Alert from User = (1 * 8) + 1 = 9
    3. Informational from mail = (2 * 8) + 6 = 22

    When you look at a SYSLOG servers output, it probably has the severity levels and facilities nicely printed, all it is doing is reversing the process. For example, if we received a priority of 58, we would firstly divide 58 by 8, this comes out at 7.25, if we just take the whole number, we can see that this was a message from the NEWS facility, if we take 7*8 from 58, we get 2, and this, we know this was a critical severity message.

    As you can see, the whole thing is rather easy. The SYSLOG protocol is brilliant in its simplicity.

    So, back to PowerShell.

    If we were going to write a PowerShell CMDLet to send a SYSLOG message, what would it look like?

    Let us start with parameters. We are going to need to know the SYSLOG server we want to send the message to, we need a message to send, and we need to define the severity and facility that is sending the message. Optionally we might want to specify the hostname of the machine sending the message (we can get this if not specified), we might want to specify a timestamp (but we could get lazy) and finally our SYSLOG server might be running the service on a different port, so we should be able to specify the port if it is different from the default, UDP514.

    How do we go about specifying the severity and facility levels? We don’t want to force users to remember 0 through to 7 and 0 through to 23? We need to make this easier! How about some sort of data type? Thankfully, we can use ENUM types within PowerShell to define some simple data types and simplify specifying these parameters. If you don’t know about ENUMs, I suggest you do some Googling, they are very handy and quite useful.

    What would the ENUM definitions look like?

    For those of you who don't know, each item in the ENUM is assigned a number, starting with 0. We will use these numbers as part of our calculation of the priority.

    So once we have these data types defined, what's next? Let's take a look at parameters. Parameter's for a funciton are pretty straight forward, destination server, message, severity, facility, hostname, and date/time stamp are all we really need. In terms of mandatory parameters, only the first 4 are, we can determine the other two for the user.

    What's next? Well, what about determining the priority to be sent based upon severity and facility?

    The only other tricky part is the date/timestamp, but once again, that isn't too tricky. We just use Get-Date and a custom specified format.

    Finally let's stick it all together:

    Well we now have a message to send to the syslog server? Well all that is left to do is encode and send the message using a UDP client object.

    The finished CMDLet looks like this:

    You can also see the finished product on my GitHub. I built a module based upon my prefered structure here.

    As you can see, this is all pretty simple stuff. Now we get to go off and user it in your scripts! Using this CMDLet is pretty simple, there is an example included in the comments. Simply specify the various details, and then check your SYSLOG server to see the results.

    Stay tuned into my blog for a non-PowerShell post about some issues I recently faced with Windows Integrated Authentication.


    Assessing the impact of Supermicro BMC vulnerability with PowerShell

    So there has been quite a bit of news around a vulnerability in some of Supermicro's Baseboard Management Controllers (BMC) which allows an attack to remotely retrieve the admin credentials for the BMC system remotely (and in plain text). You can find the original write up by CARI.Net, as well as SANS and Arstechnica.

    Most recommendations that have been provided about assessing if you have vulnerable systems have involved netcat (nc), which has to be one of the best tools in a sysadmins utility belt, however, speaking from experience, it has the drawback that some anti-virus products don't like it, which results in security people complaining. There is another way, and that is to use PowerShell.

    It is pretty simple to use the Invoke-WebRequest CMDLet, specifying a the URI you want to test. The URL in this case will be HTTP://<your server>:49152/PSBlock. If we can't connect, or receive a 404 file not found, then we are probably ok, if we receive content back, then we need to take a closer look!

    So what does our PowerShell expression look like?

    If you get content returned, then you really do need to investigate further. Hopefully this is a good example of also how to use Invoke-WebRequest.


    Creating GUIDs with PowerShell

    This will be a really short ones folks, but this is something I have found incredibly useful over the last few days.

    Ever needed to create a GUID from within PowerShell? Well it turns out to be incredibly easy, we just leverage the .Net framework.

    To create a GUID, simply use "[GUID]::NewGuid().GUID" and that will return the GUID as a string.

    I told you this would be short and simple.


    Start-CHARGENAmpAttack – Looking at CHARGEN protocol denial of service attacks

    I am going to start by saying that this post is pretty much all Patrick Gray's (, @riskybusiness). Patrick recently did a short interview with Marc Eisenbarth as part of the AusCert coverage for the Risky Business podcast, where they were discussing amplification attacks like those seen using DNS and NTP, but also looking at the future where things like SNMP or even the CHARGEN protocol might come into play. Now I happened to be listening on a train that was slowly going nowhere, and thought to myself, how hard would it be to write a CHARGEN DDOS tool that makes use of these amplification techniques? Could I manage to write one?

    So last week, whilst managing work, on call, and a dead PSU in my main machine at home, I started looking at how easy it would be.  All in all, it took about 3 days from inception to actually having a primitive tool which could be used. I can’t take all of the credit for this however, I had help from Google and some rather good pieces of code from (that is one long domain name). All I did was add some easy to use PowerShell code using jobs to provide parallelism.

    So before we get too far along. Let’s talk about CHARGEN.

    What is CHARGEN?

    CHARGEN, or the Character Generator Protocol, is a network service defined in RFC864, from way back in 1983 (which makes it older than me). It was designed for testing, debugging and measuring networks and applications. The idea is simple, connect to the port and get a “random” amount of “random” characters back. You can connect on TCP or UDP on port 19, with none/some data, and it will send the “random” data back to you.

    The specification contains a significant number of security issues which as you will see shortly, will lead to its misuse. This isn’t a protocol which is in wide use today, but oddly enough, you will find it in some of the most peculiar places (home routers, printers, etc) and this is why we, as IT and security professionals, need to be aware of its capabilities.

    To enable CHARGEN services in Windows, simply install the “Simple TCP/IP Services”. In Linux, enable via INETD.

    How do we abuse CHARGEN?

    The most typical abuse for a protocol like CHARGEN is by its involvement to amplify DDOS via the spoofing of source IP addresses in UDP packets. The scenario is pretty much the same whether the protocol to be abused is DNS, NTP, SNMP or CHARGEN. The attacker sends a UDP packet to the server running the protocol, however the source IP address has been specified incorrectly, in other words, spoofed, to look like a different address. In this case, the attacker will specify an IP address of a server or service they which to DDOS. When the server receives this UDP packet, it will send the response back to the source address. This response will be sent to the victim machine, the one whose IP address was fraudulently specified in the original packet.

    How does this amplify our attack? Well, the attack is sending an extremely small packet to the server running CHARGEN, which will then send back a packet containing the “random” data. This response can be anywhere from a few bytes to several kilobytes in size. Thus, the attacks very small message has been amplified to something much larger.

    Let’s take a look at a quick CHARGEN message exchange. In the capture below, our client has sent a message to the CHARGEN server, which has responded back to it straight away. The client send a packet with a length of 58 bytes, and the server responded with a packet with a length of 1441 bytes. This is roughly a 28 fold increase in size. It should also be noted that the source MAC for the incoming packet matches the destination MAC of the outgoing packet. This means that at the hardware later, the same machine sent and received this packet.

    Packet papture of a legitimate CHARGEN session

    But how do we do this in practice?

    We need to be familiar with network and socket programming to be able to create UDP packets with invalid/spoofed source addresses, it isn’t something we can simply do with the Microsoft .Net Framework’s building UDPClient class. The built in UDPClient class automatically specifies our IP address as the source IP in the packet, and we don’t want that. We need to work with the lower network socket classes to have our way.

    Now, I wanted to try and do all of this in PowerShell, but right now I just don’t have the knowledge, and my .Net and C# skills are pretty limited as well. Thankfully as I was trying to learn .Net socket coding, I came across the site,, and whilst the site resembles something from the 1990’s, there is some really good stuff there. In particular interest to me/us for this code is Chapter 8, and within that chapter, sections 23 and 25.

    Section 23 (Creating Protocols Header Definition Class (C#)) covers off developing a basic wrapper around the low level .Net framework socket components, allowing for a simpler way of creating and sending things like UDP packets. These protocol header definitions are expanded upon in Section 25 (C# Raw UDP Socket Program Example). At the end of Section 25, you will have an application called RawSocketUDP.exe, which is perfect for UDP packet spoofing and amplification attacks. The executable takes in a source and destination IP and port, an IP on the local machine to bind to, a payload size and the number of packets to send. Normally, the bind address (the IP address on our system we want to use to send the packet) and the source address would be the same, because we want the response to come back to us, but if we want to be naughty, we simply say another address is the source.

    Our first CHARGEN attack!

    The demo environment consists of 4 machines:

    Two servers with CHARGEN - 192.168.1 and 192.168.2

    One attacker –

    One victim –

    So how do we go about an attack using source IP address spoofing and the amplification abilities of CHARGEN?

    Well, here is an example CHARGEN attack:

    RawSocketUDP.exe -as -ad -ps 3389 -pd 19 -b -n 1

    A break down is:

    Source (-as) is – this is the system we are trying to take down, our victim

    Destination (-da) is – this is our server running CHARGEN which will be exploited

    Source Port (-ds) is 3389 – The CHARGEN service response will be directed at this port (UDP)

    Destination Port (-pd) is 19 – This is the port number for CHARGEN on the server we are exploiting

    Bind Address (-b) is – This is our IP address, we need to specify which network interface we are send the packet out on.

    Number of times to send (-n) is 1 – Send one UDP Packet

    So what happens when we run this command? Well let’s look at a packet captures. Note: yes, the times are slightly off in the captures, the test lab didn’t/doesn’t have super accurate clocks. Onwards to captures.

    First on the machine we are launching the attack from:

    Packet capture from attacker's PC

    See how we send out a packet, but get no response. Also note that the source isn't our IP address of

    Now on the server running CHARGEN:

    Packet capture from CHARGEN Server

    See how we receive one CHARGEN request packet, and send one out but notice that the source MAC address for the incoming packet differed from the destination MAC addresse for the response! This is the clear sign that the source IP address has been spoofed in thus packet.

    And finally, the victim:

    Packet capture from victim

    See how we receive a packet when we weren't expecting one.

    How do we scale this attack out?

    Now how do we weaponize this on a large scale?

    Well, what about PowerShell!!!

    So how would we go about this one? Well this is/was my thinking:

    1. Get list of CHARGEN systems
    2. Import list into PowerShell
    3. Run a separate PowerShell Job for each server, which:
      1. Runs RawSocketUDP.exe
      2. Sleeps
      3. Repeat

    Sounds pretty easy to do. What we need is a PowerShell CMDLet that can simplify the process, accepting say, a victim’s IP address, some CHARGEN servers, etc. and then run RawSocketUDP.exe in parallel against each of the specified servers. We would need some logic to keep running the executable over and over again, perhaps having a small sleep between each time we send a batch of UDP packets to the CHARGEN server.

    Let’s take a look at what I came up with:

    This is a rather simple CMDLet, accepting as parameters specifying the IP address of the victim, the IP address on out machine sending the initial CHARGEN packets, one or more Servers running CHARGEN (with values taken from the pipeline) and finally the folder where the RawSocketUDP.exe is located. Optionally we can specify the port on the victim side, the number of messages we will send in each period and the period we will sleep between runs. For these optional parameters, the defaults are UDP3389 (RDP), 10 messages and 10 seconds.

    The Process {} code block is rather simple, for each CHARGEN server IP address specified earlier, we will create a new job. The job has a rather simple ScriptBlock {}, which will start by changing the location we are currently at to be the previously specified location of the RawSocketUDP.exe. After that, there is a simple while ($true) loop which will run indefinitely and does two things; run the RawSocketUDP.exe with the appropriate parameters, and then sleep for the specified period of time.

    That’s all of the code, rather simple and easy.

    Let’s perform a larger attack

    This time, we are going to perform a much larger attack than our first. This time we will make use of two servers running the CHARGEN protocol, and to direct attack traffic to our victim on

    After importing the function previously mentioned. Our attack comes down to this oneline:

    So what is this command? Quite simply we are passing an array of CHARGEN server IP addresses to our Start-CHARGENAmpAttack function, specifying the victim IP, our IP and of course, the location of the RawSocketUDP.exe.

    What will happen next? Well, every 10 seconds we will send 10 UDP packets to the CHARGEN server, which will respond, but direct the response to the victim IP. This will keep happening until we stop the jobs with the following commands:

    Of course, we might want to up the number of packets we send, and reduce the sleep period. We probably want to target more CHARGEN servers as well.

    And a quick look at the packet capture on the victim…yes there are lots of unwanted packets flowing in!

    Packet capture from victim with multiple CHARGEN servers attacking

    How effective is it?

    It is hard to really say how effective this attack is. In my simple lab, the two CHARGEN servers did generate a significant amount of traffic, however determining if this could be effective isn’t something I have the gear to do. What I can say is this, in my simple lab, left alone for two hours, the two CHARGEN servers did manage to crash the machine which was the victim. As we have seen this amplification attacks, especially DNS and NTP be quite effective against large targets, then CHARGEN could also be leveraged, providing you can find enough servers running the protocol.

    So where to from here?

    This is the hard part really. No one should have CHARGEN exposed, but it is always worth checking your environment to be sure, and not just the perimeter, this attack could be deliciously fun within a corporate network. Printers and print servers and the like often have CHARGEN running, and would make good little zombies for attacking some other part of the corporate infrastructure, and without some decent monitoring in your environment, this could be a pain to lock down.

    I hope you enjoyed this blog post. It was longer than usual, but I really do hope there was something in there for people to enjoy. If you do try out CHARGEN amplification attacks with what you have seen here, please let me know, I would love to hear from you.