Chapter 7. SQL Server 2005 Security

IN THIS CHAPTER

• An Introduction to Security in SQL Server 2005

• Authentication

• Authorization

• Auditing Enhancements

• Cryptography Support

• Surface Area Configuration (SAC)

• SQL Server Agent Security Overview

• SQLCLR Security Overview

• Service Broker Security Overview

On January 24, 2003, a worm named Sapphire or W32.Slammer attacked the computers running SQL Server 2000 and MSDE 2000, propagating via UDP Port 1434 and causing a dramatic increase in network traffic. This incident reinforced the importance of the ongoing Trustworthy Computing initiative. In the past two years, Microsoft has invested significant time and resources in enhancing the security in each of its products, including SQL Server 2005.

SQL Server 2005 redefines the server’s security framework by introducing several new features and enhancements that offer developers and administrators greater control over access to sensitive data while simplifying their routine tasks. A lot of work has been done to address weaknesses in previous releases, to simplify security management and administration, to strengthen SQL Authentication, to provide granular permissions, to secure system metadata, to improve auditing capabilities, and to secure various subsystems. This chapter discusses these security enhancements to assure you that SQL Server 2005 is secure by design, secure by default, and secure in deployment.

An Introduction to Security in SQL Server 2005

The security features in SQL Server 2005 fall into five categories:

• Authentication

• Authorization

• Auditing

• Cryptography Support

• Surface Area Configuration (SAC)

In addition, each new subsystem is designed with security in mind. For instance, SQLCLR security features help ensure that the .NET CLR code running inside SQL Server is secure and reliable; Service Broker provides multiple layers of security to dialog messages by authenticating and authorizing dialog initiators and targets, by preserving the message integrity, and by securing the transport; and Reporting Services security follows the role-based security model to determine who can perform operations and access items on a report server.

This chapter covers the new security features in each of the five categories and also provides an overview of security features in new subsystems such as SQLCLR and Service Broker. Detailed discussions on subsystems’ security features can be found in later chapters.

Let’s begin by looking at new security features in the authentication arena.

Authentication

Authentication is the process of validating that someone is who they say they are. In other words, authentication is the process of verifying someone’s identity based on things like login name and password, security token, or certificate.

Like previous releases, SQL Server 2005 continues to support two authentication modes: SQL Authentication and Windows Authentication. The latter is still the recommended way of connecting to SQL Server. The authentication model in SQL Server is enhanced to strengthen the SQL Authentication mode. Before we look at this, let’s first look at two new security features related to how a connection is made to a SQL Server instance: endpoint-based authentication and the role of the SQL Browser service.

Endpoint-Based Authentication

SQL Server 2000 allows clients to connect over network protocols such as TCP/IP and named pipes. The only application-level protocol supported by SQL Server 2000 is the Tabular Data Stream (TDS), which is used to send SQL statements from the client to the server and result sets from the server to the client. The TDS packets are encapsulated in the packets built for the underlying network protocol, such as TCP/IP packets. SQL Server 2005 extends this framework by introducing the concept of endpoints.

An endpoint can be thought of as an entry point into a SQL Server instance. You can create multiple endpoints per instance by using the CREATE ENDPOINT Transact-SQL (T-SQL) statement. When you create an endpoint, you have to specify what transport protocol it will be accessed over and what kind of payload it will accept. For instance, you can create an endpoint to work with TDS payload over the TCP/IP network protocol. In other words, an endpoint binds a transport protocol to payload.

The benefit of this approach is that you can manage permissions on a per-endpoint basis. Endpoint security comes in the form of permissions such as CONNECT that can be granted, revoked, or denied to database users. For instance, using endpoints, you can implement a scenario where certain users can access SQL Server over named pipes or shared memory, but not over HTTP, a transport protocol that is now supported by SQL Server 2005. SQL Server 2005 introduces support for HTTP, which can work with XML/SOAP payload and can be used to implement XML web services or Service Oriented Architecture (SOA) in SQL Server 2005. This is discussed in more detail in Chapter 10, “XML and Web Services Support in SQL Server 2005.” In addition, an endpoint can also be created to implement database mirroring and work with Service Broker. Database Mirroring is discussed in Chapter 8, “Reliability and High Availability in the Database Engine,” and Service Broker is discussed in Chapter 14, “SQL Server 2005 Service Broker.”

The other benefit with the endpoint approach is that you can start, stop, or disable an endpoint to have SQL Server listen or not listen on that endpoint. When you create an endpoint and do not specify the STATE = STARTED clause, for security reasons the endpoint is stopped by default.

When SQL Server 2005 is installed, it creates an endpoint for every enabled transport protocol, such as named pipes, TCP/IP, shared memory, and so on. Each authenticated login is assigned the CONNECT endpoint permission on these endpoints, which can be denied on a per-endpoint basis. For security reasons, SQL Server 2005 does not create any HTTP endpoints out of the box. If you need to enable XML web services in SQL Server 2005, you can create HTTP endpoints by using the CREATE ENDPOINT statement.

With the endpoint-based authentication model, when a user connects to a SQL Server instance, in addition to verifying the login credentials, SQL Server also verifies that the login is authorized to connect via the endpoint.

SQL Browser Service and the New Security Model

SQL Server 2000 enables up to 16 instances to be installed on a machine. SQL Server 2005 raises this number to 50. When a client tries to connect to a named instance or an instance listening on a non-default static or dynamic port, the client has no knowledge of where to connect. The instance resolution protocol in previous SQL Server releases was an answer to this problem. With this approach, a client could connect to the SQL Server machine over UDP port 1434 and enquire about all the instances listening on that machine. This open port 1434 was exploited by hackers, and this led to the W32.Slammer attack described at the beginning of this chapter.

This functionality of translating an instance name passed in from the client to the appropriate TCP/IP port or pipe is now separated into a Windows service called SQL Browser. In SQL Server 2005 you can stop or disable the SQL Browser service to turn off the UDP 1434 traffic instead of relying on the firewall.

When the SQL Browser service is running and clients query over UDP 1434, the SQL Browser service returns a list of SQL Server machines (SQL Server 7.0 and above, clustered and non-clustered), Analysis Server machines, Report Server machines, and SQL Mobile databases, along with version numbers for all the servers.

When the client knows about the TCP/IP port or the named pipe to connect to, the next step is login authentication. For Windows logins, the token from the domain controller is presented to the SQL Server machine, and for SQL logins, the login name and the password hash are presented to the SQL Server machine. After establishing the login credentials, SQL Server 2005 ensures that the login has access over the endpoint through which it is trying to connect. When this is verified, SQL Server switches to the database to obtain the user context, which is then used for database permissions for the most part.

In summary, SQL Server 2005 separates the functionality of listening over UDP 1434 and responding to anonymous clients with instance information or mapping instance names into TCP/IP port or named pipe into a separate service called SQL Browser, which can be turned on or off to control the UDP 1434 traffic. And the notion of endpoints allows you to control access to the server based on transport and payload, as well as the ability to start, stop, or disable individual endpoints to better control access to the server.

Let’s now look at how SQL Server strengthens the SQL Authentication mode.

Strengthening SQL Authentication

Windows Authentication is still the recommended approach for connecting to SQL Server because it can leverage the domain password policy and other security infrastructure and is based on NTLM and Kerberos protocols, which are much stronger than the SQL Authentication. However, Microsoft recognizes that customers may need to continue to use SQL Server authentication. Hence, SQL Server 2005 strengthens the SQL Authentication to make it more secure. The following are some of the limitations of SQL Authentication in SQL Server 2000:

• When clients connect to a server by using SQL Authentication, the channel is not secured, and anyone can figure out the login and password used to connect to SQL Server. The only way to secure the client/server channel is to install a certificate from the Certificate Authority on the server and client machines and then use Super Socket Net-Library (Dbnetlib.dll and Ssnetlib.dll) to encrypt the communication. In other words, the channel is not secure by default; extra administrative steps have to be performed to secure it.

• It is not possible to enable or disable logins.

• SQL Server 2000 does not support any password policy or account lockout or expiration rules.

