Relational databases are used in an amazing variety of applications with connections from a dizzying array of clients over widely distributed networks, particularly the Internet, which makes data accessible to almost anyone, anywhere. Databases can hold a significant portion of human knowledge, including highly sensitive personal information and critical data that makes international commerce work.

These characteristics make databases attractive targets for people who want to steal data or harm its owner by tampering with it. Making sure that your data is secure is a critical part of configuring SQL Server and developing applications that use it to store data. This Stairway explores the basics of SQL Server 2012 security so that you can protect your data and server resources, getting as granular as you need to be to protect against the unique security threats that can affect your data. Much of the information will apply to earlier versions of SQL Server, going back to SQL Server 2005, because that is when Microsoft overhauled security in the product. But I'll also discuss features that are only in SQL Server 2012 and later.

SQL Server has everything you need to secure your server and data against today's sophisticated attacks. But before you can use these security features effectively, you need to understand the threats you face and a few basic security concepts. This first stairway level provides a foundation so that you can take full advantage of the security features in SQL Server without wasting time on features that do nothing to protect against specific threats to your data.

The Threats

Identifying the threats to a particular set of data and its server is an important first step in understanding how to configure and use SQL Server to protect your data. A database you create to manage your grade school soccer team's equipment inventory probably doesn't require heavy security measures. You'll probably want to provide at least minimal access control so that a team member can't just randomly change the record of who has which box of soccer balls. But it is probably not the end of the world if someone gets in and either steals or messes up the data.

On the other hand, if the database has personal data about the children on the team, such as home addresses and phone numbers, you'll probably want to step up security protections (and you may be legally required to do so). You might protect the privacy of the data by segregating access so that almost anyone with access to the database can change the equipment data but only a select few can access the personal data. If the data includes mom and dad's credit card number, you'll need to go to extreme lengths to protect that data.

There are a number of threats your data may be susceptible to, and the following list is a few of the more common ones. Plenty of resources are available on the Web that can help you analyze the risks for your specific situation. This list is intended to help you start thinking about threats and how to use the features of SQL Server to counter them or at least reduce your data's exposure to them.

• Theft of data: Theft of data covers various types of unauthorized access to your data, whether by an outsider hacking into your network or an insider scanning for dirt on famous people. It may involve the thrill of reading forbidden information or be motivated by the potential sale of stolen credit card numbers.
• Data vandalism: A hacker who gains access to your data can change it, which can cause a whole range of problems, from public embarrassment to shutting down your entire operation (which could happen when all of your customer records are deleted).
• Data corruption: One of the biggest benefits of storing data in a relational database is that the database itself can help protect the integrity of the data. Data integrity includes mandating that every order have an associated customer, that a date stored in a date field really represents a calendar date, and that a percentage field contains only values between 0 and 100. Data integrity probably isn't the first thing you think of in connection with security, but it is an important part of protecting your data.
• Illegal storage: In the past, the data you collected during the course of business was literally your own business. But now myriad federal laws exist in the U.S., throughout the European Union, and other countries that control the kinds of personal data you can store, how you store it, and how you protect it. The penalties for violations can be severe—both monetary penalties and damage to the public image of your company.

This stairway covers the features in SQL Server 2012 that help mitigate these kinds of threats and many others. You have to understand the threats to your data to know how to protect against them. Don't waste time on measures that don't protect against specific threats to your specific data. You'll never be able to cover all hypothetical situations, and at worst you'll make your database server completely unusable by its intended users. Security is always a compromise that balances the risks against the time and money necessary to implement and maintain safeguards.

Security Design Philosophy

Early in 2002 Bill Gates sent out his infamous Trustworthy Computing memo, which proved to be a turning point in how Microsoft thinks about and implements security in its products. The resulting Trustworthy Computing initiative, according to the Microsoft Web site, “focuses on creating and delivering secure, private, and reliable computing experiences based on sound business practices. Our goal is a safer, more trusted Internet.”

In other words, a bit over a decade ago Microsoft became very serious about security. The first version of SQL Server released after this change was SQL Server 2005, in which Microsoft completely overhauled security throughout the product. Since then, each version of SQL Server has retained that new security infrastructure while incrementally enhancing it and adding new features, both to improve security and to address new and emerging threats.

