SQL Server Encryption Presentation on July 9, 2015

I will be giving a presentation on SQL Server Encryption through MSSQLTips. It’s at 3 PM EDT on July 9, 2015.

You can register through the MSSQLTips.com page for the webinar.

This sign-up page will allow you to sign up for multiple future webinars.

A rough outline of the presentation:

Data in the Database

  • The case for partial encryption (some data unencrypted)
  • The datatypes we use for encrypted data
  • The options available and who can see decrypted data
  • How we use SQL Server’s built-in functionality
  • Addressing Performance Issues

Encrypting the Whole Database (Transparent Data Encryption)

  • How it works
  • What you need to make it work
  • How do you handle recovery / disaster recovery

Encrypting Backups

  • Don’t wait until after it’s written to disk
  • TDE to the rescue
  • Encrypted backups in SQL Server 2014
  • Don’t reject 3rd party products

Encrypting Connections to SQL Server

  • The options
  • What about POODLE?
  • What about IPSEC?

On Software and OS Lifecycle Management

An Important Rule: 

If you’re trying to convince someone to your viewpoint, insulting them generally doesn’t work. If it does anything, it will be more likely to entrench them against your position. Therefore, this is something that IT professionals should avoid. 

If You Write Your Own Software

or work for an organization that’s core business runs on the software your organization writes, it is easy to miss the issues that folks who typically deploy third party solutions face. Among them is what the vendor will support with regards to operating system, core application components, etc. For instance, an IT pro may be perfectly ready to roll a Windows Server 2012 VM for a new application deployment. However, the vendor doesn’t support beyond Windows Server 2008 R2. Guess what the IT pro is going to deploy? In the vast majority of cases, that server is going to be Windows Server 2008 R2.

As a young IT pro, I often thought, “Hey, I can convince the vendor to support my configuration.” What I quickly learned, however, was quite different. As The Rock says,

“It doesn’t matter what you think!”

If you have to run a particular software package and they have requirements you don’t like, most of the time you have to accept your dislike and conform to the vendor.

When New Features Trump Maintenance

Being a security type, I always want the most streamlined, secure operating system and/or application. However, taking the time to upgrade takes away from time to implement new features unless the new features desired are in the operating system or application that is in need of an upgrade. If the new features come as a result of development or another application, you may not get the option of upgrading when you want. This is especially true when you support a lot of core applications and when business is constantly looking for new features and values new features over maintaining existing systems (even though replacement or fixes or upgrades will be substantially more at a later time). 

Trying to fight this in some organizations is fruitless. The organization will perform the upgrade (or migration to a new OS baseline) when they are forced to dos o. In this case, a bit more wisdom from The Rock,

“Know your role!”

TL;DR Version

We don’t always get to pick when we upgrade OS or application version. There are other factors in play. Don’t assume that one professional’s seeming lack of desire to do so is because of any particular reason. The only way to know is to ask, and to do nicely, without insulting said professional.

On PowerShell

I use PowerShell a lot and I write about using it to solve problems quite frequently. The fact that I can extending Powershell by interfacing with the .NET Framework or making a COM/COM+ object call means I can do just about anything I need to do in order to manage a Windows system. As a result, I consider PowerShell one of my most powerful tools.

However (you knew there was going to be a however), PowerShell is one tool among many. If you are a smart IT pro, you build your toolbox with the tools that are most appropriate for you. Yes, you take into account where the industry is as well as what your current job(s)/client(s) use. Sometimes that means you choose a tool other than PowerShell. To some, though, that sounds of blasphemy. It shouldn’t be. If you’re a senior IT professional, you should be amenable to finding the right tool for the job, even if it’s not the one you like the most. If you’re at an architect level, you had better be prepared to recommend a technology that is the best fit, not the best liked (by you).

When I think in these terms, it means I don’t build Windows system administration tools with Perl any longer. Unfortunately, even though ActiveState still has a very functional version, Perl has faded greatly from view on the Windows side. Granted, it was never very bright, but there were some big name proponents and it gave a whole lot of functionality not available in VBscript/Cscript/Jscript. That’s why some enterprise shops turned to it. With PowerShell, the functionality provided by Perl on Windows systems, the functionality missing from earlier Microsoft scripting languages, is present. So PowerShell will usually make more sense.

I said usually. I don’t automatically select PowerShell because it is the recommended standard by Microsoft. What clients am I running on? What other languages am I using? For instance, if I’m a heavy Python shop, that can be used to manage Windows systems. It may be more cost effective to write in Python than in PowerShell. If I have linux and Mac OS X platforms, I’m likely not using PowerShell. It’s all about the right tool for the job. And the right tool has more considerations than what a particular company recommends.

On Automation

I’m a big fan of automation. I’ve been in IT for 27 years now. One unchanging rule during that time is there is always more to do than there is time to do it. Automation helps close that gap. And when I can automate something, I can do more than peers who can’t. That gives me a competitive advantage. So, three cheers for automation. 

However, the reality is that a lot of administration is still manual. It may sound clever to say that if it’s not automat-able it’s not something you want a part of or that you’re not a player in some space because you don’t automate. But that’s not reality. 

For instance, people can choose to use the cloud and not automate. One reason that the cloud was advertised in the first place was to reduce on-premise costs. You could move to cloud servers and shutdown your costly datacenter and save. You didn’t have to change your day-to-day activities and you would still likely save. That’s not always true, as some startups have shown the math of switching to their own servers when reaching a certain capacity point. But that’s not the point. The point is you should be able to use the cloud even if you aren’t going to automate. 

