SQL Server

On The Horizon: Improved Data Security In SQL Server 2005

Don Kiely

Prerelease info in this article is subject to change. 

At a Glance:

  • Secure design, default, and deployment with SQL Server 2005
  • New granular permissions
  • Separation of user, schema
  • Proxy accounts for special rights

SQL Server 2005

Security Policy

Permission Types

Security and data integrity have always been two of the most fundamental reasons to store data in a relational database. That was true when SQL Server 2000 was released, but the world of computing security has changed in ever nastier ways since then..

As a result, Microsoft has been talking a lot about the security issues they have grappled with during the design and development stages of SQL Server™ 2005. You can read all the details at the SQL Server Web site, but the following three points summarize the company's Trustworthy Computing initiative:

  • Secure by design
  • Secure by default
  • Secure in deployment

Microsoft has performed extensive threat modeling and security analysis to identify the threats facing database servers, resulting in a SQL Server 2005 that is secure by design. This means that you'll need a deeper understanding of security than ever before, but you'll have a rich set of tools with which to apply that knowledge.

Secure by default means that SQL Server 2005 is secure out of the box when you install it with default settings. Features that are not required by a basic database server are left uninstalled. The biggest impact is that you will have to spend a lot of time after initial installation turning on the features you require and installing additional components. It also means that because you have to do the work to hunt down the feature and install it or turn it on, most installations will have a smaller attack surface. Figure 1 shows a few of the features and their default status.

Figure 1 Default Status

Service Installed Running
Analysis Services No
CLR Integration Yes No
Database Mirroring Yes No
Debugging Yes No
Integration Services No
Notification Services No
Replication No
Reporting Services No
Service Broker Yes No
SQLeMail Yes No
SQLMail Yes No

Secure in deployment means that Microsoft is committed to providing all the information and tools you need to make intelligent decisions about deploying SQL Server 2005 securely. As you'll see, there are many more security choices than ever before, and making the wrong choices can too easily circumvent security features. Microsoft is also providing tools to manage security over time. For example, you can now install updates automatically. These initiatives have practical implications for administrators.

Password Policy

The first step in any attack is to get access to the server, exploiting any security weaknesses. SQL Server 2005 has plenty of features that make it more secure and easier to lock down after initial installation. One major change is that if you choose to continue using SQL Server logins, you can tie the password policy to Windows security. This requires Windows Server 2003™, but means you can finally enforce password strength and expiration policies without relying on homegrown solutions.

Granular Permissions

Probably the most sweeping change to the SQL Server 2005 security infrastructure is the new, highly granular permission model. Earlier versions of SQL Server frequently required an admin to grant a user membership in a fixed server role that had permissions far too broad for simple tasks. This violates the principle of least privilege and opens the door to abuse both by the user and attackers. Probably the most egregious example is that auditing used to require system admin (sa) privileges.

SQL Server still uses the basic grant, deny, and revoke permission states of earlier versions. In addition, the general permissions scheme still uses a grantee that receives a permission at either the server or database level and a securable that represents an object such as a table or entire database that needs protection. However, a login can now have permissions granted to it.

There are big changes in the level and type of permissions available, as well as a hierarchy of permissions that let you, for example, grant sweeping permissions to a user at the database level, and then surgically remove similar related permissions at the table level. (See the sidebar "Permission Types" for more information.)

One of the benefits of the newly granular permission scheme is that metadata is now protected as well as data. In earlier versions, a user with any access to a database was able to see the entire structure of the database, whether or not she could access the data within it or execute any stored procedures. Now SQL Server 2005 examines the permissions a principal has within the database and will only display a catalog view of the object if the principal is the owner or has some permission within the object. There is also a VIEW DEFINITION permission that can grant permission to view metadata information even without other permissions in the object.

Proxy Accounts