SQL Server 2005 fixes these limitations. SQL Server 2005 contains built-in support for securing the channel for SQL Authentication. It first checks whether an SSL certificate is installed for the server communication, and if a certificate exists, SQL Server 2005 uses it to secure the communication. If not, SQL Server 2005 automatically generates a 512-bit certificate that it uses to encrypt the credentials. Note that you don’t have to make any changes in your SQL Server 2005 applications to use this feature, and SQL Server 2005 continues to provide support for old-style authentication for down-level clients.

SQL Server 2005 introduces a new DDL statement, ALTER LOGIN, that can be used to enable or disable a login.

Now let’s look at the new password policy feature in SQL Server 2005 that fixes the third limitation mentioned earlier.

Password Policy Support

Windows network administrators almost always use the following security features to secure the domain logins and thereby in some way secure their networks:

• Enforce password policies to ensure that domain users make use of strong passwords.

• Enforce account lockouts so that if someone tries the brute-force approach to breaking in using a particular login, that account gets locked out or disabled after a certain number of failed tries.

• Enforce password expiration so that domain users change their passwords periodically.

• Force users to change their passwords on first login.

All this functionality is now available for SQL Authenticated logins. SQL Server 2005 makes use of the Windows 2003 Server API to enforce the Windows password policies of the computer on which SQL Server is running on the SQL Authenticated logins. Because this API is not available on Windows 2000 Server, SQL Server 2005 only supports native password complexity. The native password complexity feature disallows blank or null password and certain words, such as password, admin, sa, administrator, sysadmin, the name of machine, or the same string as the login name.

The sp_addlogin system stored procedure for creating new SQL Server logins is being deprecated in SQL Server 2005. Using the new DDL statement CREATE LOGIN is the recommended approach for creating new logins. For each new SQL Authenticated login that you create, SQL Server by default enforces the password policy.

There are three important options with the CREATE LOGIN DDL statement for SQL authenticated logins:

• CHECK_POLICY—ON by default, this option enables the Windows password and lockout policy on SQL logins. For security reasons, it is not recommended to set this option to OFF. Turning off the CHECK_POLICY option puts you back to SQL Server 2000 behavior, where SQL Server does not enforce password complexity or lockout rules. The account lockout mechanism ensures that if there are X number of bad password attempts within Y window of time, the account is locked out for a Z period of time, where X, Y, and Z are the parameters controlled by the policy. It is important to note that the CHECK_POLICY option controls both password complexity and account lockout. Just password complexity is not sufficient to protect against brute-force attack.

• CHECK_EXPIRATION—ON by default, this option specifies whether the password expiration policy should be enforced on the SQL login being created. If you set the CHECK_POLICY option to OFF, the CHECK_EXPIRATION option is also turned off. In other words, you can enable CHECK_POLICY without enabling CHECK_EXPIRATION, but you cannot enable CHECK_EXPIRATION without enabling the CHECK_POLICY option. CHECK_EXPIRATION and CHECK_POLICY are enforced only on Windows 2003 Server and above. On other operating systems, SQL Server 2005 supports only native password complexity.

• MUST_CHANGE—This option is only supported on Windows Server 2003, and when it is specified, SQL Server prompts the user for a new password when the login is used to connect to SQL Server. If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the CREATE LOGIN statement will fail.

The is_policy_checked and is_expiration_checked fields in the sys.sql_logins catalog view can be used to determine whether the CHECK_POLICY and CHECK_EXPIRATION options are ON or OFF. This catalog view derives from the sys.server_principals view, which contains fields such as is_disabled, which can be used to figure out whether the account is enabled or disabled.

In addition, you can associate an existing credential with a SQL Authenticated login. SQL Server 2005 uses such credentials when connecting outside the server. The credential can be created by using the CREATE CREDENTIAL DDL statement. One credential can be associated with multiple logins, but a login can have only one credential associated with it.

When specifying the password for SQL Authenticated login, you can use the HASHED option to tell SQL Server that the password string is already hashed and SQL Server should store the string as it is in the database, without hashing it again.

The following is an example of using the CREATE LOGIN and ALTER LOGIN statements to create and alter SQL Authenticated logins:

USE master;
GO

CREATE LOGIN Bob WITH PASSWORD = 'Bob';
GO
CREATE LOGIN Bob WITH PASSWORD = 'Admin';
GO
CREATE LOGIN Bob WITH PASSWORD = 'Admin',
   CHECK_POLICY = OFF,
   CHECK_EXPIRATION = ON;
GO

CREATE LOGIN Bob WITH PASSWORD = 'Admin', CHECK_POLICY = OFF;
GO
CREATE LOGIN Rob WITH PASSWORD = 'WjkKWjkF', CHECK_EXPIRATION = OFF;
GO

ALTER LOGIN Rob DISABLE;
GO

SELECT * FROM sys.server_principals;
SELECT * FROM sys.sql_logins;
GO

DROP LOGIN Bob;
DROP LOGIN Rob;

The first three CREATE LOGIN statements in this script fail because the first two statements do not specify the strong password, and the third statement tries to keep password expiration on without keeping policy check on, which is not allowed.

The fourth CREATE LOGIN statement succeeds even though it is using a weak password string because it turns off the CHECK_POLICY option. The next login creation succeeds, too, because it uses a strong password and requests to turn off password expiration, while keeping policy check on, which is allowed. The ALTER LOGIN statement illustrates disabling a login.

This concludes the discussion on authentication-related security features in SQL Server 2005. Let’s now look at authorization-related enhancements introduced in SQL Server 2005.

Authorization

After SQL Server has authenticated the user and has verified that someone trying to access the SQL Server resource is who they say they are, the next step is to authorize the user to determine what resource can or cannot be accessed and what actions can or cannot be performed by that user. The authorization process essentially verifies the permissions and access rights granted to the user.

SQL Server 2005 introduces several interesting features in the authorization arena, as you will learn in this section. However, let’s first look at some terms introduced in SQL Server 2005.

SQL Server 2005 Security Terminology

The following are some important terms in SQL Server 2005:

• Principal—Principal is a generic term that can be used to refer to an individual Windows login or a Windows group, a SQL login, a database user, an application role, or a database role, which is used for authentication and authorization purposes in SQL Server. The sa SQL Server login and BUILTINAdministrators Windows group are examples of principals. Each principal has a unique SID. The sys.server_principals and sys.database_principals catalog views can be used to view a list of server-level and database-level principals, respectively.

• Securable—Securables are items like endpoints, databases, the Full-Text catalog, Service Broker contracts, tables, views, functions, procedures, and so on that you can secure at the server level, database level, or schema level.

• Grantor—The grantor is the principal that grants a permission.

• Grantee—The grantee is the principal to whom the permission is granted.

Let’s now look at how SQL Server 2005 simplifies the management of permissions and incorporates support for the principle of least privileges.

Granular Permissions Control

SQL Server 2000 provided the ability to manage statement-level permission for a few statements such as CREATE DATABASE; object-level permissions such as SELECT, INSERT, DELETE, REFERENCES, or UPDATE on objects; and permissions based on fixed server-level and database-level roles. The problem with fixed roles is that they do not follow the principle of least privileges. For instance, with SQL Server 2000, a user has to be a member of a sysadmin fixed server role to run the Profiler or SQL trace. However, when you make someone part of sysadmin, that person has full control of the server and can do whatever he or she chooses to. According to the principle of least privileges, if you want grant permission to a user to perform an action, you should be able to grant the permission just for that action—nothing more, nothing less. This is what SQL Server 2005 is trying to achieve by introducing several new permission verbs and allowing you to grant permissions at multiple scopes. SQL Server 2005 provides granular permissions at the server level, database level, schema level, object level, and principal level. The SQL Server 2005 security model is hierarchical. For instance, if you grant some permission at a schema level, the principal will get that permission on all objects in the specified schema.

Examples of server-scoped permission include permissions to run a trace or create endpoints. Examples of database-scoped permissions include permissions to create tables, views, procedures, functions, a Service Broker queue, contracts and services, synonyms, schemas, XML Schema collections, and so on. Examples of schema-scoped permissions include permissions to alter any objects in a particular schema, execute any procedure or function in a particular schema, and so on. Examples of object-scoped permissions include permissions to alter a specific table, view, procedure, function, or synonym. SQL Server 2005 not only allows assigning permissions to a principal (such as a login or user), but it also defines a set of permissions on principals itself, and these permissions can in turn be granted to other principals. For instance, you can grant IMPERSONATE permission on login X to the other login Y, allowing the login Y to impersonate and run commands as login X.

