As part of my SQL Saturday 194 homework, I’m reading through “Securing SQL Server: Protecting Your Database From Attackers” by Denny Cherry (blog|@mrdenny on Twitter). Denny’s doing a full day training session (“pre-con”) at this event on “SQL Server Security”, so I thought I would try to get ahead of the game a little…
Securing SQL Server: Protecting Your Database from Attackers
Denny’s book (Amazon US, Amazon UK) takes us through the whole security process, starting with the basics of network & firewall configs, and working through encryption, passwords, programming, and includes a section on auditing; this last is a subject that is becoming increasingly important for those of us who have to deal with the little things like SOX compliance, for example!
Helpfully, at the end of each chapter, in addition to the obligatory summary section, is a handy list of references.
Chapter 1: Securing the network
This is a huge area, and there are many books available on the subject. However, this provides a handy checklist of things to think about, starting with controlling external access through firewalls and going on through network topology (such as use of a DMZ).
He then goes on to a brief introduction to physical security, including the headline “Keep your hands off my box”. This is stuff that I’ve had frequent discussions with previous employers, such as the basics of securing your data centre from unauthorised access by the untrained and unsupervised. Can you imagine the damage that can be caused by someone in your data centre, even if all the servers are locked down?
Chapter 2: Database Encryption
A not-so-quick look at database encryption techniques and algorithms, comparing server-side and client-side techniques and including C# & VB.NET sample code for use in your client applications. There’s a useful section on Transparent Data Encryption, mentioning the lack of TDE on FileStream data. There are also discussions on Network Sniffing, running over SSH, setting up IPSec Policies, doing encryption at the network device level, including walkthroughs on how to get these up & running. The chapter ends up with a very brief mention of your options when running SQL Azure (sorry, Windows Azure SQL Database) – very limited.
Chapter 3: SQL Password Security
Anyone remember SQL 2000 and the ability (by default) to install it with a blank sa password? I’m not alone – Denny also remembers this… So this chapter looks at Windows vs SQL passwords / security, touching on Extended Protection (for SQL Server 2008 R2 and above), SPNs, strong passwords (smilies in passwords? really?). The chapter moves on to protecting the Connection String itself within, say, a web application. Moving on, there are discussions of application roles within SQL Server, securing Linked Server passwords, using Windows policies for password strength (including how to audit for passwords that don’t match this).
Now, how do we keep track of all those passwords?
Chapter 4: Securing the Instance
This chapter covers security at the instance level, with the first step of ensuring that you only install and configure features that you actually need. There are a couple of side discussions about the SQL Browser service, and the SQL Slammer worm from 2003 – anyone here remember that? I do… Further discussion on SQL Server Authentication vs Windows Authentication, but now covering more about the Windows Logins that SQL Server creates. There’s also some discussion about renaming or disabling the SA account.
The chapter then moves to a discussion of securing access by using Stored Procedures rather than granting access to the database tables themselves, and a side discussion on Dynamic SQL. This really boils down to “Minimum Permissions Possible” – another one of those mantras that’s well-known to those of us who work with auditors and other security guys…
What else? Linked Servers, Policy-based Management, BitLocker…
Chapter 5: Additional Security for an Internet-Facing SQL Server and Application
Oh, this could be a big area, again bringing up some painful memories…
Denny takes us through the SQL CLR, Extended Stored Procedures, and further discussion on protecting your Connection Strings before touching on specialist Database Firewalls. From there, Denny talks about the pros and cons of clearing the virtual memory page file at system shutdown, before moving on to User Access Control, or UAC. There are some useful hardening settings discussed here, as well as some other domain policy settings.
As well as discussing the security of SQL Servers, Denny also touches on security within SSRS.
Chapter 6: SQL Injection Attacks
This is a favourite of mine, and it’s never the done thing to discuss this without mentioning Little Bobby Tables. (What’s scary is I know that link without having to look it up, thanks to pointing so many people at it in the past… What, you don’t know what I’m talking about? Go, click the link – I’ll still be here when you get back.)
SQL Injection is still a major problem, and Denny takes us through various scenarios of possible attack, and how to defend against them. I would also recommend reading Alex Kuznetsov’s book “Defensive Database Programming” (which Denny doesn’t reference).
Chapter 7: Database Backup Security
There’s not a lot of point in protecting your database, and then allowing for no security around the actual database backups. We regularly hear horror stories of sensitive data being transported on unencrypted disks which are then lost – governments seem particularly prone to this sort of problem, probably because their losses are more likely to be widely reported…
Denny covers off Maintenance Plans, naming your database backups with date/time flags, routine deletion of old backups, encryption of backups, including discussion of some of the third-party tools available to help with this. Denny also reprises the Transparent Data Encryption functionality first discussed in Chapter 2, as that is also applicable here.
Chapter 8: Auditing for Security
Auditing in SQL Server only really comes a possibility with SQL Server 2008. However, this chapter covers off information about login auditing, data modification auditing (through Change Data Capture), data query auditing, schema change auditing. There’s a useful section on using Policy-Based Management to run audits, both locally and centrally. C2 auditing, the old school, is being superceded by “Common Criteria”, and Denny talks about both, as well as providing links to documents and tools to help with the implementation of Common Criteria auditing.
Chapter 9: Server Rights
Again, we’re looking to keep the rights as low as possible. Fortunately, later versions of SQL Server make this easier, and Denny takes us through what’s required and how to manage it, at the server level, the OS level, the file system level. Not just for the SQL Server software, but also the rights required by the DBA. What next? “Console Apps Must Die”. Oh yes.
Then there’s the problems caused by third party vendors not necessarily understanding SQL Server permissions. I’ve run into this problem a couple of times, and have blogged about it elsewhere. If only all vendors would pay attention to what’s actually required…
And that’s it for chapters – we now have an appendix with useful checklists for getting you through PCI-DSS, SOX and HIPPA audits that I wish I’d seen a couple of years ago before going through my first encounter with SOX…
This is an impressive book, covering a lot of ground. Although no book of this size can hope to cover everything in sufficient detail, this does give you a very good start and provides a useful set of references for further information on the really intricate bits. Thoroughly recommended.