C H A P T E R 1 4
■ ■ ■
327
SQL Server Security
Understanding the SQL Server security model and how to effectively implement and manage a secure
SQL environment is important for a DBA. If your employer’s data gets compromised, it’s not just your
job that is lost; it could also affect the lives of many people outside your company. You have probably
heard stories of hackers obtaining credit card information for thousands of helpless consumers. All these
thefts could have been prevented had proper security measures been in place. SQL Server comes with a
well-tested security model that enables DBAs to delegate access control from the server level down to
the column level and, with the help of native encryption capabilities, to the individual column entries. In
this chapter, you will learn how users are authenticated and authorized within SQL Server, and you’ll
learn some best practices to follow with respect to the security of your SQL Server instances.
Terminology
Before we dive deep into the security topic, it’s important to first define some key concepts. To help in
this discussion, imagine the scenario where you want to access your valuables that are in a safety deposit
box. Safety deposit boxes, if you’re not familiar, are metal boxes of different sizes that store valuables
and are located in a vault at a bank. They usually require a key to open them. In addition, a bank will
often ask for identification before allowing you to get your box.
Authentication
The bank needs to make sure you are who you claim you are. They do this by asking for identification,
traditionally a driver’s license. This action is called authentication. SQL Server makes sure you are who
you claim you are by asking for credentials. You give SQL Server credentials in one of two ways. You can
give SQL Server a username and password combination. SQL Server will take a hash of the password you
supplied and compare it with a hash of the password that is stored in its internal tables. If the two hashes
match for the given username, you are authenticated. This type of authentication is known as SQL
Server authentication.
The other way to authenticate to SQL Server is through Windows authentication. With this type, you
do not need to type in a password; rather, since you are logged into Windows, SQL Server asks Windows
to verify your identity via your Windows security token.