Permissions in SQL Server 2005 are still managed using the familiar T-SQL constructs GRANT, DENY, and REVOKE. The GRANT statement gives permission to perform the action, DENY prevents from performing the action, and REVOKE brings to the original “unassigned” state by removing the granted or denied permission. SQL Server 2005 extends these statements and introduces several new permission verbs, such as CONTROL, IMPERSONATE, ALTER TRACE, and ALTER SERVER STATE, to provide granular control over managing permissions at multiple scopes.

Here is the syntax of a simple GRANT statement:

GRANT < permission > [ ON < scope > ]
        TO < principal >

< permission >::=  ALL [ PRIVILEGES ] | permission_name
    [ ( column [ ,...n ] ) ]

< scope >::= [ securable_class :: ] securable_name

< securable_class >::= APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY
    | CERTIFICATE | CONTRACT | DATABASE | ENDPOINT | FULLTEXT CATALOG
    | LOGIN | MESSAGE TYPE | OBJECT | REMOTE SERVICE BINDING | ROLE
    | ROUTE | SCHEMA | SERVER | SERVICE | SYMMETRIC KEY | TYPE
    | USER | XML SCHEMA COLLECTION

< principal >::= Windows_login | SQL_Server_login
    | SQL_Server_login_mapped_to_certificate
    | SQL_Server_login_mapped_to_asymmetric_key
    | Database_user | Database_role | Application_role
    | Database_user_mapped_to_certificate
    | Database_user_mapped_to_asymmetric_key

Before looking at examples of the GRANT statement, let’s look at some of the new permission verbs introduced by SQL Server 2005:

• CONTROL—With CONTROL permission, the grantee can do everything that the owner can do. For instance, if you grant CONTROL permission on a schema to someone, the grantee can do everything that the schema owner can do. However, note that you are only giving owner-like permissions to the grantee; you are not making the grantee the owner. You can use CONTROL permission to implement a subtractive model, where you start with maximum, owner-like permissions and then take away certain specific permissions.

• ALTER—The ALTER permissions gives the grantee the ability to alter the properties of a particular securable and a securable contained within that scope. For instance, ALTER on a procedure gives the user permission to change the procedure text, and ALTER on a schema gives user permission to create, alter, and drop objects from the schema.

• ALTER ANY <server securable> | <database securable>—This permission grants the ability to create, alter, and drop individual instances of server securables, such as logins, endpoints, and so on, or database securables, such as schemas.

• TAKE OWNERSHIP—This verb is yet another great example of granular permission control in SQL Server 2005. In previous releases, you had to be a member of the sysadmin server role to change the database owner, and you had to be a member of the sysadmin server role or both the db_ddladmin and db_securityadmin database roles in order to change the object ownership. With SQL Server 2005, if you are assigned TAKE OWNERSHIP permission, you can take the ownership of the securable on which it is granted.

• IMPERSONATE <login> | <user>—This is one more example of granular permission control in SQL Server 2005. Previously, only the members of the sysadmin server role or the db_owner database role could use SETUSER to impersonate another user. Now, if you are assigned IMPERSONATE permission, you can impersonate a login or user by using the EXECUTE AS and REVERT statements.

SQL Server 2000 provided the db_datareader and db_datawriter database roles, which allowed a user to read from or write to any table in the database. However, if you wanted to give EXECUTE permission on all the stored procedures and functions in the database, it was required to execute a GRANT statement for each stored procedure and function. There was no role called db_procexecutor that allowed executing any procedure in the database. The hierarchical permission model in SQL Server 2005 fixes this problem by allowing you to grant EXECUTE permission at the schema level or database level to allow users to execute any procedure within the specified schema or database, respectively. You can create your own role by using the CREATE ROLE statement and then grant EXECUTE permission to that role. Now, whoever is part of that database role will have EXECUTE permissions on the stored procedures.

Let’s now look at some examples of granular permission control and the hierarchical permission model in SQL Server 2005. Let’s begin by creating two users in the AdventureWorks sample database:

USE AdventureWorks;
GO
CREATE LOGIN Test1 WITH PASSWORD = 'PWD1';
GO
CREATE USER Test1;
GO
CREATE LOGIN Test2 WITH PASSWORD = 'PWD2';
GO
CREATE USER Test2;
GO

These lines create the logins Test1 and Test2 and add them as users in the AdventureWorks sample database. Let’s now grant some permissions to user Test2:

GRANT CONTROL ON OBJECT::Person.Address TO Test2;
GRANT SELECT  ON SCHEMA::Person TO Test2;
GRANT EXECUTE ON DATABASE::AdventureWorks TO Test2;

USE master;
GRANT ALTER TRACE TO Test2;

These GRANT statements allow the Test2 user to have full control on the Person.Address table, much like the owner of the table; query any object in the Person schema; and execute any procedure or function in the AdventureWorks database. The ALTER TRACE permission allows Test2 to run Profiler to trace SQL Server. Because ALTER TRACE is a server-scoped permission, the statement has to be run in the master database.

The following statement illustrates principal-scoped permission and the new IMPERSONATE permission verb to allow user Test1 to impersonate user Test2:

USE AdventureWorks;
GRANT IMPERSONATE ON USER::Test2 TO Test1;

You can start a new query window, connect as Test1, and run following statements:

USE AdventureWorks;
SELECT * FROM Person.Contact;
GO
ALTER TABLE Person.Address ADD col1 int NULL;
GO
ALTER TABLE Person.Address DROP COLUMN col1;
GO

EXEC dbo.uspGetEmployeeManagers 1;
GO

You should notice that all the four statements fail because user Test1 does not have permission to select from the Person.Contact table or change the Person.Address table or execute the stored procedure.

You can run the following line to impersonate the Test2 user and then run the preceding four statements. This time, the batch succeeds without any error messages because Test2 user has the required permissions:

EXECUTE AS USER='Test2';
GO
SELECT USER_NAME()
GO
--Run the earlier batch again

You can run the REVERT statement to switch back to the Test1 user execution context. Close this query window.

Next, you should start the Profiler tool and connect as user Test2. You should then be able to run SQL trace even though Test2 is not part of the sysadmin server role but has ALTER TRACE server-level permission. After you have verified that you can run Profiler trace by connecting using the Test2 login, close the Profiler and return to original query window in Management Studio, where you should execute the following lines to clean up:

USE AdventureWorks;
GO
DROP USER Test1;
GO
DROP USER Test2;
GO

DROP LOGIN Test1;
GO
DROP LOGIN Test2;
GO

Module Execution Context

As described in the previous section, the SQL Server 2005 security model allows granting permissions to users and logins and also granting permissions on users and logins (principals in general). One such principal-level permission is IMPERSONATE, which allows a user or login to change the execution context and perform actions as an impersonated user or login. As illustrated earlier, once the IMPERSONATE permission is granted, the EXECUTE AS statement can be used to switch the execution context, and the REVERT statement can be used to revert to the previous execution context.

SQL Server 2005 introduces a concept called module execution context that allows you to specify the execution context under which to execute a module, such as a stored procedure, a function (except an inline table-valued function), or a trigger. You can specify the EXECUTE AS clause with DDL statements such as CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER to implicitly define the execution context of these user-defined modules. You can just grant the EXECUTE permission on the module to the users, and when the module is actually executed, the database engine will use the account specified with EXECUTE AS to validate permissions on objects referenced in the module. This way, you don’t have to separately give permissions on the referenced objects to users of the module.

This might seem somewhat similar to the concept of ownership chaining that was available in previous releases. As a matter of fact, module execution context is designed to fix some of the limitations of ownership chaining.

Ownership Chaining and Its Limitations

The notion of ownership chaining simplifies permissions management and also yields small performance gains. The term chain refers to a virtual link that is established when an object, such as a stored procedure or a function, accesses other objects, such as tables and views.

For instance, if UserX executes the procedure UserY.Proc1, SQL Server first checks whether UserX has EXECUTE permission on UserY.Proc1. Once that is verified, if UserY.Proc1 queries the table UserY.Table1, SQL Server does not check whether UserX has permissions on UserY.Table1 because the procedure owner and object owner are the same (that is, UserY), forming an ownership chain. This way, you don’t have to grant permissions on referenced objects—just the EXECUTE permission on stored procedures. By avoiding the extra permission check, SQL Server provides a small performance gain. However, if the procedure UserY.Proc1 queries the table UserZ.Table2, SQL Server checks whether the caller, UserX, has permissions on UserZ.Table2 because the referenced table owner (that is, UserZ) is different than the procedure owner (that is, UserY), breaking the ownership chain.

