SQL Server 2012 : SQL Server Security Overview

Four Themes of the Security Framework

SQL Server 2012 security is organized around four themes: Secure by Design, Secure by Default, Secure by Deployment, and Secure Communications.

Secure by Design

Security has been a design consideration in all earlier versions of SQL Server. As of SQL Server 2005, the product development group made sure that everyone was on the same page when it came to security. The entire product team went through mandatory security training, and threat models were written and reviewed for all components of all the features within the product. In addition, a massive effort was carried out to review code with respect to security for the entire product. Microsoft takes security very seriously, and designers of features within SQL Server have made security a top consideration in the final design.

Secure by Default

The Secure by Default approach is one of the most notable areas of the Security Framework that SQL Server users will experience. You can experience it by simply installing SQL Server with the default options. Users of SQL Server 2000 and earlier versions will notice that services such as SQL Server Agent are off by default. In addition, certain features such as xp_cmdshell and OPENROWSET queries are disabled. This “off by default” approach attempts to minimize the surface area for attack, and the effects of this can be seen throughout the product.

Secure by Deployment

Perhaps one of the most challenging issues with SQL Server is effective deployment in a production environment. With so many different configurations and features, it can be difficult for administrators to keep on top of the latest updates and best practices. SQL Server 2012 is now part of Microsoft Update to help alleviate the pain of determining the latest patch to apply.

Secure Communications

Even before SQL Server 2005 was released to the public, a plethora of technical information was already available in various forms. White papers, webcasts, and active newsgroups educated and assisted beta customers with the product. Today, most of these webcasts and white papers have been updated and provide rich educational content.

All editions of SQL Server include security features that help users protect their data.

• Overview of security, including authentication and authorization

• User-schema separation

• Encrypting data within the database and while in transit

• Auditing monitored server and database events

• Protecting SQL Server

Reducing the Surface Area for Attack

The more doors you have in your house, the more opportunities an intruder has to break in. Even if those doors are made of solid steel, each with a nice shiny lock, they are still considered a surface area that is vulnerable for attack. One obvious way to eliminate this vulnerability is to not have a door where you don’t need one. In a computer application, the equivalent solution is that if you are not using a particular feature, you should turn it off if possible.

Out of the box, SQL Server provides a reduced surface area for attack by automatically turning off features that are optional. These features include the SQL Server Agent service, SQL Server Browser service, various server functions including xp_cmdshell, common language runtime (CLR) integration, and others.

In SQL Server 2005, a system stored procedure named sp_configure was introduced that could be used to turn many of these features on and off programmatically. SQL Server 2005 also introduced a graphical management utility named the SQL Server Surface Area Configuration Tool. This utility allowed administrators to turn these features on and off from a user interface. As of SQL Server 2008, the sp_configure stored procedure remains, but the Surface Area Configuration Tool has been replaced by the Surface Area Configuration facet in the Policy Based Management Framework (PBM).

PBM was introduced in SQL Server 2008 to declare and enforce different management policies against various target environments. Functionality formerly provided by the Surface Area Configuration Tool in SQL Server 2005 is now one of many PBM facets that can be configured in SQL Server 2012.

SQL Server Security Overview

If you are already familiar with the concepts of users and roles and SQL Server logins. But for those who aren’t, we’ll provide a quick explanation. The concepts of users and roles exist both in the Microsoft Windows world and in SQL Server. In an abstract sense, they are referred to as principals. Principals are entities that can request resources from the application or an operating system. In the case of Windows, principals are entities such as Domain Users and Groups, and Local Users and Groups. In SQL Server, these entities are logins and server roles. Within a database, these entities are database users, database roles, and application roles, to name a few.

So what can you do with entities? Chances are you have an object such as a file or a database table that you want to allow access to. These objects, or securables, are the resources to which the authorization system regulates access. Some securables can be contained within other securables, creating nested hierarchies called scopes that can themselves be secured. The securable scopes in the SQL Server Database Engine are server, database, and schema. Every securable in SQL Server has an associated permission that can be granted to a principal.

Figure 1 shows a graphical representation of the principal-securable-permission model.

Figure 1. Principal-securable-permission model.

SQL Server Logins