Like SQL Server 2000, SQL Server 2005 initially has a single SQL Agent proxy account used to access resources across the network. But, as with permissions, SQL Server 2005 provides a much more granular system for assigning rights to users and roles for executing SQL Agent jobs. Microsoft has also added additional subsystems you can use proxy accounts with, although the T-SQL subsystem still executes under the owner as it did in SQL Server 2000.

Members of the sa role, of course, can still do anything they want in any of the subsystems. Granting any other user rights to use subsystems requires the creation of at least one proxy account, which can grant rights to one or more subsystems.

Figure 2 shows how a proxy account, MyProxy, is assigned to multiple principals—here a user and a role. The proxy account uses a credential, which links it to an account, usually a domain account, with permissions in the operating system necessary to perform whatever tasks are required by the subsystem. Each proxy can have one or more subsystems associated with it that grant the principal the ability to run those subsystems.

Figure 2 SQL Agent Proxy Account

Figure 2** SQL Agent Proxy Account **

Figure 3 shows the T-SQL code necessary to implement the scheme shown in Figure 2. It starts by creating a credential—a database object that, in this case, provides the link to the operating system account with rights to perform the desired actions in the subsystems. Then it adds a proxy account, MyProxy, that is really just a friendly name for the credential. Next, it assigns the proxy to two principals, in this instance a SQL Server login and a custom role. Finally it associates the proxy with each of the four SQL Agent subsystems.

Figure 3 T-SQL Code to Create a SQL Agent Proxy

msdb..sp_add_proxy @proxy_name = 'MyProxy', @credential_name = 'MyCredential'
msdb..sp_grant_login_to_proxy @login_name = 'MyLogin', @proxy_name = 'MyProxy'
msdb..sp_grant_login_to_proxy @login_name = 'MyRole', @proxy_name = 'MyProxy'

sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'ActiveScripting'
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'CmdExec'
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'ANALYSISQUERY'
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'DTS'

A proxy is not a way to circumvent security in the operating system. If the credential used with a proxy doesn't have a particular permission in Windows®, like writing to a directory across the network, the proxy won't have it either. You can also use a proxy to grant limited execution rights to xp_cmdshell, since it is a favorite tool used by attackers to extend their reach into the network once they compromise a SQL Server installation. The proxy provides this protection because even if the principal has unlimited rights on the network, as does a domain administrator, any commands executed through the proxy have only the limited rights of the credential account.

User/Schema Separation

SQL Server has long supported the full server.database.owner.object syntax found in the SQL specification for fully naming objects, like so:

SELECT * FROM MySERVER.Northwind.dbo.Customers

In this case, the server is MyServer, the database is Northwind, the owner is dbo, and the object of interest is the Customers table. If you have followed the Microsoft advice in versions through SQL Server 2000, all of your user-defined objects are owned by dbo, the database owner, which automatically becomes the owner when a member of the sa fixed server role creates the object.

If the SELECT statement you just saw runs on the MySERVER box and Northwind is the current database, you can leave off all of the prefixes and execute this statement instead:

SELECT * FROM Customers

This is because SQL Server automatically looks in the current server in the active database, then uses either the current user or dbo as owner of the object.

The simplicity of this kind of object reference belies an important simplification in versions through SQL Server 2000: despite what I said in the last couple of paragraphs, "dbo" in the first SELECT statement refers not to the object's owner but to the object's schema. A schema is an abstraction defined in the ANSI SQL specification as a kind of bucket object that owns other objects. Up to now users and schemas were essentially the same thing. When you created a new user, SQL Server would create a schema with the same name and assign any objects owned by the user to the schema.

In SQL Server 2005, users generally don't own objects. Instead, one or more users or roles own schemas, which in turn contain objects, as shown in Figure 4.

Figure 4 User/Schema Separation

Figure 4** User/Schema Separation **

Besides being a purer implementation of ANSI SQL, the separation of users from schemas is significant for three reasons. First, it facilitates object ownership by multiple users or roles, making it easier to administer levels of permissions granted to those principals. Second, it makes an administrator's life far easier when a user who owns the object leaves the company or has a change of job responsibilities and is no longer required to own a set of database objects. No longer do you need to meticulously add a new owner and drop the old owner from each object. Now you can make those changes one time on the schema and be done with it, making ownership management a snap.