As you can see, ownership chaining is a very useful concept. However, it suffers from three limitations:

• The ownership chain works only for DML statements (SELECT, INSERT, UPDATE, and DELETE) and does not work for DDL statements. For instance, in the preceding example, if the stored procedure UserY.Proc1 alters UserY.Table1, even though the executing object owner and referenced object owner are the same, SQL Server still checks permissions to ensure that UserX has sufficient permissions to alter the table.

• For security reasons, SQL Server explicitly blocks or disables ownership chaining for dynamic SQL.

• Ownership chaining relies on the fact that that the owners in the chain are the same. Ownership chaining is not designed to let you run a module under a different user context.

All these limitations are addressed by the module execution context feature introduced in SQL Server 2005. If you specify the EXECUTE AS clause with the module definition, SQL Server runs the entire module under the security context of the specified account, regardless of whether the ownership boundaries are crossed, the module contains DDL statements, or the module uses dynamic SQL. The concept of ownership chaining and module execution context is illustrated in Figure 7.1.

Figure 7.1. SQL Server 2005 introduces the EXECUTE AS clause, which you can use to implicitly specify the security context to be used while running a user-defined module.

Let’s now look at module execution context features and the EXECUTE AS clause in more detail.

The EXECUTE AS Clause

The WITH EXECUTE AS clause can be used to explicitly define the execution context of the user-defined modules, including stored procedure, functions, DML triggers, DDL triggers, and Service Broker queues. The EXECUTE AS clause can be specified with the CREATE or ALTER DDL statement for user-defined modules. You can specify one of the following four execution contexts for the module:

EXECUTE AS {CALLER | 'UserName' | SELF | OWNER}

The default is CALLER (except in Service Broker queues, where SELF is the default), which provides the same execution context behavior as in previous release—that is, the permission check is skipped if the referenced object owner and module owner are the same, forming the ownership chain. If the referenced object owner and the module owner are different, breaking the ownership chain, SQL Server ensures that the caller has the permissions on the referenced object. By specifying an execution context other than CALLER, you are telling SQL Server that you cannot rely on ownership chaining and that you would like to specify an account under which it should run the module.

In order to specify <UserName> with EXECUTE AS, you must have the IMPERSONATE permission on that user. The statements in the module are then executed in the security context of the specified account. For server-scoped DDL triggers, you have to specify a login name.

The SELF execution context indicates the user creating or altering the module. SELF is the default for Service Broker queues. SELF is essentially a shortcut to <UserName>, where SQL Server puts the name of the user creating or altering the procedure.

The OWNER execution context tells SQL Server 2005 to run the module under the security context of the current module owner at the time of execution. While creating the module, if you are specifying a different owner, you must have the IMPERSONATE permission on the module owner. The specified owner must be a singleton account, and it cannot be a role or a group.

Let’s now look at EXECUTE AS in action. In the following example, the user Sean is a DBA who would like to allow the user Erin to truncate a table but not do anything else with that table. Because TRUNCATE is not a grantable permission, Sean creates a stored procedure and specifies the EXECUTE AS SELF clause and grants the EXECUTE permission to user Erin. This way, the DBA creates his own new “permissions bucket.”

You can begin working with this example by creating the users Sean and Erin:

USE AdventureWorks;
GO

CREATE LOGIN Sean WITH PASSWORD = '1234';
GO
CREATE LOGIN Erin WITH PASSWORD = 'pqrs';
GO

CREATE USER Sean;
CREATE USER Erin;

EXEC sp_addrolemember 'DB_OWNER', 'Sean';
GO

Next, you need to launch a new query window, connect as login Sean with password 1234, and run the following statements to create a table and the stored procedure to truncate the table:

USE AdventureWorks;
GO
CREATE TABLE dbo.tblTestData
(col1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  col2 VARCHAR(10) DEFAULT 'Test'),
GO
INSERT INTO dbo.tblTestData DEFAULT VALUES;
INSERT INTO dbo.tblTestData DEFAULT VALUES;
INSERT INTO dbo.tblTestData DEFAULT VALUES;
INSERT INTO dbo.tblTestData DEFAULT VALUES;
GO
SELECT * FROM dbo.tblTestData;
GO

CREATE PROCEDURE dbo.usp_TruncateTestData
WITH EXECUTE AS SELF
AS
   SELECT USER_NAME();
   TRUNCATE TABLE dbo.tblTestData;
GO

GRANT EXECUTE ON dbo.usp_TruncateTestData TO Erin;
GO

Then you need to launch one more query editor window. This time connect as user Erin with password pqrs and run the following script:

USE AdventureWorks;
GO

SELECT * FROM sys.all_objects WHERE type = 'U';
GO

SELECT * FROM dbo.tblTestData;
GO

EXEC dbo.usp_TruncateTestData;
GO

Because the user Erin does not have permission on any user table, the query on the sys.all_objects catalog view returns an empty set, even though there are many user tables in the database. This is because of the new metadata security feature introduced in SQL Server 2005, which is discussed later in this chapter.

Let’s say that somehow user Erin found out that there is a table called tblTestData. If she tries to run the SELECT statement on this table, the operation fails because Erin does not have permission to query the table. However, when Erin runs the stored procedure, SQL Server runs the procedure under the security context of user Sean, as shown by the SELECT USER_NAME() statement inside the procedure. And because user Sean has full control on the table, the truncate statement inside the usp_TruncateTestData stored procedure succeeds.

Next, you need to close the query window connections made using the logins Sean and Erin and return to the original query window, where you can run the following statements to clean up:

DROP TABLE dbo.tblTestData;
DROP PROCEDURE dbo.usp_TruncateTestData;

DROP USER Sean;
DROP USER Erin;

DROP LOGIN Sean ;
DROP LOGIN Erin ;
GO

If you comment the WITH EXECUTE AS clause while creating the stored procedure, and if you follow the preceding demo steps again, you notice that when Erin executes the stored procedure, it fails because it is executing under her security context, and she does not have any permission on the table. If the procedure had the SELECT statement instead of the TRUNCATE statement, the stored procedure would work because of ownership chaining (the procedure owner and table owner are the same). However, ownership chaining works only for SELECT, INSERT, UPDATE, and DELETE statements, and not for TRUNCATE or any DDL statements, so this procedure fails if EXECUTE AS is commented. This example illustrates that EXECUTE AS works, regardless of the type of statements in a module.

User–Schema Separation

In SQL Server 2000, a fully qualified object name consists of four parts—server name, database name, owner name, and object name—and can be specified by using the following format:

[[[server.][database].][owner_name].]object_name

The objects can have the same name, as long as they have a different owner. In other words, you can have two tables named tblTest, as long as they are owned by different users. In that case, the applications qualify the object as User1.tblTest or User2.tblTest, depending on which table they wants to access. As you can see, here the username is used to avoid duplicates and to avoid name collisions. As per the ANSI SQL-92 standard, this is the job of a schema. SQL Server 2000 treated the schema and user the same. In other words, the schema and user were tightly coupled; they were one and the same. As per the ANSI SQL-92 standard, the purpose of schemas is to act like a namespace to club related objects and to avoid name collisions. With SQL Server 2000, the usernames were used for this purpose, and there was no true support for schemas.

Although this approach worked, there are a few problems. Let’s say UserX created a bunch of tables, stored procedure, functions, views, and so on. And the application accessed the objects as UserX.object_name. Now, let’s say for some reason that you wanted the drop UserX after the application went into production. You cannot drop a user unless all the objects the user owns are dropped or the ownership is assigned to a different user. Dropping the objects in almost all cases is not possible. And changing the owner requires that the application change. After the ownership is assigned to UserY, for example, the application has to access the objects as UserY.object_name instead of UserX.object_name.