It may not be as efficient or as cost-effective, but it still should be doable. There may be other business drivers that prevent IT from embracing automation. In the real world, that happens. It happens a lot. There are a finite number of resources. And if business determines that you as a resource would be better spent building out something new rather than automating something existing, then you are building something new. That’s reality. 

So when I hear about a new technology like Nano, I can like it without jumping on the automation bandwagon. Look, you just told me it’s compartmentalized and there’s a lot of surface area removed, even when compared to Windows Server Core. From a security perspective, I am doing a happy dance. I agree that automation makes it better. But just because your vision is automation, automation, automation, doesn’t mean it is everyone’s. And when there are other factors to consider, they may be right for what they are trying to do.

Trust No One Implicitly

At the Charlotte BI Group meeting last night, one of the questions I was asked after I gave my talk on Securing the ETL Pipeline was this:

“So you’re basically saying we should trust our DBAs?”

My response caught several people off guard:

“No, I’m saying to trust no one. Not even your DBAs.”

That received more than a few raised eyebrows. I went on to explain. I have two simple reasons to make this statement:

1) The difference between a trustworthy and untrustworthy employee is one life event.

Your DBA gets hammered in the divorce settlement and is now looking at barely scraping by. He or she has access to data that can be sold, and sold for a lot of money because (a) there is a lot of it and (b) it’s verified. You don’t think temptation is going to change a few folks’ behavior? Instead of divorce, substitute bankruptcy due to medical bills (especially if said person lost a loved one after all those bills) or a drug habit that becomes consuming.

A point I made along these lines is we often don’t know the personal lives of our co-workers, so it’s not a given that we’d catch such things. After all, a pilot who didn’t want to lose flying status was able to hide that he was shopping around doctors and we know how that ended up.

2) It might be your employee’s ID, just not your employee.

The Anthem hack tells us all we need to know on this topic. A telling quote from the article:

“An engineer discovered the incursion when he saw a database query being run using his credentials”

Trust No One Implicitly:

As #2 points out, even if you have trustworthy employees, you still have the case where an attacker can get in and steal data. Even though you trust your employees, you need to have controls in place that performs checks like you don’t trust them. That was my point last night. It’s no longer a matter of if an intruder is going to get in. It mostly definitely is now when and for how long.

Speaking at Charlotte BI Group Tomorrow

Tomorrow, Tuesday, April 7, 2015, I’ll be speaking at the Charlotte BI user group. The meeting starts at 5:30 PM.

Here’s the info:

RSVP Link

Topic: Securing the ETL Pipeline

We’re going to look at typical ETL (Extract, Transform, Load) pipelines and consider the weak points an attacker might go after. Our goal in this isn’t to cause FUD (Fear, Uncertainty, and Doubt), but to discuss risks at each point, options for protecting the vulnerability, and what we’ve seen typically done (if anything). While this talk primarily focuses on Microsoft SQL Server, especially the database engine and SSIS, many of the points covered will be applicable to any solution set.

Location: 8055 Microsoft Way, Charlotte NC 28273

Women in Technology

Warning: rant forthcoming.

I don’t get the women in technology problem. Oh, I understand and see the problem. I also understand and see the problem is primarily with men. I just don’t get why there is a problem. 

Maybe the reason I don’t get the problem with women being our peers goes all the way back to first grade. In my first grade class, there were four of us always competing for top marks: Barbara, Olivia, Sasha, and me. It didn’t matter what the subject was, we fought hard for the best grades. Based on that small sample size one could wonder why men were qualified to be in technology. Thankfully, no one thinks this way. 

Fast forward to high school. My junior and senior years were spent at the SC Governor’s School for Science and Mathematics (SCGSSM). At that school, roughly half of the students are male. The other half are female. And let me tell you that intellectually, male or female, they are flat out awesome. I have two bachelor’s degrees: physics and mathematics. Some people treat degrees, especially technical degrees as a sign of intelligence. If that were the case, then I’m among the dumbest kids to graduate from my high school. So many of my classmates have doctorates. Note I didn’t specify “male” classmates. It’s not the proper adjective. 

After high school I went off to what was then an all-male military college, The Citadel. It wasn’t because I wanted to get away from women or thought they were less capable. I wanted a military college because I was seriously considering a 20-year career as an officer. I wanted a multi-service military college because I knew that was the direction the military was going. And I wanted to be close to home because my mom was having some serious health issues. At The Citadel I interacted with some outstanding female professors like Dr. Jane Bishop (history) and Dr. Mei Chen (mathematics). With regards to IT specifically, I was blown away by Dr. Margaret Francel (computer science). I’ve met very few males who can keep up with her intellect.

I also spent a lot of my college time havening. On the havens I met very knowledgeable peers who were female. Folks like Eliste, pooh, and Tenelia, to name a few among many, knew their way around *nix and NeXT systems.   After college, Eliste went into IT and she was good at it but she has moved on to other challenges. Tenelia knows her geology and tracks earthquakes. And the one that I still talk to nearly everyday (when she isn’t trying to trounce me in Words With Friends), pooh, is still one of my go to people if I have a development question. That’s why my experience at The Citadel reinforced my view from SCGSSM: it isn’t about gender.

Therefore, I don’t get why some males have an issue with females being their peers. If a woman can code, she’s an asset to my team. If she can troubleshoot a routing issue, she’s one more competent coworker to share the load. If she can figure out why the database server suddenly starts choking on some bad queries, that means I can focus on something else. A slow day in IT is like that mythical unicorn – it doesn’t exist. There’s more work than there are workers. If you don’t see more work than you can possibly accomplish, then I don’t question her skills, I question yours. 

Previous Older Entries

Follow

Get every new post delivered to your Inbox.

Join 4,110 other followers