The third benefit again concerns least privilege. In the past, a user or role may have needed elevated privileges to work with objects owned by dbo, privileges that normally go far beyond what might be needed for users to make changes to their small part of a database. In SQL Server 2005, you can create whatever schemas you need to cover the database's objects and assign ownership of each schema only to the users or roles that require it to do their job.

Execution Context

SQL Server 2005 also allows far more granular and flexible permissions on execution contexts for stored procedures and user-defined functions, except inline table-valued functions. As part of a CREATE PROCEDURE you can specify the security context in which the procedure will run, whether it is in the context of the caller (the only option in SQL Server 2000 and earlier versions), as a specific user, as the user who created the procedure, or as the owner. The user context that is causing the stored procedure to run still needs to have execute permission, but once the procedure is running it is under the specified security context.

The security context of T-SQL code becomes critical with broken ownership chains, when the owner of the procedure does not own all of the objects accessed by the procedure. By specifying an execution context and thereby controlling the user under which SQL Server checks the ownership chain, you have a great deal of flexibility with how you define and manage object ownership.

The procedure or function's execution context is set as part of the CREATE PROCEDURE statement using the WITH EXECUTE syntax, as shown in Figure 5.

Figure 5 Execution Context

CREATE PROC GetCustomersInCountry
    @Country nvarchar(15)
        SELECT * FROM Customers 
        WHERE Country=@Country
GRANT EXECUTE ON GetCustomersInCountry TO User2

Once the procedure is created, User2 can execute the procedure but User1, specified in the EXECUTE AS clause, must have the required permissions on the Customers table. If the procedure calls other stored procedures, SQL Server would similarly enforce ownership rules by using User1, rather than User2.

You can use any of four forms of the EXECUTE AS statement:

EXECUTE AS CALLER can be used when you have an unbroken ownership chain or if you have good control over ownership management.

EXECUTE AS USER = 'User1' or the shorter version EXECUTE AS 'User1' is used when you have centralized ownership in User1. This simplifies ownership management by not requiring you to manage many users and their ownership.

EXECUTE AS SELF is a shortcut for the user who created the procedure. The actual user ID is saved in the catalog. This form probably will not be used often since it would be easy to fragment ownership depending on how you log into SQL Server.

EXECUTE AS OWNER can be used when the security context should be the current owner of the procedure, which cannot be a role. If there is no qualified owner, the owner of the module's schema is used as the security context.

When using the EXECUTE AS 'User1' form, the user executing the procedure must satisfy at least one of the following conditions to prevent the use of execution context to circumvent security:

  • He must be a sysadmin or db_owner
  • He must have CONTROL SERVER permissions on server or the database
  • He must have IMPERSONATE permission for username

The trickiest part of managing the developers and administrators who create procedures with execute context switches is first building a solid ownership foundation on which to change the execution context. With that foundation in place you'll be able to keep tight control over who can do what in accordance with the principle of least privilege.


It is arguable whether or not data stored in a secure database on a secure server in a secure network needs to be encrypted. After all, if no unauthorized users can get at the data, why bother using the many processing cycles required for encryption, particularly for many fields in a huge table?

The problem is that according to the security principle defense in depth, you have to assume that attackers will penetrate all walls of defense you erect around a valuable resource. Every layer of defense makes it less likely that an attacker will win the prize, and data encryption is a final level of defense against database attackers.

SQL Server 2000 and earlier didn't provide data encryption within the database, but you could do it yourself, using external COM components, for example. Third-party encryption products are also available for you to use. SQL Server 2005 provides full, internal support for data encryption using custom keys, digital certificates, or pass phrases.