Application developers often use dbo as the object owner to avoid the problem just described and also to avoid any name resolution issues. If the object is not fully qualified, SQL Server first tries to find an object with the specified name that is owned by the currently logged-in user. If not found, SQL Server tries to find dbo.object_name. For example, if UserX is trying to access table tblTest, and the table name is not fully qualified with the owner name, SQL Server first tries to find UserX.tblTest, and if it is not found, SQL Server tries to find a table named dbo.tblTest. This is the reason it is recommended that you fully qualify objects to avoid this extra name resolution check. Using dbo to group objects works fine until an application needs to create the objects and even the new objects have to be under the dbo ownership to aid name resolution. Just the CREATE TABLE permission is not sufficient to create a table in the dbo namespace. You have to be a member of either the sysadmin fixed server role or the db_dbowner or db_ddladmin fixed database role to create an object with dbo as the owner.

SQL Server 2005 decouples users (or principals in general) from schemas by changing the four-part naming convention to this:

[[[server.][database].][schema_name].]object_name

As you can see, with SQL Server 2005, the schema is the third part of the fully qualified object name. Schemas in SQL Server 2005 provide the notion of namespaces, allowing you to club the related objects together under a single name. In SQL Server 2000, objects were owned by users, whereas in SQL Server 2005, objects are contained in a schema that is owned by a user. Because a user does not own the objects directly, a user can be dropped without the objects needing to be dropped. By just assigning the schema owner to some other user, you can drop the user. Changing the schema owner does not require changing the application because the application continues to access the object as schema_name.object_name, regardless of who the schema owner is. This is illustrated in Figure 7.2.

Figure 7.2. SQL Server 2005 decouples users from the schema to simplify the security management and for ANSI SQL-92 compliance.

Schema can be created by using the CREATE SCHEMA DDL statement. The sys.schemas catalog view can be used to view a list of schemas in the current database.

Here is a simple example of how to create a schema and create objects in that schema:

USE AdventureWorks;
GO

CREATE SCHEMA Inventory;
GO

CREATE TABLE Inventory.tblReportData
   (col1 INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    col2 XML DEFAULT '<Report />'),
GO

INSERT INTO Inventory.tblReportData DEFAULT VALUES;
SELECT * FROM Inventory.tblReportData;
GO

SELECT name AS SchemaOwner FROM sys.database_principals
   WHERE principal_id =
   (SELECT principal_id FROM sys.schemas
       WHERE name = 'Inventory'),
GO

SELECT * FROM sys.all_objects WHERE schema_id =
   (SELECT schema_id FROM sys.schemas
       WHERE name = 'Inventory'),

DROP TABLE Inventory.tblReportData;
GO

DROP SCHEMA Inventory;
GO

This script creates a schema named Inventory and then creates a table in that schema. Note how this new table is accessed by INSERT and SELECT statements, using the schema_name.table_name convention, rather than user_name.table_name. This script uses the sys.schemas, sys.database_principals, and sys.all_objects catalog views to find out about the schema owner and the objects in the schema. The schema owner depends on the account you are logged in as while running the script. To drop the user, all you have to do is assign the schema ownership to some other user, and the application can continue to access the object as Inventory.tblReportData.

Multiple objects can still have the same name, as long as they are in different schemas. Because objects are not owned by users anymore, you might ask how name resolution works. How does SQL Server 2005 determine which object to access when a fully qualified name is not provided? The answer to this question is a new concept called default schemas.

Default Schemas

When you create or alter a user by using a CREATE USER or ALTER USER DDL statement, SQL Server 2005 gives you an option to provide a default schema for that user. You can use the WITH DEFAULT_SCHEMA = schema_name construct to specify the default schema for that user. If you do not make use of this construct while creating the user, by default SQL Server 2005 makes dbo as the user’s default schema. The notion of default schemas is used for name resolution when an object is not fully qualified. SQL Server looks for the default schema for the user trying to access the object, and if the object is not found in user’s default schema, SQL Server searches for the object in the dbo schema.

Here’s an example of a default schema:

USE AdventureWorks;
GO

CREATE LOGIN demoLogin WITH PASSWORD = '1234';
CREATE USER demoLogin WITH DEFAULT_SCHEMA = Sales;
GRANT SELECT ON OBJECT::Sales.Store TO demoLogin;
GRANT EXECUTE ON XML SCHEMA COLLECTION::Sales.StoreSurveySchemaCollection
    TO demoLogin;
GO

EXECUTE AS USER = 'demoLogin';
GO

SELECT * FROM Store;
GO
SELECT * FROM Sales.Store;
GO

REVERT;
GO

ALTER USER demoLogin WITH DEFAULT_SCHEMA = Production;
GO

EXECUTE AS USER = 'demoLogin';
GO

SELECT * FROM Store;
GO
SELECT * FROM Sales.Store;
GO

REVERT;
GO

DROP USER demoLogin;
DROP LOGIN demoLogin;
GO

This script creates a login named demoLogin and a user with the same name in the AdventureWorks sample database. While creating the user, a schema named Sales is made as the default schema for the user. The script then grants permissions so that this user can run SELECT queries on the Sales.Store table. The security execution context is then changed by using an EXECUTE AS statement to run the next SELECT statement under the demoLogin user’s context. Both the SELECT statements succeed because the user’s default schema is Sales. When an object is not fully qualified, SQL Server looks for an object in user’s default schema. If it is not found, SQL Server looks in the dbo schema namespace. In this case, it finds the Store table in the user’s default schema. The script then changes the user’s default schema to Production. The same SELECT then fails because it does not find the Store table either in the Production schema or in the dbo namespace.

The notion of default schemas allows applications to not fully qualify objects but still guarantees to find the objects. As described earlier, in SQL Server 2000 this was achieved by having dbo as the owner. However, as alluded to earlier, creating objects to have dbo as the owner required high privileges, such as being part of the sysadmin, db_dbowner, or db_ddladmin roles. The default schema notion also fixes this problem. You can create a schema and have that as the default schema for all your users. Users can now create objects in this schema without requiring high privileges, and they can access the objects without fully qualifying the objects.

A SQL Server 2005 database may consist of many schemas. Each schema has an owner. A user can own multiple schemas; however, there is always a single owner per schema. Each user has a single default schema that aids SQL Server in name resolution. In order to create objects in a schema, the user must have the CREATE permission and also ALTER or CONTROL permission on the schema.

Secure Metadata

SQL Server 2005 disallows direct access to system tables and exposes persistent metadata in the form of catalog views. SQL Server 2005 implements a security layer on top of the catalog views to restrict access to metadata and to make it visible only on a need-to-know basis. Row-level filtering on a catalog view ensures that if the user is not the owner or does not have permission to view the metadata, the catalog view row is not returned.

SQL Server 2005 defines a new permission, VIEW DEFINITION, which can be granted to a user to allow access to the metadata. Like other permissions, the VIEW DEFINITION permission can be granted at multiple scopes such as at database, schema, or object level.

Here is an example of catalog security in action:

USE AdventureWorks;
GO
CREATE LOGIN login1 WITH PASSWORD = 'WjkKWjkF';
GO
CREATE USER user1 FOR LOGIN login1;
GO

EXECUTE AS USER = 'user1';
GO
EXEC sp_helptext 'dbo.uspGetBillOfMaterials';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.uspGetBillOfMaterials'));
GO
SELECT * FROM sys.objects WHERE type = 'U';
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES;
GO
REVERT;
GO

GRANT VIEW DEFINITION ON OBJECT::dbo.uspGetBillOfMaterials TO user1;
GRANT VIEW DEFINITION ON SCHEMA::HumanResources TO user1;
GO

EXECUTE AS USER = 'user1';
GO
EXEC sp_helptext 'dbo.uspGetBillOfMaterials';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.uspGetBillOfMaterials'));
GO
SELECT * FROM sys.objects WHERE type = 'U';
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES;
GO
REVERT;
GO

DROP USER user1;
DROP LOGIN login1;
GO

This script creates a user who initially does not have any permission. When this user tries to see the stored procedure text by using sp_helptext, SQL Server 2005 returns an error, and OBJECT_DEFINITION returns NULL. When this user tries to obtain a list of user tables by using the sys.objects catalog view or by using the INFORMATION_SCHEMA view, SQL Server 2005 returns an empty result set because the user does not have permission on any of the tables and also does not have VIEW_DEFINITION permission. When the VIEW_DEFINITION permission is granted, the user can see the stored procedure text and can see all the tables in the schema on which the VIEW_DEFINITION permission is granted. The script uses EXECUTE AS and REVERT statements to change the security execution context.

