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:

• Error: “The local node is not able to communicate with the WSFC cluster” – AlwaysOn Availabiliy Group
• Microsoft KB 2847723: Cannot create a high-availability group in Microsoft SQL Server 2012

Links for PowerShell Shenanigans

I thought it might be easier for those wanting the links from my resent presentation if I provided a list of them here, so you didn't have to go through the presentation to find them.

My code on GitHub: http://j.mp/1i33Zrk

QuarksPWDump: http://j.mp/1kF30e9

PowerSploit: http://j.mp/1gJORtF

PowerWorm Analysis: http://j.mp/RzgsHb

PowerBleed: http://j.mp/1jfyILK

Microsoft PowerShell/Security Series:

http://j.mp/OOyftt

http://j.mp/1eDYvA4

http://j.mp/1kF3z7T

http://j.mp/NhSC0X

http://j.mp/NhSEpy

Practical Persistence in PowerShell: http://j.mp/1mU6fQq

Bruteforcing WinRM with PowerShell: http://j.mp/1nBlwX2

I hope you all enjoy!

Brisbane Infrastructure Group Presentation

On Tuesday I presented to the Brisbane Infrastructure Group a similar presentation to the one at CrikeyCon. This presentation contains updated information, some defence measures and just better information overall.

You can download the slide deck here, and the SlideShare can be found here (and embedded below).

I am working on a recorded version, which should be up in the coming weeks.

I have been tied up with some projects at work, as well as some personal development and personal projects at home. One of two I *hope* to be writing about really soon.

PowerShell Error: The specified structure must be blittable or have layout information.

So recently I was working with the Get-TSLsaSecret CMDLet which was written by Niklas Goude, a guest blogger from TrueSec who was writing on the Hey Scripting Guy Blog.

You can find the write up on the CMDLet here, It is also worth reading the previous post in the series here.

Everytime I ran the CMDLet, ensuring that I was running as a 32bit session, which was elevated, I would recieve the message "The specified structure must be blittable or have layout information.", I looked and looked and couldn't see anything I was doing wrong, or anything wrong in the code.

I the stubled upon Microsoft KB 2909958. Resolving the issue is very simple, we just need to do another cast on the line with the error.

In the code from Microsoft/Niklas, change:

[LSAUtil.LSAUtil+LSA_UNICODE_STRING] [System.Runtime.InteropServices.marshal]::PtrToStructure($privateData, [LSAUtil.LSAUtil+LSA_UNICODE_STRING])

to:

[LSAUtil.LSAUtil+LSA_UNICODE_STRING][System.Runtime.InteropServices.marshal]::PtrToStructure($privateData, [System.Type][LSAUtil.LSAUtil+LSA_UNICODE_STRING])

And you are done!

More to come on Get-TSLsaSecret!!!!

DirectAccess with Computer Certificates and SHA512 algorithms

I have posted this as a question on the TechNet forums, but also wanted to post it here.

I have just finished another test lab deployment of Direct Access, and have noticed one interesting issue which I am trying to confirm.

In the lab I deployed the PKI part of the infrastructure quite a while ago, its a typical deployment, with an offline root and online issuing authority. They were configured to use the SHA512 for the Signature and Signature Hash algorithms. This selection might seem paranoid, but has never been an issue as all clients have been Windows 7 or higher and thus have full support.

I deployed direct access, and using basically the default settings, everything appears to be working correctly, clients could successfully connect.

I then switched to requiring computer certificates, issued a certificate to the DA Server and the test clients based upon the "Computer" template.

Clients could no longer connect. Looking at the diagnostic logs for Direct Access it appeared that the tunnels were not being established correctly. Looking at get-DAConnectivityStatus, there was definitely an issue present, with a sub status error referring to errors with remote network authentication. Nothing really appeared out of the ordinary in the client diagnostic logs or event viewer.

I went looking on the server, nothing appeared at first to point to a problem, however on inspection of the system event log, there were errors in regards to TLS:

'An TLS 1.2 connection request was recieved from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed'

So I looked around and didn't find anything out there, I couldn't see anything obvious, then it occurred to me that we are using SHA512 in our certificates.

I quickly fired up a new CA in the test environment, this time basically accepting the defaults. Reissued certificates computer certificates to the DA server and the test clients, and I am now successfully connecting.

Does anyone know of this incompatibility? is it documented anywhere? IS this known by anyone? Has anyone seen this as well?

Any more info would be great.