Data encryption is easy enough using the mature encryption libraries, such as those built into the .NET base class library and the Win32® CryptoAPI. The difficult part is managing the keys and storing them safely and secretly. SQL Server 2005 can provide key management for you or you can take on the job yourself. Consistent with the new granularity of permissions, even individual users can encrypt data so that only they can decrypt it.

Say that you have a Customers table with a Social Security number field called SSN. This is the kind of data that you have a strong obligation to protect. Since it is going to hold cipher text, the SSN field is defined as a varbinary(60) field. In this case I'll use a certificate generated by SQL Server and symmetrical keys. Because the encryption is contained totally within SQL Server, which manages the keys, there is no need to transmit the key across the network, allowing safe use of symmetrical keys.

The first step in the encryption process is to create the master key used in the database for encryption, a digital certificate, and the symmetric key used for the data encryption (see Figure 6).

Figure 6 Create Master Key

    WITH SUBJECT = 'User1Cert'

You can then use the symmetric keys to encrypt the data in the SSN field. The code in Figure 7 assumes that you are connected to the database as User1 or have switched security contexts to that user.

Figure 7 Open Symmetric Key

INSERT INTO Customers 
    VALUES(1, EncryptByKey(Key_GUID('User1Key'), '987-65-4321'))
INSERT INTO Customers 
    VALUES(2, EncryptByKey(Key_GUID('User1Key'), '123-45-6789'))

For simplicity, this example uses only the CustID primary key and the encrypted SSN field, and ignores the data for other fields in the table.

The code in Figure 7 uses the new T-SQL EncryptByKey function to encrypt the data inserted using the User1Key created earlier. You could also use the EncryptByCert to encrypt the data using a certificate or EncryptByPassphrase to use a phrase that SQL Server uses to create a key. Any user that tries to read the data, even with full access permissions on the table, will be unable to read the encrypted data.

Retrieving and decrypting the data follows similar steps, using the DecryptByKey function, as shown here:

SELECT CustID, CONVERT(varchar, DecryptByKey(SSN)) as SocialSecurity
    FROM Customers

The CONVERT function call is necessary because DecryptByKey returns varbinary data rather than textual data.

This simple example barely scratches the surface of the encryption features that are available in SQL Server 2005. As implied by the code, you can create a complete hierarchy of keys that secure the objects and keys below it. You can also create unlimited keys and certificates that are associated with various users and roles.


As an administrator charged with the security of your database servers you're really going to like SQL Server 2005. Of course, migrating existing databases will take some work from a security perspective, a change more akin to the move from SQL Server 6.5 to 7.0 rather than SQL Server 7.0 to 2000. But with the granular permissions scheme, SQL Agent proxy accounts, data encryption, execution contexts, and other changes I've covered here, you have all the tools that are necessary to make your database servers truly resilient against today's class of attacks.

Permission Types

The following are some of the new permissions you can set in SQL Server 2005 and what they accomplish.

CONTROL Confers owner-like permissions that effectively grant all defined permissions to the object and all objects in its scope, including the ability to grant other grantees any permissions. CONTROL SERVER grants the equivalent of sa privileges.

ALTER confers permission to alter any of the properties of the securable objects except to change ownership. It inherently confers permissions to ALTER, CREATE, or DROP securable objects within the same scope. For example, granting ALTER permissions on a database grants that user permission to change its tables. Granting ALTER TRACE grants auditing permissions.

ALTER ANY <securable object> confers permission to change any securable object of the type specified. For example, setting ALTER ANY ASSEMBLY grants the right to change any .NET assembly in the database, while at the server level granting ALTER ANY LOGIN lets the user change any login on that server.

IMPERSONATE <login or user> confers permission to impersonate the specified user or login. This permission is necessary to switch execution contexts for stored procedures.

TAKE OWNERSHIP confers the permission to the grantee to take ownership of the securable objects.

Don Kiely, a Microsoft MVP, is a senior technology consultant who focuses on security. When he isn't writing database software, he's writing about technology, speaking about it at conferences, and training others. Reach him at donkiely@computer.org.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.