As Microsoft described it back in the days of SQL Server 2005, Trustworthy Computing influenced the development of SQL Server by defining four pillars of the product's security design:

• Secure by design: Microsoft performed extensive threat analysis and security audits of its design and code and interactions to identify points where an attacker could gain a toehold into the server and data. The result is that Microsoft designed SQL Server to protect the confidentiality, integrity, and availability of the data you store there.
• Secure by default: Out of the box, SQL Server installs and activates only critical core database components by default. This means that features that are not critical to the core database functions are either not installed or are installed but not activated. A feature that isn't installed is not susceptible to attack. You have to consciously and deliberately install or activate non-core features. This prevents many attacks on features you might not know were installed because you've never used them.
• Secure in deployment: Microsoft provides tools and support for installing SQL Server securely and keeping it secure. The SQL Server Configuration tool helps you configure the server securely. Best of all, updates to SQL Server are now available as part of the online Microsoft Update service, so it's easy to get security updates and patches.
• Secure through communication: Microsoft has set up an elaborate infrastructure to gather information about vulnerabilities in its products. But all of that information is worthless if it stays within Microsoft. So the company committed to communicating new vulnerabilities, aggressively patching to protect against them, and shipping regular updates to its Books Online Help system to reflect new security information.

This philosophy of “it's just secure” has implications throughout the product. But although SQL Server is fairly secure out of the box, as you create databases and install services you have to make wise security choices to keep the instance of SQL Server secure. It takes work and vigilance to make and keep a production database server secure.