Now that you have a high-level understanding of SQL Server security and how it relates to Windows security, we can get into SQL Server–specific areas. To connect to SQL Server, you need a login. This login can be a combination of a custom user name (such as Login1) and a complex password. Alternatively, you can add an existing Windows account as a login to SQL Server and forego the creation of a separate user name and password. Thus, SQL Server supports two types of logins: Windows logins and SQL Server logins.

Logins themselves have no access to any specific database within SQL Server; they allow only for connection to a SQL Server instance. Thus, logins are entities that can be granted server-wide permissions to perform certain actions. These actions are bundled into server roles such as sysadmin, diskadmin, and dbcreator (to name a few). Table 1 shows the list of server roles and their corresponding functions. Server roles are fixed—you cannot drop them or create new ones in addition to the set of nine fixed server roles.

Table 1. Fixed server roles.

Server Role	Description
bulkadmin	Members can run the BULK INSERT statement. Membership in this role still requires that non-sysadmin users have access to the object being updated.
dbcreator	Members can create, alter, drop, and restore any database.
diskadmin	This role is used for managing disk files. Most of the capabilities relate to adding and removing backup devices.
processadmin	Members can terminate processes that are running in an instance of SQL Server. This role is useful if you want to give someone the ability to kill a long-running query or an orphaned connection.
public	All valid SQL Server logins are members of the public role.
securityadmin	Members can manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions as well as database-level permissions. They can also reset passwords for SQL Server logins. This role has no rights to assign database permissions. If you want securityadmin members to be able to do this, you must make their logins part of the db_accessadmin fixed database role for the specific database.
serveradmin	Members can change server-wide configuration options and shut down the server.
setupadmin	Members can add and remove linked servers and also execute some system stored procedures.
sysadmin	Members can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator’s group, are members of the sysadmin fixed server role. The SQL Server service account is also a member of this role.

Database Users

Logins can be created using SQL Server Management Studio (SSMS) or via the Transact-SQL (T-SQL) statement CREATE LOGIN. After you create a login, you then grant it access to a particular database. Databases themselves have their own set of roles that define specific access and actions that users of these roles can take within a particular database. Before you can grant database access to a login, you must create a database user for the login. Again, you can create a database user by using SSMS or via the T-SQL statement CREATE USER.

Once you create a database user, you can optionally include it in one of the database roles. Table 2 lists the roles that all databases have. Like server roles, these database roles are fixed and cannot be modified. However, unlike server roles, you can define additional database roles as desired.

Table 2. Fixed database roles.

Database Role	Description
db_accessadmin	Members can add or remove access for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator	Members can back up the database.
db_datareader	Members can read all data from all user tables.
db_datawriter	Members can add, delete, or change data in all user tables.
db_ddladmin	Members can run any data definition language (DDL) command in a database.
db_denydatareader	Members cannot read any data in the user tables within a database.
db_denydatawriter	Members cannot add, modify, or delete any data in the user tables within a database.
db_owner	Members can perform all configuration and maintenance activities on the database, including dropping the database.
db_securityadmin	Members can modify role membership and manage permissions.

There is a special database role that sysadmin users cannot explicitly give other users permissions to; it is known as the public role. All database users are implicitly included in the public role. This role captures all default permissions for users in a particular database. It cannot have users, groups, or roles assigned to it because everyone belongs to this role by default. This role cannot be dropped. Thus, to protect against unauthorized data access, you should minimize the permissions granted to the public role. Instead, grant permissions to other database roles and to user accounts associated with logins.

The guest User Account

On the topic of unauthorized data access, it is important to note a special user account available in SQL Server called guest. This account is created by default on new user-defined databases but does exist in master and tempdb. However, the guest account is disabled by default, which means that it does not have any access within the database. The guest account allows a login without a user account to access a database. A login assumes the identity of the guest account when all of the following conditions are met:

• The login has access to an instance of SQL Server but does not have access to the database through his or her own user account or via a Windows group membership.

• The database contains a guest account.

• The guest account is enabled in the database.

You can apply permissions to the guest account just as you can any other user account. If possible, however, you should avoid using the guest account entirely, because all logins without their own database permissions obtain the database permissions granted to the guest account. If you absolutely must use the guest account, be sure to grant minimum permissions to it.