Microsoft SQL Server is becoming a more mature, more feature-rich, and more secure database management system with each new version. SQL Server 2012 is an enterprise-class relational database server. Sometimes, it might not look like it to the staff whose responsibilities are to deploy it, to create databases and write T-SQL code, and to administer it. Since, SQL Server is a Microsoft product, designed to be as easy to install and user friendly as possible, some of its users might not measure the importance of doing things right. The data stored in databases is the company's most precious thing. If a company loses its data, its business is gone and likewise if the data is stolen. We have heard many stories of customers or users whose databases were stolen from the Web. It has even happened to the biggest companies such as Sony (we will talk about Sony's case in this book).
Ok, it's obvious that securing your data is important. But how do you do it? SQL Server runs on Windows, so securing Windows is also involved; it is a client-server application, so securing the network is important; SQL Server needs to allow access to Windows accounts inside a domain, or to SQL Server defined accounts for Web and heterogeneous network access; it needs to read and write backup files that are secured and sometimes the data stored in SQL Server must be protected by encryption. This is a complex environment and securing it requires a set of skills and knowledge that we try to cover in this book in the most practical fashion. This is a cookbook, so all the subjects are presented as recipes, but security also requires knowledge about technologies and practices. You need to know what you are doing, therefore the recipes also contain more detailed explanations. It is also difficult to isolate recipes, so they might be related to each other. For example, in the chapter dedicated to authentication, the flow of recipes details how to create logins, and then how to create database users and map them to logins. So this cookbook can be helpful in two ways—you can pick the recipes you need for the task at hand, but you can also gain benefit by reading it cover to cover, helping you to master all that you need to know to effectively secure SQL Server.
Chapter 1, Securing Your Server and Network, presents all that you need to know to secure the system on which SQL Server runs, meaning Windows, the network, Windows Firewall, and the SQL Server service accounts.
Chapter 2, User Authentication, Authorization, and Security, covers authentication and authorization at the server and database levels. There is a precise hierarchy of authorization in SQL Server, based on server-level logins, database-level users, database schemas, and server and database users. We will also talk about the new SQL Server 2012 contained databases feature.
Chapter 3, Protecting the Data, delves into permissions, which is securing the database objects. You can project directly or by using roles and schemas, you can also use views and stored procedures to limit access to your data. You can also fine-tune cross database security.
Chapter 4, Code and Data Encryption, is about encrypting data and signing code using the encryption keys and algorithms offered by SQL Server. You will learn how to use keys and certificates to encrypt column values to sign your data, how to encrypt your entire database or your database backups, and how to use module signature to authenticate code across databases.
Chapter 5, Fighting Attacks and Injection, talks about security from the client code and T-SQL code perspective. If you are careless, it is easy to leave holes in your client code that could be used by attackers to gain access to your database server. This chapter shows you what the threats are and how to protect your data.
Chapter 6, Securing Tools and High Availability, explains that SQL Server is no simple database server; it comes with a set of tools and features that have their own security needs. In this chapter, we will cover securing SQL Server Agent, Service Broker, SQL Server Replication, and the mirroring and AlwaysOn functionalities.
Chapter 7, Auditing, is dedicated to keeping track of what happens on your server. You will learn what is available to keep track of what happens on the server and with your data, with triggers, SQL Server Trace, or SQL Server Auditing.
Chapter 8, Securing Business Intelligence, covers securing the Business Intelligence stack of SQL Server. These tools have a simpler security model and this chapter gives enough detail for you to effectively secure SQL Server Analysis Services, Integration Services, and Reporting Services.
This book covers Microsoft SQL Server 2012. All recipes dealing with interactions with the operating system assume that you are using Windows Server 2008 R2 Enterprise Edition and that your SQL Server is part of a Windows Server 2008 R2 Active Directory. You can easily adapt the recipes to another Windows version or edition, and what exists only in Windows Server 2008 R2 AD is pointed out in the recipes.
Some SQL Server tools and functionalities are available only in SQL Server Enterprise Edition. That's the case, for instance, with Transparent Database Encryption (TDE) and some levels of SQL Server Auditing. This will be mentioned in the recipes that present these technologies.
This book is written under the assumption that you are a DBA of some sort. Database Administrator might not be written on your business card, but you have at least some of the responsibilities of a DBA in your company. This book is mainly focused on the SQL Server relational engine. If you do only Business Intelligence, the last chapter is dedicated to it but the focus of all other chapters is the relational engine. Anyway, even if you do only BI, you might have some communication with the relational engine, and you probably need to know how authentication works in the relational engine.
If you are a programmer whose responsibilities are to write T-SQL code, and maybe to do light administration with SQL Server, you will also learn everything you need to know to help keeping SQL Server safe, mainly in Chapter 3, Protecting the Data, we will talk about permissions; in Chapter 4, Code and Data Encryptio, we will talk about encryption; and in Chapter 5, Fighting Attacks and Injection, we will talk about SQL injection.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: "The name of the service of a default instance is mssqlserver."
A block of code is set as follows:
SELECT OBJECT_NAME(m.object_id) as name, p.name FROM sys.sql_modules m JOIN sys.database_principals p ON m.execute_as_principal_id = p.principal_id;
Any command-line input or output is written as follows:
$username = "DOMAIN\Administrator" $password = "MyPassword" | ConvertTo-SecureString -asPlainText -Force
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: " If your SQL Server instance is already installed, you can access the service account properties using SQL Server Configuration Manager found in the Configuration Tools menu under Microsoft SQL Server 2012".
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.