Securing SQL Server: Protecting Your Database from Attackers

385 Pages·2012·13.612 MB·English

by  Denny Cherry

Securing SQL Server
Securing SQL Server
Second Edition
Denny Cherry
AMSTERDAM • BOSTON • HEIDELBERG • LONDON
NEW YORK • OXFORD • PARIS • SAN DIEGO
SAN FRANCISCO • SINGAPORE • SYDNEY • TOKYO

Syngress is an Imprint of Elsevier
Acknowledgements

I’d like to thank everyone who was involved in putting this book, my second solo
project, together (if I forgot you on this list, sorry). This includes my editor Heather,
my friends/coworkers/peers/whatever Jessica, Thomas, Mark, Aaron, Rod, Diana
and Sergey who all helped me out greatly in putting this book together.
v
This book is dedicated to my lovely wife Kris who is gracious enough
to allow me to spend every waking moment working on this, and
to spend countless nights, weekends and entire weeks traveling in
support of the SQL Server community.

vii
Author Biography
Denny Cherry is an independent consultant who has over a decade of experi-
ence managing SQL Server, including some of the largest in the world. Denny’s
areas of technical expertise include system architecture, performance tuning,
replication and troubleshooting. Denny currently holds several all the Microsoft
Certifi cations related to SQL Server for versions 2000 through 2008 including
being a Microsoft Certifi ed Master for SQL Server 2008. Denny also has been
awarded the Microsoft MVP several times for his support of the SQL Server
community. Denny has written numerous technical articles on SQL Server
management and how SQL Server integrates with Enterprise Storage, in addi-
tion to working on several books.
ix
About the Technical Editor
Michael Cross (MCSE, MCP+I, CNA, Network+) is an Internet Specialist/Program-
mer with the Niagara Regional Police Service. In addition to designing and maintain-
ing the Niagara Regional Police’s Web site (www.nrps.com), he is the SharePoint
Administrator of their intranet. He has also provided support and worked in the
areas of software development, hardware installation/repairs, database administra-
tion, graphic design, and network administration. In 2007, he was awarded a Police
Commendation for work he did in developing a system to track high-risk offenders
and sexual offenders in the Niagara Region. As part of an Information Technology
team that provides support to a user base of over 1,000 civilian and uniformed users,
his theory is that when the users carry guns, you tend to be more motivated in solving
their problems.
Michael was the fi rst computer forensic analyst in the Niagara Regional Police
Service’s history, and for fi ve years he performed computer forensic examinations
on computers involved in criminal investigations. The computers he examined for
evidence were involved in a wide range of crimes, inclusive to homicides, fraud, and
possession of child pornography. In addition to this, he successfully tracked numer-
ous individuals electronically, as in cases involving threatening e-mail. He has con-
sulted and assisted in numerous cases dealing with computer-related/Internet crimes
and served as an expert witness on computers for criminal trials.
Michael has previously taught as an instructor for IT training courses on the
Internet, Web development, programming, networking, and hardware repair. He is
also seasoned in providing and assisting in presentations on Internet safety and other
topics related to computers and the Internet. Despite this experience as a speaker, he
still fi nds his wife won’t listen to him.
Michael also owns KnightWare, which provides computer-related services
like software and Web development, Web page design, SharePoint, and consultant
services. He has been a freelance writer for over a decade and has been published
over three dozen times in numerous books and anthologies. When he isn’t writing or
otherwise attached to a computer, he spends as much time as possible with the joys
of his life: his lovely wife, Jennifer; darling daughter Sara; adorable daughter Emily;
and charming son Jason.
xi
Introduction

