T-SQL Tuesday #87: Data Theft is Red, Backup Encryption is blue…

T-SQL Tuesday #87: Fixing Old Problems with Shiny New Toys

This month’s T-SQL Tuesday, hosted by Matt Gordon (b/t), is about using new features in SQL Server (from 2014 onwards) to fix old problems.

I’ll throw my hands up there right now, I’m not a security guru. Never have been, likely never will be. That said, I’m still cautious about permissions being given (I’ve had to fight against being given Domain Admin rights before), and how data is being stored. I have to be, ensuring the safety of this data is part of my job.

Not all threats you’ll encounter are external, in fact some of the greater threats are probably there right now, right beside you. Disgruntled employees, BYOD without proper policies or implementation, someone looking to make a quick quid or two, or even just carelessnes – data being restored here there and everywhere. I’m not trying to sound paranoid, but as much as we don’t like to, these are things that need to be considered. However you want to look at it, security is a problem, and it’s a big one.

For those of us only able to gaze wistfully at the gold leafed Valentines roses, TDE is not an option, and so we need to look into other alternatives which brings in a whole host of muddling around with third party applications. Then in came SQL Server 2014 with a little extra piece of the puzzle – native backup encryption.

There was an all-in-one application, a complete Practice Management System that dealt with case data, accounts, employee records. The SQL Server itself was locked down pretty well – there was no RDP and access to the drives themselves were locked down tight. All that is, except for the drive holding the backups. The server had been setup to perform daily backups at 2AM, and then transaction logs every 10 minutes. At around 4AM another server would spin up, connect, copy all the backups to its encrypted volume, clean up “expired” backups, then power down. Backups were then left on the SQL Server for a restore job that ran around 10AM, before clearing down.

Management was happy with the setup, so nothing further was thought of this. Requests for setting up Backup Encryption were met with the sounds of “Well, it works as it is…” That was, until the emails started.

There were several tables with triggers against them. The idea being that as new cases with certain conditions were entered into the system, emails highlighting things such as potential risk etc would fire off. Team leaders, doing as they were meant to do, went off in search of these cases within the PMS so they could be continued. Except, they didn’t exist. This probably happened a few times, them putting it down to the case maybe being removed, before they raised it further up the chain. No audit logs showing these being created in the first place, let alone deleted. So up and up the chain it went, until finally we were involved.

Traces, shadowing the user that seemed to be raising these alerts, these phantom cases just didn’t exist. If these cases weren’t being entered, or deleted, from this server, and there’s no trace of the mails being queued within dbmail, then the emails couldn’t have been coming from this server. So we went back and searched the email headers for an IP address… bingo!

Prior to the 10AM job, these backups were being copied in an ad-hoc fashion to another server and restored there. Normally we have a whole process for restoring to another server, which strips out any sensitive information and sanitised the database, but these were directly bypassing the processes. This was probably also not the only copy.

Now that we knew what was happening, we were able to get the copies we knew of sanitised. Fortunately, there was no malicious intent, but it allowed us to further the case for using Backup Encryption. Un-sanitised data would always be backed up with encryption, able to be restored only to a certain set of servers where the sanitisation process would run. Eventually backups were copied to a locked down (permissions wise) network share instead of the convoluted process of copying to the encrypted volume server. Management was satisified, team leaders were satisfied, and another gremlin was binned.

I still had to gaze wistfully at the gold leafed Valentines rose…

Setting up backup encryption:

T-SQL Tuesday #86: But I can do it in Dev…

T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

This month’s T-SQL Tuesday, hosted by Brent Ozar (b/t), is about SQL Server Bugs & Enhancement requests.

You can almost sense it, like a disturbance in the force. There’s that one dev, or one team, and when they start the long walk over you know what it is.

“… Can’t you just enable it on the server? Why are you making this so difficult? We really want/need to use CDC/TDE/(this really cool Enterprise only feature.)”

I am of course talking about Developer Edition just giving you all the features of Enterprise out of the box, and the subject of the following Connect item from Des Norton: https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version

There were several legacy servers from before my time, but I’ll focus on just three – all of which were Standard Edition.

  • Server one hosted a database for Application X (“AppX”), consisting of data for Business Unit 1 (“BU1”);
  • Server two hosted a schematically identical database for AppX, but with data for Business Unit 2 (“BU2”)
  • Server three hosted the database for Application Y (“AppY”), a financial application that was to include data from both AppX databases.

Due to the nature of the data and to prevent conflicts of interest, BU1 should never have access to the data for BU2 and vice-versa. AppX didn’t have functionality to implement information firewalls, and existing business policy at the time was the data should be hosted separate. Dev had gone about setting up their own little test lab of 3 servers, using the Developer Edition (as you’d expect.) Dev was to enhance AppX with auditing functionality due to new requirements on the business, and also to provide a solution to integrate the financial data from both AppX databases into AppY. The project had been going on several months prior to my starting, and I joined near the end of the project when UAT was to begin. The Dev had opted to use CDC to provide the requested functionality. You can probably guess what came next.

Msg 22988, Level 16, State 1, Procedure sp_cdc_enable_db

In the end, the options came down to the following:

  1. Redesign the solution to account for the lack of Enterprise edition; or
  2. Dish out the dosh for servers one and two to become Enterprise; or
  3. Undertake a server consolidation exercise, and only upgrade one to Enterprise edition; or
  4. Ensure we have SA, virtualise, and license the host for Enterprise edition.

The business went for 1. Dev had an extra couple of months of redeveloping the solution, which eventually went live. In total the project ended up 4-5 months past the original expected completion date. There’s more that helped to delay the project than just Developer Edition having Enterprise feature sets by default – I don’t know what the communication between the previous DBA and the Dev was like, or whether these were decisions taken during the period between the previous DBA leaving and myself joining. Being able to target Developer Edition to have the feature set of Standard could have saved those few extra months (and a full week of meetings!)

Whilst those running SQL Server 2016 can apply SP1, which brings some Enterprise level functionality to Standard edition, being able to target a specific edition will still help with future Enterprise only feature releases.