Ransomware – the stuff of nightmares

Viruses, Malware, Phishing and even the dreaded Y2K of yesteryear – none of these “horror stories” keep IT Administrators up at night quite like the term Ransomware does. Like an “invasion of the body snatchers” type scenario, suddenly your data is no longer your own. It’s still on your server, on your very own infrastructure – but your data (arguably, the heart and soul of your operation) is no longer accessible. Even if you pay the Ransom, there’s no guarantee that the nightmare ends.

Yes, you can secure your “always online, always accessible” Server Environment, but the reality is that there’s always a possibility (however minute) of Ransomware lurking just around the corner. For some of us, we want – nay, need – just that little bit of extra confidence that we’ve sufficiently protected ourselves against Ransomware. Almost as if we’re looking for a sort of “chamomile tea” solution, if you would entertain the phrase; just that final bit so that we can rest easily with the assurance that we’ve done all we possibly can to prevent the threat.

As luck would have it, for one particular project, I was able to work hand in hand with an adept team to implement the very same sort of “chamomile tea” solution for one such concerned client.

Adding another layer of protection – a reflective layer at that

So here’s the scenario in a nutshell: The client commissioned another service provider (Skynamo) to implement a Mobile Sales Application that accesses data from a database that we (MRBM) look after. The IT Administrator has kitted out the SQL Server (named VEGA) with all sorts of Ransomware protections – Firewall, Anti-Virus, Secure Access policies, etc – the whole nine yards. However, he’s still apprehensive about opening up ports on VEGA, essentially having it open to the whole WWW. That’s when he pitches his “chamomile tea” solution – they’ve got another server (SIRIUS) which was previously used as a test environment for Custom Development that hasn’t been in active use for some time. He’s happy to open up ports on SIRIUS (being a non-critical Server), so he asks if we can mirror the database from VEGA to SIRIUS so that the data can be accessed from there instead.

As with most IT solutions, the answer was “of course we can – but it’s not necessarily straightforward and not without some caveats”. There were several challenges to overcome:

VEGA and SIRIUS were running SQL Express 2014 and SQL Standard 2008 R2 respectively.

• While SQL Express does support some mirroring, it can’t act as a Primary Mirror.
• Other issues arose from this as well – inability to restore to lower version, etc.

The Mobile Sales Application only needed to access Master Data (less than 1GB worth)

• While the entire database was not large (< 20GB) it would have been redundant to mirror it entirely (transactions and all) for only a sliver of data to ever be accessed.

Lastly, and the most challenging, VEGA and SIRIUS had differing default SQL COLLATIONS.

• Any database admin worth his salt knows that mixing COLLATIONs is bad news.
• Because of how we had to implement this (without traditional mirroring), this had its own separate cadre of challenges to overcome.

Now as service providers on opposite ends of the problem (making the data available vs consuming the data), it’s a good thing that we had a solid working relationship from having collaborated on a couple projects across several sites previously. Had that not been the case, we most likely wouldn’t have been able to fully realize the solution that the client wanted. In the end, here’s how we did it:

We implemented the entire Mirror using SQL scripts.

• And we do mean the entire Mirror. Oh, we tried several things first – Copy, Restore, Base Copies of the Database and few other things. Most of these fell flat because of issues with the Version and COLLATION differences.
• This included Tables, Views, Functions, Triggers and all the in between plumbing that made the database work, with specific focus on getting the objects in place for the Mobile Sales Application.
• Both Service Providers worked closely, pruning and perfecting scripts until the scripted Mirror worked as intended.

Summary – Reflecting on the solution

It’s a very non-traditional solution, but it works. We’ve got a Database Mirror without implementing SQL Mirroring, a fully Scripted Database Synch with JOINS and FUNCTION CALLS across Servers with differing Builds, Versions, Editions and COLLATIONs!

Trawling the web in preparing this “chamomile tea” revealed that many others had given up when presented with some of the challenges we overcame during this implementation. So why did we work that hard at achieving this for the client? Because, both service providers were on board with the idea that for the client, peace of mind is priceless.

If the unthinkable does indeed happen and Ransomware does hit SIRIUS, the IT Administrator can rest easily knowing that the soothing “chamomile tea” solution successfully protected VEGA from the brunt of the attack’s payload.