Chapter 6. Security

As you learned in chapter 1, successful database administration involves designing and executing tasks that ensure that a database meets four key criteria: security, availability, reliability, and recoverability. This chapter is dedicated to the first of these criteria, security, and we'll address this topic from a range of perspectives.

Before we begin, it's important to note the difference between a secure environment and a convenient one. A convenient environment, in which developers, users, and database administrators are free to go about their business unhindered, is usually an insecure one that often ends in disaster, intentional or otherwise. In contrast, a secure environment is one in which complaints about "how long it takes to get stuff done" aren't uncommon. The key is striking the balance between security and productivity.

Designing and implementing a secure database environment is hard work. Trying to lock down an existing, insecure production environment almost always involves things breaking as part of the process. As a result, the job is often placed in the "too hard" basket, with systems left in an insecure state. The key to a good security model is prioritizing its development and implementation from the very beginning, and rolling out the model in all environments well before the production environment is installed.

Fortunately, there are some proven best practices in securing SQL Server, and they are the focus of this chapter. We begin with coverage of the various authentication models before looking at locking down network security and implementing least privilege. We then focus on some of the new security features in SQL Server 2008: Auditing, Change Data Capture, and Transparent Data Encryption. The chapter closes with a brief look at the dangers of SQL injection attacks.