Auditing Enhancements

SQL Server 2005 continues to support auditing successful and failed logins as previous releases did. In SQL Server Management Studio, you right-click the server instance in Object Explorer, select Properties to launch the Server Properties dialog, and on this dialog, you use the Security page to turn on or off auditing of successful or failed logins.

In addition to login auditing of the database engine, SQL Server 2005 now also supports auditing of Analysis Services. The Security Audit event class in the SQL Profiler tool can be used to audit login and logout actions. To see this in action, you can launch Profiler, connect to an Analysis Services instance, and select the Security Audit event class.

When you connect to a SQL Server 2005 instance by using Profiler, you see a lot of new events under the Security Audit class. Examples of these new security audit events include Audit Schema Object GDR Event, Audit Database Object Take Ownership Event, Audit Server Principal Impersonation Event, Audit Broker Conversation, Audit Broker Login, and so on.

Using triggers is one of the ways in which developers implement a custom auditing solution to track DML events, such as INSERT, UPDATE, and DELETE. SQL Server 2005 extends this model, and now you can use the new DDL triggers or event notification features to implement custom auditing solutions to track DDL events, such as altering a table, dropping a view, and so on. DDL triggers and event notifications are discussed in great detail in Chapter 6, “Transact-SQL Enhancements.”

Cryptography Support

In previous releases, if you were to encrypt and decrypt data, you had to either implement your own custom solution or rely on third-party vendor solutions that integrated with SQL Server to provide cryptography support. Listening to the feedback from SQL Server developers, Microsoft decided to add native support for encryption capabilities, including the key management infrastructure in SQL Server 2005. Functions such as EncryptByKey, EncryptByAsmKey, EncryptByCert, and EncryptByPassPhrase, as well as their decryption equivalents can be used to encrypt and decrypt data. The following sections show you how to encrypt/decrypt data and how to manage keys and certificates, and then it discusses signed modules, which are an alternative to the module execution context feature described earlier. Let’s begin with the data encryption feature introduced in SQL Server 2005.

Data Encryption

Encryption is a technique for securing data by converting clear or plain text into scrambled text or ciphertext. Encryption algorithms require an entity that they can use to encrypt or decrypt the text. This entity is known as the key. If the same key is used for encryption and decryption, such a key is termed a symmetric key. If one key is used for encryption and a different key is used for decryption, this key is termed an asymmetric key. An asymmetric key is generally a pair of public and private keys. SQL Server 2005 supports both symmetric and asymmetric keys. Asymmetric keys offer more robust security than symmetric keys, but encryption and decryption using asymmetric keys can be a very costly operation and should be restricted to small datasets. In addition to using symmetric and asymmetric keys, you can use a certificate or a passphrase to encrypt and decrypt data. You can think of a certificate as an entity that wraps the public key and is associated with a private key. A symmetric key can be created by using the CREATE SYMMETRIC KEY DDL statement, an asymmetric key can be created by using the CREATE ASYMMETRIC KEY DDL statement, and a certificate can created by using the CREATE CERTIFICATE statement, which can load a certificate from a file into SQL Server or have SQL Server generate a certificate.

Encrypting and decrypting using keys and certificates is discussed later in this chapter. To see cryptography in action, here’s a simple example of encrypting and decrypting data by using a passphrase:

USE AdventureWorks;
GO
CREATE TABLE dbo.tblAccount
  (RecordID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
   AccountNo VARBINARY(100) NOT NULL,
   BusinessName VARBINARY(512) NOT NULL,
   Balance MONEY NOT NULL);
GO

INSERT INTO dbo.tblAccount VALUES
  (EncryptByPassPhrase('WjkKWjkF_1', '001'),
   EncryptByPassPhrase('WjkKWjkF_2', 'Progressive Sports'),
   1000);

INSERT INTO dbo.tblAccount VALUES
  (EncryptByPassPhrase('WjkKWjkF_1', '002'),
   EncryptByPassPhrase('WjkKWjkF_2', 'Metropolitan Sports Supply'),
   5000);

SELECT * FROM dbo.tblAccount;
GO

SELECT RecordID,
       CONVERT(VARCHAR(15),
             DecryptByPassPhrase('WjkKWjkF_1', AccountNo)) AS AccountNo,
       CONVERT(VARCHAR(255),
             DecryptByPassPhrase('WjkKWjkF_2', BusinessName)) AS BusinessName,
       Balance
FROM dbo.tblAccount;
GO

DROP TABLE dbo.tblAccount;
GO

This script creates a table to store some financial data. The requirement is to encrypt two fields: the account number and the business name. The script uses the EncryptByPassPhrase function during the INSERT operation and the DecryptByPassPhrase function while querying the data from this table. After you run the INSERT statements, the account number and business name columns appear as varbinary encrypted data in the table. The DecryptByPassPhrase function, along with the same passphrase used for encryption, is used to turn the ciphertext into plain text.

Let’s now look at what it takes to implement encryption by using keys and certificates.

Key Management

As mentioned earlier, you need a key or a certificate to encrypt and decrypt data. The SQL Server 2005 team realized that it is critical to secure the keys and certificates used for encryption. If someone gets access to a key/certificate, he or she can easily decrypt the data, and you lose the purpose behind encrypting the data. Therefore, how keys are managed and secured is very important.

SQL Server stores keys in the database. Symmetric keys (where the same key is used for encrypting and decrypting the data) and the private key (the secret part of an asymmetric key) are always stored encrypted in a database. Also, when symmetric keys and private keys are being used and are in memory, they are encrypted in memory, too.

There are three ways to secure symmetric keys and private keys. The first method allows you to specify a password that only you know. SQL Server uses this password to encrypt the key. With this method, it is your responsibility to secure the password. The second method for securing a key is to encrypt it by using a certificate. The third method is to encrypt a key by using another key, which is secured by a password, certificate, or yet another key.

As mentioned earlier, a certificate is nothing but an entity that wraps the public key and is associated with the private key. If a key is secured by using a certificate, it is important that the private key associated with the certificate be secured. The private key associated with the certificate can be secured by using a password or by using a database master key. You can create a single database master key per database by using the CREATE MASTER KEY DDL statement. The database master key itself is secured by using a DPAPI (Windows Data Protection API)-based instance-level secure key called a service master key. The service master key is generated per instance during the installation process and is the root of the SQL Server encryption hierarchy.

Figure 7.3 shows the key management concepts discussed here.

Figure 7.3. SQL Server 2005 provides a robust and secure mechanism to protect the keys and certificates used for encryption.

Let’s now look at an example of encrypting data by using a symmetric key. In this example, the key itself will be secured by using a certificate. The certificate will be secured by using a database master key, which will be secured by using a password.

The following code creates a sample database and a table to store encrypted data:

USE master;
GO
CREATE DATABASE BankDB;
GO
USE BankDB;
GO
CREATE TABLE dbo.tblAccount
  (RecordID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
   AccountNo VARBINARY(100) NOT NULL,
   BusinessName VARBINARY(512) NOT NULL,
   Balance MONEY NOT NULL);
GO

Now you should create a symmetric key to be used for encrypting the data. The symmetric key will be secured by using a certificate, which in turn will be secured by using a database master key. As shown here, you need to create a database master key, and then you need to create a certificate and a symmetric master key:

--Step 1
CREATE MASTER KEY
   ENCRYPTION BY PASSWORD = 'WjkKWjKF';
GO
--Step 2
CREATE CERTIFICATE certAccount
   WITH SUBJECT  = 'certAccount';
GO
--Step 3
CREATE SYMMETRIC KEY keyAccount
   WITH ALGORITHM = TRIPLE_DES
   ENCRYPTION BY CERTIFICATE certAccount;
GO

SELECT * FROM sys.symmetric_keys;
SELECT * FROM sys.certificates;
GO

The sys.symmetric_keys and sys.certificates catalog views can be used to view the keys and certificates metadata. The sys.key_encryptions catalog view information related to encryptions of symmetric keys. For instance, the following query could be used to get a list of symmetric keys encrypted using the certificate named certAccount:

SELECT [sk].[name] FROM
   sys.symmetric_keys AS [sk] JOIN
   sys.key_encryptions AS [ke]
        ON [ke].key_id = [sk].symmetric_key_id
   JOIN sys.certificates AS [c]
        ON [ke].thumbprint = [c].thumbprint