And keep in mind that sometimes the best way to protect data is simply to not put it in the database—for example, only store credit card numbers if you have an absolutely valid need to hang on to that kind of information (and there aren't many of those). If you don't have such a need, you should process the credit card transaction and store the results, but not the credit card information itself. You can't suffer a security breech on data you don't have!

The Two Stages of Security

The security model of SQL Server, like that of Windows, is a two-stage process that allows a user or other login to access the protected resources within the server:

• Authentication: A user logs in, is validated, and gains access to the server. Authentication answers the question, “Who are you?” and requires the user to prove it, usually by providing the password that goes with a user name, but other forms of proof are becoming more popular.
• Authorization: A user is able to interact with only those SQL Server objects—such as databases, tables, and stored procedures—for which she has permission. Authorization answers the question, “What are you allowed to do?”

A user might be able to log in to SQL Server, but unless they have permission to do something, such as access a database, it won't do them much good. So you not only need to provide your users with authentication credentials, you also need to authorize them to access data by defining accounts for them in each database they need to use once they're authenticated.

When you think about it, requiring a user account in each database makes a lot of sense. Otherwise, what would prevent a user from logging in to SQL Server and then using any database they want? There are variations of this concept to accommodate various scenarios, but this is the basic state of security.

Later levels in this stairway cover both authentication and authorization, and you'll learn how to implement various security features in SQL Server to protect against the threats you've identified to your data.

SQL Server Security Terminology

As you work with security in SQL Server and other products, you'll encounter various specialized terms. Here are a few of the more common terms and what they mean in the context of database security.

Authentication: As mentioned above, authentication is the process of positively identifying a person by requiring her to prove that she is the person associated with a login. It answers the question, Who are you?

Authorization: Once the system authenticates a user, authorization (as described above) determines the permissions that the user has within the server or database. This answers the question, What can you do now that you're here?

Group: In Windows, a group is a principal that has logins associated with it. Any permissions granted to the group are granted to the associated logins.

Impersonation: All Windows processes, including the various SQL Server processes, run in a particular security context, usually that of the principal who caused the process to start. Impersonation occurs when a process temporarily takes on a different security context. This is a very powerful and necessary ability, but comes with the potential of abuse.

Login: A login is a principal that has some level of access to objects in a server instance. In common usage, login is often used interchangeably with user. But a SQL Server login is an account used to access a server from outside. A login sometimes includes rights to access server-wide objects, such as configuration information, but does not normally confer any rights in a database.

Permission: A permission is a right to access a protected resource, such as to read data from a table or to create new databases at the server level. A permission often implies other permissions, depending on the scope of the subject permission.

Principal: A principal is any user or code component that can receive permissions to access protected resources in SQL Server.

Privilege: A privilege is the broad set of rights or permissions that a principal has. This word is sometimes used interchangeably with permission, which more often means a specific, narrow right. Privilege implies a set of broader permissions.

Role: A SQL Server role is analogous to a Windows group, but is limited to the confines of the SQL Server instance. Like a group, you can assign logins and users to a role, which conveys to the logins and users all permissions that the role has.

User: A user is a principal that has some level of access to objects in a particular database. A user is often mapped to a login. In simple terms, a login has access to a SQL Server instance, and a user has access to data.

You'll see most of these terms used throughout this stairway.

Managing and Implementing SQL Server Security

As with most management and administrative tasks in SQL Server, there are almost always multiple ways to manage and implement security features, including using the graphical interface of Management Studio, writing and executing T-SQL code, and using SQL Server's integration with PowerShell for the command line junkies among us. Using PowerShell is outside the scope of this stairway, but throughout the stairway you'll see numerous examples of using the other two techniques. (To learn about the close integration between PowerShell and SQL Server, check out the Stairway to SQL PowerShell.)

To use Management Studio for security features, most often you'll just right-click an appropriate object in in Object Explorer and select Properties, then use the appropriate page in the dialog box to make the changes you want. For example, to set permissions on the AdventureWorksLT2012 sample database, right-click the database name in Object Explorer, select Properties, and then select the Permissions tab, as shown in Figure 1.1.

Figure 1.1: Permissions page for modifying the security of the AdventureWorksLT2012 database.

At both the database and server instance level, Object Explorer includes a Security node that lets you manage and implement other kinds of security features. Figure 1.2 shows this node for the AdventureWorksLT2012 database and at the server instance level. Each of the two highlighted nodes contains various child nodes that give you access to security objects appropriate to the scope of the selection.

Figure 1.2: Security objects at the Server and Database level in Object Explorer.

You'll find various security features tucked away throughout Management Studio and its various windows, so it is worthwhile to do some exploring as you come across them.

One of the changes introduced with the security overhaul in SQL Server 2005 was to allow permissions to be assigned in far more granular ways. As you'll learn in later levels, you can assign many kinds of permissions to a variety of principals in order to implement the important security principal of least privilege—the idea that each principal has permissions to do exactly what they need to do: no more and no less. Along with that change, Microsoft beefed up T-SQL to include strong support in the language for directly manipulating security objects.

The code in Listing 1.1 shows a simple example of the T-SQL you can use to create security objects. The code starts by creating a server-level login linked to an existing Windows login. Then, in the AdventureWorks2012 database, the code creates a user mapped to the login, as well as assigning a default schema to the user. And the last bit of code shows how you can drop a user in the database and create another user mapped to the same login. Very contrived, of course, but it shows what you can do in code. Or in Management Studio's GUI tools, if you prefer.

-- Add a Windows login to SQL Server
CREATE LOGIN [Marathon\JoeStairway] FROM WINDOWS;
GO
USE AdventureWorks2012;
GO
-- Name the user the same name as login
CREATE USER [Marathon\JoeStairway] FOR LOGIN [Marathon\JoeStairway]
    WITH DEFAULT_SCHEMA = Production;
GO
-- Or, rename the user in the database
DROP USER [Marathon\JoeStairway];
GO
CREATE USER Jane FOR LOGIN [Marathon\JoeStairway];
GO
-- Query metadata to show that user was created
SELECT * FROM sys.database_principals WHERE name = 'Marathon\JoeStairway';
SELECT * FROM sys.database_principals WHERE name = 'Jane';

Listing 1.1: Sample code for creating a server login and database user.

If you want to try running this code, there are a few changes you're likely to need first. Unless you're on a machine named Marathon and have an existing user name JoeStairway, you'll need to change those names in the code. The code creates the database user in AdventureWorks2012 and uses its Production schema, so you'll either need to have that database installed or change the code to use another database and an existing schema in it. But you'll probably want to install the AdventureWorks suite of sample databases, since this Stairway will make frequent use of them.

Summary

This first level of the stairway has provided an overview of the basic security concepts in SQL Server 2012. You've learned about some of the more common threats to data, and explored the design philosophy behind SQL Server security. You learned about the two stages of security – authentication and authorization, learned a few security terms that you'll see throughout the stairway, and saw that you can manage and implement security both through Management Studio's GUI tools as well as in T-SQL code.

In the next level, you'll learn more about how authentication works in SQL Server and the authentication options available.