As you move through this book you may notice that this book doesn’t gently fl ow
from one topic to another like a lot of technical books. This is intentional as many
of the subjects covered in this book are going to be related, but separate fi elds of
study. As you move through the various chapters in this book you’ll be able to
secure a portion of your infrastructure. If you think about each chapter of the book
as an independent project that you can take to your management the way that the
book is structured may make a little more sense. My goal for this book is that
after reading it you’ll have the most secure database that you can have within your
environment. If you purchased the fi rst edition of this book you will see a lot of
material which is the same. The reason for this is that this is all still relevant, and
didn’t need to be rewritten. Every chapter in this book has been updated in some
way with more information and new information for SQL Server 2012, so every
chapter is worth the reread.
Our book starts from the outside looking in, with the most outside thing that can
be controlled being your network design and fi rewalls. In larger shops this will be
outside the realm of the database professional, but in smaller shops there may be a
single person who is the developer, DBA, systems administrator.
There are a lot of database encryption options available to the DBA. Usually
many, many more than most people realize. As we move through this chapter we’ll
start by looking at how to encrypt the data within the database itself, then move to
having the SQL Server automatically encrypt all the data, having the MPIO driver
encrypt all the data, and having the HBA encrypt all the data. Not only will we
look at how to do each one, but what the upsides and the downsides of each of these
techniques are.
One of the most common problems at smaller database shops are password
policies, and using week passwords in production. In Chapter 3 we’ll go over using
some ways to ensure you are using a strong password, and some best practices to give
yourself some extra layers of protection.
In Chapter 4 we’ll look at securing the instance itself, including minimizing the
attack surface, and securing the parts of the database which we have to leave open
for client connections.
Chapter 5 is really geared towards the smaller companies who have to have their
databases accessible from the public Internet (hopefully if this is you, as you’ll be
going through Chapter 1 as well). In this chapter we are going to look at some extra
precautions that you can take to protect yourself to make it as hard as possible for
someone to break into your database.
In Chapter 6 we will look at the SQL Server Analysis Services service from
a security perspective in order to ensure that this is as secure as the source data in
the OLTP and Data Warehouses. Just because there is rollup data being stored here
doesn’t mean we can let it out into the world.
xxi
xxii Introduction
In Chapter 7 we look at SQL Server Reporting Services from a security point of
view. The reports hosted here could give unauthorized people a wealth of information
that more than likely shouldn’t be leaked out on the public internet.
In Chapter 8 we are going to look at one of the most common techniques for
breaking into a Microsoft SQL Server, the SQL Injection attack. We’ll look at why
this attack vector is so successful, how to protect yourself, and how to clean up after
an attack.
The next chapter is Chapter 9 where we are going to talk about what is probably
the least favorite subject of everyone in an Information Technology role, backups.
No matter how secure your database is, if your backups aren’t secure then nothing
is secure.
As we move into Chapter 10 we will look at the security options as they relate
to the Storage Area Network. Specifi cally we will look at some security options on
various storage arrays and the network switches which connect our servers to them.
Probably the next least popular topic is Chapter 11, auditing. You need to know
when something is happening within your database, and who is doing it.
In Chapter 12 we look at the various operating system level rights that people
within the organization should have.
In Chapter 13 we look more specifi cally at object level permissions within the
database itself including granting, revoking and denying rights to objects.
The appendix at the end of this book is a set of checklists which you can use to
help pass your various audits including what information is available in the book
regarding the new SAEE compliance. While they aren’t a sure fi re way to ensure that
you pass your audits, they are a set of bullet points that you can use to work with your
auditors to ensure that you can get to passing quickly and easily.
CHAPTER
1
Securing the Network
INFORMATION IN THIS CHAPTER:
• Securing the Network
• Public IP Addresses versus Private IP Addresses
• Accessing SQL Server from Home
• Physical Security
• Social Engineering
• Finding the Instances
• Testing the Network Security
• Summary
SECURING THE NETWORK
You may think that talking about the network is a strange way to start off an S tructured
Query Language (SQL) Server book, but the network, specifically the perimeter of
your network, is the way that external threats will be coming to attack your SQL
Server. A poorly defended network will therefore give an attacker an easier time to
attack your network than if the network were properly secured. In larger companies
the network design and lockdown would be under the control of the network admin-
istration and network security departments. However, in smaller companies, you may
not have either a network security department or a network administration depart-
ment. You may not even have a full time database administrator (DBA) or systems
administrator . In a typical larger company, developers do not have to worry about the
network design and setup as this is handled by the network operations team. How-
ever, in smaller companies the software developer may be asked to design or even
configure the network along with the Web servers or application servers.
No matter your position within the company, it is always a good idea to have a
working understanding of the other technologies in play within IT. This will allow
for decisions to be made in a more thorough manner by looking at the entire infra-
structure instead of examining how the process needs to be completed with just one
piece of technology or another.
Securing SQL Server http://dx.doi.org/10.1016/B978-1-59-749947-7.00001-0 1
© 2013 Elsevier, Inc. All rights reserved.
2 CHAPTER 1 Securing the Network
Network Firewalls
At your network perimeter will be your network’s firewall. This will probably be a
network device in its own right or a software component within your network’s main
router to the Internet. This firewall is designed to block and allow traffic based on a
set of rules that have been loaded into its configuration. Some routers do not have
a firewall software package loaded into them. In the case of network devices that
don’t have a built-in firewall, you’ll want to use the Access Control List (ACL) of
the device to control what port connections are allowed through the network router.
With regard to blocking access through a device, an ACL can be just as effective
as a full firewall. However, a full firewall will give you additional protections that
the ACL cannot, such as providing you with Distributed Denial of Service (DDoS)
protection. DDoS protection is used to keep a network up and running in the event
that the network comes under a DDoS attack. A DDoS attack occurs when a group
of computers, usually zombie computers owned by unsuspecting people being con-
trolled by a hacker, send large numbers of requests to a specific Website or network
in an attempt to bring the network offline. DDoS protection is handled by specific
network devices that are configured to look for patterns in the network traffic that is
coming into the network, and block network traffic from reaching the destination if
the network traffic appears to be part of a DDoS attack.
Typically, your firewall would sit between the public Internet and your border
router. A border router is the device that sits at the edge, or border, of a network
between the company’s network and the Internet Service Providers (ISPs) network
and handles the routing of data between the public Internet and the private company
Internet. This allows the firewall to protect not only the internal network from the
Internet, but also the border router from the Internet. A network diagram is shown in
Figure. 1.1 and will be the network design that is referenced throughout this chapter.
In this sample network design, the Internet cloud is shown in the upper left. Connected
to that is the firewall device that protects the network. Connected to the firewall is
Email Server
Network Switch Database Server
Router
Firewall 204.245.12.0 192.168.0.0 Web Server
Figure 1.1 Basic Network Diagram