WHERE [c].[name] = 'certAccount';

After the symmetric key is created, the data can be encrypted by using the EncryptByKey function. But before you use the key, you need to open the key by using the OPEN SYMMETRIC KEY statement, which prepares the key to be used for encryption or decryption. Once the key is open, it can be used until it is closed or until the session is active. You can execute the following statements to open the symmetric key and use it for encrypting the data.

OPEN SYMMETRIC KEY keyAccount DECRYPTION BY CERTIFICATE certAccount;
GO

INSERT INTO dbo.tblAccount VALUES
  (EncryptByKey(KEY_GUID('keyAccount'), '001'),
   EncryptByKey(KEY_GUID('keyAccount'), 'Progressive Sports'),
   1000);

INSERT INTO dbo.tblAccount VALUES
  (EncryptByKey(KEY_GUID('keyAccount'), '002'),
   EncryptByKey(KEY_GUID('keyAccount'), 'Metropolitan Sports Supply'),
   5000);
--Close Symmetric Key
CLOSE ALL SYMMETRIC KEYS;
GO

If you now look at the rows in the table, you see that the account number and business name columns appear as encrypted data:

SELECT * FROM dbo.tblAccount;
GO

To decrypt the data, the key needs to be opened again:

OPEN SYMMETRIC KEY keyAccount DECRYPTION BY CERTIFICATE certAccount;
GO

SELECT RecordID,
       CONVERT(VARCHAR(15), DecryptByKey(AccountNo)) AS AccountNo,
       CONVERT(VARCHAR(255), DecryptByKey(BusinessName)) AS BusinessName,
       Balance
FROM dbo.tblAccount;
GO

--Close Symmetric Key
CLOSE ALL SYMMETRIC KEYS;
GO

--Cleanup
USE master;

GO
DROP DATABASE BankDB;
GO

This time you see the account number and business name columns appear decrypted as plain text.

When a database master key is created by using the CREATE MASTER KEY statement, SQL Server encrypts it by using the specified password and stores it in a system table in the user database, which can be viewed by using the sys.symmetric_keys catalog view. At the same time, SQL Server encrypts the database master key by using the instance-level service master key and stores it in the master database. SQL Server uses this encrypted user database master key in the master database for key management, without relying on the user password used to encrypt the database master key. You can change the password used for encrypting the database master key by using the ALTER MASTER KEY DDL statement and specifying the REGENERATE WITH ENCRYPTION BY PASSWORD option. Regenerating the database key leads to decrypting all the keys generated using this database master key and re-encrypting them by using the new database master key. This process can be resource intensive and should be run during off-peak hours.

During the installation, the service master key is generated by using DPAPI, which derives the key from the service account under which the SQL Server service is running. You can regenerate the service master key by using ALTER SERVICE MASTER KEY, which results in decrypting all keys generated using the current service master key and re-encrypting them by using the new service master key. This is a resource-intensive operation and should only be performed during off-peak hours. You can back up and restore the service master key by using the BACKUP SERVICE MASTER KEY and RESTORE SERVICE MASTER KEY statements.

In this section, you have learned how to encrypt data by using passphrases and keys and how key management and security work in SQL Server 2005. The next section illustrates use of certificates to sign a module, which is an alternative technique to implement the module execution context feature described earlier.

Signed Modules

Earlier in this chapter, you learned about the EXECUTE AS clause, which can be used to change the security execution context. This clause, when specified with the modules, fixes the issues with ownership chaining, allowing you to explicitly specify the execution context under which the module should run. This greatly simplifies permission management because you don’t have to individually grant and manage permission on the objects referenced in the module. However, this approach has one limitation. Because the module execution context changes to the one specified by using the EXECUTE AS clause, there is no way you can find out who from the list of users having the EXECUTE permission on the module actually executed the procedure. The example provided in the section “The EXECUTE AS Clause,” earlier in this chapter, illustrates this by outputting the value returned by USER_NAME().

In summary, if you want to grant access to a resource but only when it is accessed through a particular module, the problem with EXECUTE AS is that it changes the module execution context, which may interfere with your auditing process. If you want to preserve the execution context, you can use a signed module.

To implement signed modules, you have to create a certificate, create a “logical” user associated with the certificate, grant permissions on referenced objects to this logical user, and then use the ADD SIGNATURE T-SQL construct to associate the certificate with a stored procedure, a function, a trigger, or an event notification.

As long as the certificate is associated with the module and the logical user associated with the certificate has permissions on the referenced objects, the users having EXECUTE permissions should be able to successfully execute the module and access the referenced objects through the module. When SQL Server 2005 executes the module and it finds out that a certificate is associated with the module, it adds the certificate to the security token, and by virtue of the presence of the certificate in the token, access to referenced objects is granted for the duration of module.

Let’s now look at a signed module in action. The goal is to restrict direct access to a table but allow access to the table via a stored procedure.

You can begin working with a signed module by creating a login and a user named tableOwner, who will have permission to create schemas and tables in the AdventureWorks sample database. The EXECUTE AS statement is then used in the script to change the security context to the tableOwner login, and in this context, a schema and table are created, and a few rows are added to the table. The REVERT statement at the end changes the execution context back to the original login used to connect to SQL Server. You can execute the following script by using Management Studio or SQLCMD:

USE AdventureWorks;
GO
CREATE LOGIN tableOwner WITH PASSWORD = 'pwd';
GO
CREATE USER tableOwner;
GO
GRANT CREATE SCHEMA TO tableOwner;
GRANT CREATE TABLE TO tableOwner;
GO

EXECUTE AS LOGIN = 'tableOwner';
GO
CREATE SCHEMA tableOwner;
GO
CREATE TABLE tableOwner.tblTest
   (col1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    col2 DATETIME DEFAULT GETDATE(),
    col3 UNIQUEIDENTIFIER DEFAULT NEWID());
GO
INSERT INTO tableOwner.tblTest DEFAULT VALUES;
INSERT INTO tableOwner.tblTest DEFAULT VALUES;
INSERT INTO tableOwner.tblTest DEFAULT VALUES;
GO
SELECT * FROM tableOwner.tblTest;
GO
REVERT;
GO

In this example, a user named tableOwner owns a table on which the DBA wants to give access to a user, but only if this table is accessed via a stored procedure. The DBA creates a stored procedure and signs it with a certificate:

CREATE PROCEDURE dbo.sproc_Test
AS
BEGIN
   SELECT USER_NAME();
   SELECT SYSTEM_USER;
   SELECT * FROM tableOwner.tblTest;
END;
GO

CREATE CERTIFICATE crt_Demo
  ENCRYPTION BY PASSWORD = 'JbSSSA'
  WITH SUBJECT = 'Certificate to use for signing modules';
GO

ADD SIGNATURE TO dbo.sproc_Test
  BY CERTIFICATE crt_Demo
  WITH PASSWORD = 'JbSSSA';
GO

Now that the module is signed, you can create a logical user associated with the certificate and grant SELECT permission on the table to this user:

CREATE USER userCert FOR CERTIFICATE crt_Demo;
GO
GRANT SELECT ON tableOwner.tblTest TO userCert;
GO

You can now create the user to whom the DBA does not want to give direct table access—only access through a stored procedure:

CREATE LOGIN demoLogin WITH PASSWORD = 'WjkKWjkF';
GO
CREATE USER demoUser FOR LOGIN demoLogin
GO
GRANT EXECUTE ON dbo.sproc_Test TO demoUser;
GO

The user is granted EXECUTE permission on the signed stored procedure that accesses the table. Now you can change the security context to the demoLogin principal and then try to access the table directly and by running the stored procedure:

EXECUTE AS LOGIN = 'demoLogin';
GO
SELECT * FROM tableOwner.tblTest;
GO
EXECUTE dbo.sproc_Test;
GO
REVERT;

Here the SELECT statement fails, but the user can see the data through the stored procedure. Now you can revoke the permission from the certificate (that is, the certificatemapped user), and you should notice that demoLogin now can’t see the data, even through the stored procedure:

REVOKE SELECT ON tableOwner.tblTest FROM userCert;
GO

EXECUTE AS LOGIN = 'demoLogin';
GO

SELECT * FROM tableOwner.tblTest;
GO
EXECUTE dbo.sproc_Test;
GO
REVERT;
GO

