February 12, 2015 by Kenneth Fisher
When I decided on security as my topic for February’s T-SQL Tuesday blog party my thought was that security was a topic everyone would have something to say about as it’s something that touches the lives of every DBA at some level or another. Well it turns out I was right. We had a great turnout with lots of very interesting posts on a huge range of security topics. If you want a good overview of security in the SQL Server world you could do worse than reading these posts. In fact you could almost write a book using just these posts. So I did.
SQL Server Security by the Community
Chapter 1 – Overview
Sebastian Meine (b/t)
Sebastian freaked me out for a second with his frightening blog title You’ve been hacked! This is at once a frightening overview of why we need security and a high level overview of how to fix it. I particularly like the onion.
Chapter 2 – Physical Security
Paul Randal (b/t)
Paul picked a topic I don’t see very often. Securing the data technologically is important. And you certainly don’t want to forget the human factor. But to be truly secure you also can’t forget the physical side of security.
Chapter 3 – Database Engine Security
Section 1 – Privileged accounts
Boris Hristov (b/t)
Boris’ post on managing security discusses that all too common horror story of a vendor that requires sysadmin permissions. He then moves on to discuss the risk of NT Authority\SYSTEM. (And if you don’t already know what that risk is you really need to read this one.)
Andrea Allred (b/t)
Andrea may be known for her princess gowns and sweet demeanor but based on her first official T-SQL Tuesday post she doesn’t need any Prince Charming to step in and save her servers. Her advice is actually something I’d read about recently and I really love the idea. If you’ve ever wondered what to do to protect yourself from malicious use of sa you need to read her post.
Kenneth Fisher (me)(b/t)
For my own contribution I discussed the reality of denying permissions to db_owner vs dbo.
Section 2 – Using roles
Steve Jones (b/t)
Using database roles has been considered a best practice as far back as I can remember. In his post Steve gives us an example from his past of why this is so important and the steps they took to implement it.
Jim Dorame (b/t)
In another take on roles Jim takes his grouping layer out to the server itself.
Section 3 – Dealing with Logins
Robert Pearl (b/t)
Robert mentions a couple of very handy bits of security information. First, if you are using SQL authenticated logins there are some settings you had better be aware of. And second he gives us some handy T-SQL for looking at the current status of the logins on a server. It also turns out that Robert’s upcoming book (quick plug here) HealthySQL will contain even more security goodness.
Section 4 – Coding
Rob Farley (b/t)
Rob discusses SQL Injection in his excellent post SQL Injection – the golden rule. In it he describes a way to think about SQL injection that makes it much easier to tell if you are vulnerable and to avoid that vulnerability in the first place.
Section 5 – Monitoring
Daniel Mellor (b/t)
Using Policy Based Management to review your security setup is something I’ve been looking at recently myself. In Daniel’s post on the subject he answered one of my current questions and has set me on the road to solving something that has been bugging me. Expect to hear tweets on the subject Daniel. I should also mention this is his first time joining us on T-SQL Tuesday. So thanks for joining in!
Section 6 – HA & DR
Warwick Rudd (b/t)
Are you using AlwaysOn Availability Groups? If so you need to read Warwick’s post on making sure the security of your replicas continues to work when you fail over.
Angela Henry (b/t)
A server going down is never fun. When that server is your payroll server and you are on a hard deadline it’s even worse. Angela relates a “somewhat embellished” story from her own career and the security ramifications of restoring your databases on a new server.
Chapter 4 – BI security (SSAS & SSIS)
Koen Verbeeck (b/t)
One aspect of security that tends to trip up DBAs and developers alike is that of the SSIS protection levels. Koen’s post gives a great rundown of the protection levels for SSIS packages and when to use each. He also keeps us up to date by going over some of the changes made to this aspect of SSIS in 2012.
Capter 5 – Key Management
Chris Yates (b/t)
In summary security is something with a lot of moving parts that we have to pay attention to. There is a mindset needed to keep your data secure and Chris Yates does a great job letting us know where our heads should be when it comes to security.