DROP TABLE tableOwner.tblTest;
DROP PROCEDURE dbo.sproc_Test;
DROP USER demoUser;
DROP LOGIN demoLogin;
DROP SCHEMA tableOwner;
DROP USER tableOwner;
DROP LOGIN tableOwner;
DROP USER userCert;
DROP CERTIFICATE crt_Demo;
GO

This time, even the stored procedure fails because the certificate-mapped user does not have permission to use SELECT on the table. The DROP statement toward the end performs the cleanup by removing the users, logins, schemas, and table.

An important thing to note in this script is the result of the USER_NAME() function in the stored procedure. Unlike as with the EXECUTE AS clause, the security execution context remains unchanged while using signed modules, and USER_NAME() returns demoUser as the user trying to run the stored procedure. This way, you can avoid changing security execution context and still overcome the ownership chaining limitations.

Surface Area Configuration (SAC)

Surface Area Configuration (SAC) is an example of Microsoft’s commitment to security initiatives in SQL Server 2005. SAC refers to two things: the features and components that are not implicitly installed or activated during setup and a new tool that you can use to enable or disable features, services, and network protocols. By having you selectively install or activate the components and by providing the SAC tool, SQL Server 2005 lets you protect your SQL Server 2005 environment by reducing the attackable area of a system.

When you install SQL Server 2005, components such as Analysis Services, Reporting Services, Notification Services, Full-Text Search, and Integration Services are not implicitly selected. You can explicitly select to install these components.

SQL Server 2005 by default disables several engine features, such as CLR integration, execution of xp_cmdshell extended stored procedures, SQLMail, Database Mail, execution of OLE automation stored procedures, ad hoc distributed queries using OPENDATASET and OPENDATASOURCE, Web Assistant stored procedures, and so on. You can turn these features on or off by using the sp_configure stored procedure or by using the SAC tool. As in the database engine, certain Analysis Services features are also turned off. These include ad hoc data mining queries using OPENROWSET, anonymous connections, user-defined functions written using .NET CLR or COM, and linked objects. You can use the SAC tool (which you open by selecting Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration) to enable or disable these features as well. The sys.system_components_surface_area_configuration security catalog view can be used to obtain a list of executable system objects that can be enabled or disabled by SAC.

Figure 7.4 shows the SAC tool to configure services and network protocols.

Figure 7.4. SAC is a new tool dedicated to protecting SQL Server 2005 systems by reducing the attackable surface area.

Figure 7.5 shows the SAC tool for configuring database engine and Analysis Services features.

Figure 7.5. In addition to services and network connections, SAC can also be used to enable and disable database engine and Analysis Services features.

Table 7.1 lists the engine features that can be turned on or off.

Table 7.1. SQL Server 2005 Database Engine Features

SQL Server Agent Security Overview

The Agent security enhancements in SQL Server 2005 include the two new database roles in the msdb system database and an ability to use multiple proxy accounts. Administrators can use the new database role named SQLAgentUserRole to manage users who can create or execute SQL Server agent jobs. By default, no user is a member of the SQLAgentUserRole role.

SQL Server 2000 allowed one proxy account. A proxy essentially defines the security context for a job step. SQL Server changes that by allowing you to have any number of proxy accounts. You as a system administrator can allow logins, msdb roles, and system roles to access one or more proxy accounts. You can also assign proxy accounts to access one or more subsystems such as SQL Server Integration Services (SSIS; formerly DTS) package execution, Replication Distributor, Replication Transaction Log Reader, and so on. Members of the sysadmin server role can use the sp_add_proxy stored procedure to create a new SQL Agent proxy account and sp_grant_login_to_proxy to grant access on a proxy to a principal.

SQLCLR Security Overview

Microsoft knew that allowing .NET code to run inside the SQL Server 2005 engine would raise security-related concerns among database administrators. SQL Server 2005 addresses this concern by introducing a security architecture that combines SQL Server’s user-based model with the .NET Framework’s code-access–based security model. You as an administrator have complete control over what the SQLCLR can and cannot do.

The code access security in .NET is based on permissions defined in the configurable Runtime Security Policy (which you access by selecting Start | Programs | Administrator Tools | .NET Framework 2.0 Configuration). Based on where the .NET assembly originated and the user running the assembly, the administrator can determine what that assembly can or cannot do. The .NET Framework security is provided via four configurable policy levels: Enterprise, Machine, User, and Host. The assembly receives the permissions based on the intersection of permissions defined in these policies. The highest level is Enterprise, which describes the policy for an entire application. The next level is the Machine policy, which affects all .NET assemblies running on the machine. For instance, if you define Machine policy that disallows writing or creating new entries in the registry under the HKLM hive, then all assemblies running on that machine will not be able to create entries or write into the HKLM hive in the registry. The User policy allows assigning different permissions to the same assembly based on the users executing it. In the case of SQL Server 2005, it is the account under which SQL Server 2005 service is running. The Host policy is defined by the host of the .NET CLR—in this case, SQL Server 2005.

SQL Server 2005 Host policy allows you to put user assemblies into one of following three permissions buckets:

• SAFE—This is the most restrictive and recommended permission set. It allows the assembly to perform internal computations and data access, and nothing else. An assembly with the SAFE permission set cannot access external system resources such as files. This is the default permission set.

• EXTERNAL_ACCESS—This is the next level after SAFE, and it adds the ability to access external resources, such as files.

• UNSAFE—The UNSAFE permission set allows assemblies unrestricted access to resources, both within and outside SQL Server. Assemblies can even call unmanaged code. Assemblies should be put in the UNSAFE permission bucket after thorough consideration and analysis. Only sysadmin role members can import assemblies by using this permission set.

When you import a .NET assembly by using the CREATE ASSEMBLY DDL statement, you can put that assembly in one of the three permissions buckets by using the WITH PERMISSION_SET = clause, as illustrated here:

CREATE ASSEMBLY sqlclrXMLHelper
FROM 'E:DevsqlclrXMLHelper.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

When the SQLCLR .NET code is executed, the intersection of permissions defined in Machine policy, User policy, and Host policy (the permissions bucket) determine what the code can or cannot do. The .NET integration in SQL Server 2005 and security considerations are further discussed in Chapter 11, “SQL Server 2005 and .NET Integration.”

Service Broker Security Overview

Service Broker is a new technology introduced in SQL Server 2005 that brings asynchronous messaging services closer to the database engine, allowing you to build reliable, asynchronous queued database applications. A message can originate at one instance and can possibly be received at another SQL Server 2005 instance. The security features in Service Broker guarantee that the conversation is secure and that the unauthorized databases are not able to send Service Broker messages.

When you create an endpoint to be used for Service Broker communication, the default value for the ENCRYPTION option is REQUIRED, which indicates that endpoint must use encryption. Therefore, to connect to this endpoint, another endpoint must have ENCRYPTION set to either SUPPORTED or REQUIRED. The Service Broker transport authentication can be based on certificates or SSPI.

Service Broker security is based on certificates. A certificate is used to verify the identity of a remote database and to identify the local database principal for the operation. Service Broker uses the public key associated with the local database principal to decrypt the information received from the remote server. If it is able to decrypt the information, the remote database contains the private key that corresponds to the public key in the local certificate associated with the principal. After this verification, the remote database can act with the permissions of the local database principal that owns the certificate that contains the public key for the remote database.

SQL Server Service Broker and its security features are described in Chapter 14.

Summary

The goal of this chapter is to introduce you to the security enhancements in SQL Server 2005. The SQL Server 2005 team has done everything possible to ensure that this release provides the most secure and robust environment possible to build database applications. Following the Trustworthy Computing initiative, SQL Server 2005 is secure by design, secure by default, and secure in deployment.

SQL Server 2005 contains significant enhancements to authentication, authorization, metadata access, and auditing schemes. This chapter also discusses the built-in cryptography support that you can use to easily encrypt and decrypt data and secure the keys used for encryption. SQL Server 2005 provides tools and technologies that can be used to protect the SQL Server environment by disabling unused features, protocols, and services and thereby reducing the attackable surface area.

The final section in this chapter briefly discusses security initiatives in various SQL Server 2005 subsystems, such as Service Broker and .NET CLR integration. Chapter 8 discusses the new reliability and high availability features introduced in SQL Server 2005.