Hackers Beware

Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005

Don Kiely

This article is based on a prerelease version of SQL Server 2005. All information contained herein is subject to change.

Code download available at:SQLServerSecurity.exe(115 KB)

Contents

Permissions
Catalog Views and Metadata Visibility
User/Schema Separation
Execution Context
Encryption
Conclusion

It's an increasingly hostile world for your databases and client applications. Every day, attackers develop clever new attacks to compromise your valuable data. Fortunately, SQL Server™ 2005 offers strong new security features that directly address principles such as defense in depth and least privilege. As explained in SQL Server Books Online, Microsoft has implemented a number of security initiatives, which include reducing the attack surface area and making it easier to securely deploy SQL Server and databases, while providing better security tools and docs about maintaining high security in a changing security landscape.

In this article I'll explore the most interesting security enhancements in SQL Server 2005 from a developer's viewpoint. I covered admin security features in the Spring 2005 issue of TechNet Magazine. But there are plenty of dev-specific security enhancements I can explore, such as endpoint authentication and support for the security context of managed code that executes on the server. I'll focus only on the core relational database engine. Supporting systems such as Reporting Services, Analysis Services, and so on have their own security infrastructures that deserve whole articles to cover them adequately.

Permissions

SQL Server 2000 and earlier contained a reasonable scheme for assigning permissions to users, logins, and other principals. But when you attempt to lock down those versions of SQL Server, you're working with fairly blunt tools rather than the sharp scalpels necessary to implement strong security. Often you have to assign a user to a fixed role that has the permission the user needs as well as a dozen others that the user doesn't need. This is a gross violation of the principle of least privilege, in which each user or other principal has exactly the permissions needed, no more and no less.

The grantable permissions in SQL Server 2005 are far more specific than in its predecessors. Virtually any object has a variety of permissions grantable to virtually any principal. SQL Server still makes heavy use of roles at the server and database levels, but it is no longer necessary to add a user to a role when all the user needs is limited access to specific resources.

In the security terminology of SQL Server 2005, a principal is any individual, group (role), or process that can request access to a protected resource and be granted permission to access it. As in previous versions of SQL Server, a principal can be defined in Windows® or can be based on a SQL Server login with no corresponding Windows principal. Figure 1 shows the hierarchy of SQL Server 2005 principals, excluding fixed server and database roles. It also shows how logins and database users can be mapped to security objects. As in previous versions, in SQL Server 2005 the scope of the influence of the principal depends on the scope of its definition. Thus, a Windows-level principal is more encompassing than a SQL Server-level principal, which in turn is more encompassing than a database-level principal. And every database user automatically belongs to the fixed Public role.

Figure 1 SQL Server 2005 Principals Hierarchy

Also notable is that objects can be secured by granting or denying permissions. Figure 2 lists the securable objects in SQL Server 2005. At the server level, you can secure network endpoints to control the communication channel into and out of the server, as well as databases, bindings, and roles and logins. At the database and schema level, virtually every object you can create is securable, particularly within the schema object used to contain database objects.

Figure 2** Securable Objects in SQL Server 2005 **

The set of fixed server and database roles is unchanged from SQL Server 2000, so you can still take advantage of those predefined bundles of permissions when users or applications require all or most of the defined permissions. Under the principle of least privilege, however, you'll rarely want to use roles that give a principal excess permissions. Although it will be a bit more work discovering the permissions required for a principal and assigning them, it can result in a far more secure database environment.

For a sense of the number of permissions available in SQL Server, you can view the builtin_permissions catalog view:

SELECT * FROM sys.fn_builtin_permissions(default)

In the April CTP this returns more than 180 rows representing all of the permissions; I'll discuss a few of the more important ones.

The CONTROL permission confers ownership rights to the grantee, including the ability to grant permissions on the securable object. CONTROL rights on one level of the hierarchy of securable objects implies those same rights for all contained objects.

The ALTER ANY entity permission confers the right to create, alter, and drop any object of the entity type. For example, the ALTER ANY DATABASE permission at the server level allows the grantee to alter any database. The ALTER ANY ASSEMBLY permission within the scope of a specific database allows the grantee to make changes to any assembly.

IMPERSONATE ON login or IMPERSONATE ON user allows the grantee to impersonate the specified server login or database user so that any operations are performed using the impersonated security credentials. This permission is critical for the EXECUTE AS feature I'll cover later in this article.

SQL Server 2005 still uses the familiar GRANT, DENY, and REVOKE scheme for assigning or refusing permissions on a securable object. The GRANT statement has been greatly expanded to cover new permission options, such as the scope of the grant and whether the principal is able to grant the permission to other principals. Cross-database permissions are not allowed. In order to grant cross-database permissions, you'll have to create a duplicate user in each database and separately assign each database's user the permission. Like earlier versions of SQL Server, activating an application role suspends other permissions for the duration that the role is active.

Granting a particular permission can convey the rights of other permissions through implication. SQL Server Books Online contains the T-SQL for an ImplyingPermissions user-defined function that assembles the hierarchy list from the sys.fn_builtin_permissions catalog view and identifies the depth of each permission in the hierarchy. After adding ImplyingPermissions to the master database, I executed this statement to produce a hierarchical list of permissions, passing in the object and permission type:

SELECT * FROM master.dbo.ImplyingPermissions('schema', 'alter')

It might be difficult at first to appreciate just how granular permissions can be in SQL Server 2005. Consider the number and types of principals available and the number of securable objects in the server and a typical database. Consider also the sheer number of available permissions and the covered and implied permissions. Creating an application is going to require much more detailed analysis of its security needs and careful control of permissions on all objects. Nevertheless, there are still some actions that do not have a grantable permission. For example, TRUNCATE TABLE requires the ALTER permission on the table, which confers the power to do more than just delete a table's rows.

Catalog Views and Metadata Visibility

One of the benefits of the highly granular permissions scheme in SQL Server 2005 is that it affords better protection of the metadata necessary to a modern database engine. SQL Server has long supported rich metadata exposure through INFORMATION_SCHEMA views defined in the SQL-92 specification, system tables at the server and database level, and a vast number of built-in system stored procedures. You could even change the underlying state of the server and its databases by modifying the system tables, albeit with great risk to system stability. Microsoft constantly reminded us that the underlying system tables could change at any time, so using them was at our own risk.

In SQL Server 2005, Microsoft has created a new set of catalog views to expose all of the metadata throughout the server. The new sys schema encapsulates the metadata views. The catalog views are read-only, which eliminates some of the hacks that were possible in earlier versions of SQL Server. Many of the views have names similar to the old system tables to make it easier to find the information you want. For example, the sysobjects metadata is now exposed through the sys.objects view. To keep us all honest, the view definitions are physically hidden in an undocumented location with no way to directly access their definitions (unless someone hacks a server and finds them). The message is nevertheless clear: use the catalog views to get at all of the server and database data and forget about previous hacks. Stick to the stored procedures, T-SQL, and other formal ways of configuring and tweaking SQL Server. It's all for the sake of security and stability.

From a security standpoint, a benefit of using views to expose metadata is that the data returned from a catalog view is filtered according to the permissions of the user context under which the data is requested. For example, it used to be that a user who had permission to access the sysobjects table in a database—and most users did in many applications—could run a query against the table and see every available object in the database, whether or not she had access to each object. In SQL Server 2005, the user has to have some minimal access to a table, such as SELECT permission, to see the table. If the user had access to the database but not to any of its objects, a query against sys.objects returns no rows.

To see how this works, run the following code as an admin:

USE master CREATE LOGIN User1 WITH password = 'myPassword' -- Use the database of your choice USE AdventureWorks CREATE USER User1 EXECUTE AS LOGIN = 'User1' SELECT * FROM sys.objects -- Because the user has no permissions in the db, no rows are returned REVERT

The code starts by creating a User1 login on the server, then adds a user mapped to that login to the sample AdventureWorks database. Then it changes the execution context to User1 and retrieves data from sys.objects. Nothing is returned from the catalog view, because while User1 has access to the database, it has no permissions on any objects. The REVERT statement reverts the execution context to the admin user.

In order to return data, you must give the user a couple of permissions. The following code grants SELECT permission on the Contact table and EXECUTE permission on the dbo.uspGetBillOfMaterials stored procedure:

-- Give User1 rights to a table and stored procedure GRANT SELECT ON Person.Contact TO User1 GRANT EXECUTE ON dbo.uspGetBillOfMaterials TO User1 -- Execute again as User1 EXECUTE AS LOGIN = 'User1' SELECT * FROM sys.objects REVERT

When you execute the catalog view as User1, you get nine rows from the sys.objects catalog view, including metadata for the Contact table and uspGetBillOfMaterials stored procedure as well as related objects such as table constraints and a trigger. This demonstrates how granting a permission at one level of the object hierarchy grants related permissions on child objects.

As you'd expect, sysadmins and sa can see everything on the server in the system catalog views and database owners can see everything in their databases. The permissions-based filtering also applies to system stored procedures that reveal information about different objects, such as sp_help and sp_helpdb. Since these system stored procedures read the system catalog views, they are filtered according to the permissions of the principal under which the procedures run. You must be careful, though, since the limits on metadata visibility don't yet apply to all of the metadata functions, such as OBJECTPROPERTY.

Most of the old system tables, stored procedures, and views are still available for use, except that they are now all exposed as read-only views. They are there for backward compatibility and do not expose any of the new features of SQL Server 2005. Unless you are supporting old code from an earlier version, it is best to use the new system catalog views.

User/Schema Separation

The ANSI SQL-99 specification defines a database schema as a collection of database objects owned by a single principal that forms a single namespace of objects. A schema contains database objects such as its tables, views, stored procedures, functions, types, and triggers—which is a convenient way of grouping objects so a database can reuse object names and group objects under different owners.

In SQL Server 2000, the owner and schema were indistinguishable, as shown in the top portion of Figure 3. If an admin created a user called Fred in a database, there was also a schema called Fred created automatically that hid behind Fred the user. If Fred the user logged into SQL Server and created a table, Table1, the name of the table would be Fred.Table1. The same held for other objects Fred created, such as Fred.StoredProcedure and Fred.View1. If Fred were a database owner, then objects Fred1 created would be part of the dbo schema instead.

Figure 3** Users and Schemas in SQL Server **

A problem arose when it would become necessary to change the ownership of objects, such as when Fred leaves the company and George takes over Fred's job. A system admin would have to change ownership of all of the objects owned by Fred. Any T-SQL or client application code that referred to Fred.Table1 would have to be changed to George.Table1 after George took ownership of the table. Depending on the number of objects Fred owned and how many apps had the name embedded in them, this could get complicated.

SQL Server 2005 solves this problem and more closely implements the SQL-99 schema by separating the user from the schema, as shown in the bottom part of Figure 3. When you create a new user called Fred using the new DDL statements, SQL Server no longer automatically creates a schema with the same name. Instead, you must explicitly create a schema and assign ownership. Because all of the database objects are now contained in the MySchema schema, which Fred initially owns, it becomes simple to change ownership of all the schema's objects by simply changing the ownership of the schema to George. Each user can also have a default schema assigned to it, so that any objects referenced by name without the schema reference will be assumed to be in the default schema. So in the bottom part of Figure 3, if user Fred has MySchema as his default schema, he can refer to the table as either MySchema.Table1 or simply as Table1. George, who perhaps does not have a default schema associated with his user name, would have to refer to the table as MySchema.Table1. Any user with no specified default schema has dbo as the default.

Figure 4 shows how this works. The code starts by creating a login called Carol and a user called Carol in the Pubs database with CREATE TABLE permission. The security context of the running code is changed to Carol, and then Carol attempts to create a table. This attempt fails with an error: "Specified schema name 'dbo' either does not exist or you do not have permission to use it." In this case, the problem is that Carol doesn't have permission to create objects in the dbo schema. Since she doesn't have a default schema specified, the CREATE TABLE statement is attempting to create dbo.table1.

Figure 4 Separation of Users and Schemas

USE pubs -- Create a new SQL Server login using GUI: 'Carol" with -- password 'carol5', default DB pubs CREATE LOGIN Carol WITH PASSWORD = 'carol5' CREATE USER Carol FOR LOGIN Carol GRANT CREATE TABLE TO Carol EXECUTE AS LOGIN = 'Carol' GO CREATE TABLE table1 (tID int) -- Error: Don't have permission to create object in dbo schema -- Revert to admin REVERT -- Create schema and link to login CREATE SCHEMA CarolSchema AUTHORIZATION Carol EXECUTE AS LOGIN = 'Carol' GO CREATE TABLE table1 (tID int) -- Still an error. Just because there is a schema, doesn't mean -- it's the default CREATE TABLE CarolSchema.table1 (tID int) -- Success at last! -- Clean up REVERT DROP TABLE CarolSchema.table1 DROP SCHEMA CarolSchema DROP USER Carol DROP LOGIN Carol

The code then reverts to the admin user who signed in to run this code and creates a CarolSchema, giving ownership of the schema to Carol. Then, again running in Carol's security context, it attempts again to create the table. The attempt again fails with the same error message. Just because Carol has a schema at her disposal doesn't mean that SQL Server will use it by default. She is finally able to successfully create the CarolSchema.table1 when she explicitly uses that object name and a schema that she has permissions to use. The second failed attempt at creating the table, after the CarolSchema existed, would have succeeded had Carol had a default schema set, either when the user Carol was created or by adding it to the user, as shown here:

CREATE USER Carol FOR LOGIN Carol WITH DEFAULT_SCHEMA = CarolSchema -- or ALTER USER Carol WITH DEFAULT_SCHEMA = CarolSchema

Execution Context

SQL Server has long supported ownership chaining to ensure that a user who is executing code that accesses data has the proper permissions. As long as the user calling the code had execute permissions and the owner of the code was the owner of the two tables and the view that was accessed, for example, no further permissions were checked and the caller received the requested data. If the ownership chain was broken—if, for example, the owner of the code didn't own the referenced view—the caller's security context was checked.

If the caller had permission to access the view, the data was returned. If she didn't, the data would not be returned. This is how SQL Server 2000, and previous versions, works; this is fine when you want to give users access to data through a stored procedure or function when they didn't have the underlying rights to the data. Ownership chaining has some limitations. One is that it only applies to data manipulation operations and not to dynamic SQL.

But maybe you don't want to use the caller's permissions to validate access to data. What if you want to create a stored procedure that uses another user's security context to check permissions as part of a carefully designed security framework to protect data? You didn't really have any easy options before SQL Server 2005, which introduces the concept of execution context. Now, when you define stored procedures, data manipulation triggers, and user-defined functions (except inline table-valued), you can use the EXECUTE AS clause to specify which user's permissions SQL Server uses to validate access to objects and data referenced by the procedure. For example, you can specify that the permissions associated with the creator of the code are always used to access the data. The following is an example that runs the code with a specific user's permissions. In this case, the user ec must have SELECT permission on the titles table, since the execution context is that of ec:

USE pubs CREATE LOGIN ec WITH PASSWORD = 'ecpassword' CREATE USER ec FOR LOGIN ec CREATE PROCEDURE GetTitlesEC(@Table varchar(40)) WITH EXECUTE AS 'ec' AS EXECUTE('SELECT * FROM ' + quotename(@Table)) GO

SQL Server 2005 provides four execution context options. EXECUTE AS CALLER specifies that the code is executed in the context of the caller of the module. The caller must therefore have execute permission on the module as well as access permissions on all of the underlying objects. SQL Server only checks permissions for broken ownership chains, so if the owner of the code also owns the underlying objects, only the module's execute permission is checked. This is the default execution context in order to ensure backward compatibility.

EXECUTE AS 'user_name' specifies that the code executes in the security context of the specified user. This is a great option if you don't want to rely on ownership chaining and instead create a user with the necessary permissions to run the code.

EXECUTE AS SELF is a shortcut notation for specifying that the module executes under the security context of the user creating or altering the module. SQL Server saves the actual user name associated with the module rather than SELF.

EXECUTE AS OWNER specifies that the security context is that of the current owner of the module. If no owner is specified, the context of the containing schema's owner is used. The owner must be a singleton account of a specific user and not a role. This is a great option when you want to be able to change the module's owner without changing the module itself.

There are a few limitations to changing the execution context of code. The creator of the module must have IMPERSONATE permissions for the specified user. You never need to have this permission to impersonate yourself. You also cannot drop the specified user from the database until the execution context of all modules has been changed to no longer use that user. Ownership chaining doesn't apply to dynamic SQL executed within the module. Like SQL Server 2000, the execution context must have permissions on the underlying objects accessed by the dynamic SQL.

Encryption

The best protection for a valuable resource is layer upon layer of security—a principle known as defense in depth. An attacker has to pierce one layer after another before attaining the prize. Far too often, hackers have been able to compromise network security, then machine security, then database security, gaining free access to the valuable data.

Encrypted data in SQL Server is a final line of defense. Even if an attacker successfully gains access to a database, she must still decrypt the data. Given today's strong encryption algorithms, without a decryption key the attacker faces a nearly insurmountable challenge. Encryption is never free, of course. You might be tempted to encrypt all the data stored in SQL Server, including publicly accessible data such as your product catalog. But that could incur a massive performance hit because encryption is processor-intensive. Multiply the processing cycles required to encrypt or decrypt one short string by 10 million rows of data in a table, and you can easily bring a server to its knees. You should consider the level of protection required, and use encryption only when protecting a field is worth the cost incurred.

SQL Server 2005 provides a variety of key types and encryption algorithms. For symmetric keys, you can use RC4, RC2, the DES family, and the AES family of algorithms. For asymmetric keys, it provides RSA, and for certificates it uses the Internet Engineering Task Force's X.509 V1 standard.

The hardest part of encryption is key management—keeping secrets secret. If an attacker obtains the symmetric key used to encrypt your data, he can access your data and can change it with abandon. SQL Server 2005 lets you manage keys yourself, or you can have SQL Server manage them for you, using a hierarchy of encryption objects to protect various types of keys at different scopes within the server (as shown in Figure 5).

Figure 5** Encryption Hierarchy in SQL Server 2005 **

At the top of Figure 5 is the Service Master Key, the mother of all keys and certificates in SQL Server. The Service Master Key, a symmetric key that directly or indirectly encrypts all keys on the server, is created automatically when you install SQL Server. If it is compromised, an attacker can eventually crack every key in every database. For that reason, it's protected by the Data Protection API (DPAPI) in Windows. You can access it using the service account name under which SQL Server is running.

SQL Server takes care of managing the Service Master Key for you, although you can perform certain maintenance tasks on it to dump it to a file, regenerate it, and restore it from a file using the T-SQL statements shown in Figure 6.

Figure 6 Maintaining the Service Master Key

-- Export the service master key to store in safe location -- The file is encrypted using the supplied password as the key DUMP SERVICE MASTER KEY TO FILE = 'c:\servicemasterkey.dat' PASSWORD = 'S3@fBZir2D^P$x5P&tNr^uR!@wGW' -- Reload the service master key LOAD SERVICE MASTER KEY FROM FILE = 'c:\servicemasterkey.dat' PASSWORD = 'S3@fBZir2D^P$x5P&tNr^uR!@wGW' -- Regenerate the service master key. Be careful! Decrypts all keys -- encrypted with old master key then encrypts them with the new key, -- which can require a lot of processing time. ALTER SERVICE MASTER KEY REGENERATE

The ALTER SERVICE MASTER KEY statement also has options to change the recovery option and the service account used to encrypt the key in DPAPI. Most of the time, though, you won't need or want to make any changes to the key.

Within the scope of a database, the database master key is the root encryption object for all keys, certificates, and data in the database. Each database can have a single master key; you'll get an error if you try to create a second key.

You must create a database master key via CREATE MASTER KEY T-SQL before using it with a user-supplied password:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'WeZ#6hv*XHq#akAEaqcr7%CUP3aQ'

The resulting key is encrypted with triple DES and stored twice. One storage location is in the sys.symmetric_keys database table, encrypted by the supplied password, and the second is the sys.databases table in the master database, encrypted using the Service Master Key. Such duplicate storage allows automatic opening of the master key.

Detaching a database with an existing master key and moving it to another server can cause a problem, since the new server's Service Master Key will be different. ALTER MASTER KEY has options to drop encryption by the Service Master Key on the old server and then add it back on the new server. Otherwise, the master key will always have to be explicitly opened before use.

Once the database master key exists, you can use it to create any of three types of keys, depending on the encryption you need:

• Asymmetric keys are used for public key cryptography with a public and private key pair.
• Symmetric keys are used for shared secrets where the same key both encrypts and decrypts data.
• Certificates are essentially wrappers for a public key.

Figure 5 shows how these keys and certificates can be used to encrypt other keys and data. Asymmetric keys can encrypt symmetric keys and data, symmetric keys can encrypt other symmetric keys and data, and certificates can encrypt symmetric keys and data. Symmetric keys can also be created using a custom password if you want to handle key management yourself. Of course, all of these are ultimately encrypted using the database master key.

The certificate is the same sort of object that is used in the context of digital signatures when talking about signing a document, e-mail, or file. The certificate object wraps the public key used in asymmetric encryption with plenty of options for how it is created. But because SQL Server takes care of all the details, it hides the steps needed to create certificates in other environments.

You can have SQL Server create a certificate for its own use or import a certificate created by any trusted certificate authority, such as Microsoft Certificate Server or VeriSign. Options include setting a password to use to encrypt the private key or letting SQL Server use the database master key, as well as setting the creation and expiration dates. You can import the certificate from a file or read it from a signed executable or loaded .NET assembly. The following code creates two certificates, one that is associated with User1 and does not expire, and another for User2 that expires at the end of 2005:

-- Create a certificate associated with User1, encrypted with the -- database master key. The database master key must already exist. CREATE CERTIFICATE User1Certificate AUTHORIZATION User1 WITH subject = 'Certificate For User1' GO -- Create a certificate for User2 that is limited CREATE CERTIFICATE User2Certificate AUTHORIZATION User2 WITH subject = 'Certificate For User2', EXPIRY_DATE = '12/31/2005', ENCRYPTION_PASSWORD = 'q%dsabciJ&#QZk#wM5G!WB36z5m7'

Once the certificate exists, you can encrypt data using one of the new T-SQL functions that supports encryption, EncryptByCert. Imagine that you have a table called Customer defined like this:

CREATE TABLE Customer ( CustId int, name nvarchar(30), City varchar(20), CreditCardType varbinary(300), CreditCardNumber varbinary(300), Notes varbinary(4000)) GO

Encrypted data is binary, so the table uses varbinary fields to hold it. Because the encrypted data includes the digital certificate, the field lengths must be rather large, even to hold simple strings like "Visa" and a credit card number. Here is how you can insert a row of data into this table:

INSERT INTO Customer VALUES (1, 'Don Kiely', 'Fairbanks', EncryptByCert(Cert_ID('User1Certificate'), 'Visa'), EncryptByCert(Cert_ID('User1Certificate'), '1234-5678-8765-4321'), EncryptByCert(Cert_ID('User1Certificate'), 'This customer is a real flake. Don''t trust him!'))

EncryptByCert takes two parameters, the certificate ID, which you can obtain with the Cert_ID function, the name you gave the certificate, and the data to be encrypted.

You can see the benefit of encrypting data when you select data from the table. Running a SELECT * FROM Customer in SQL Server Management Studio gives the result shown in Figure 7. Because the INSERT statement uses multiple calls to the Encrypt function, each field can be encrypted using different methods.

Figure 7 SELECT on Table with Encrypted Fields

To decrypt the data as part of a SELECT statement, use DecryptByCert as shown here:

SELECT CustID, Name, City, CONVERT(VARCHAR, DecryptByCert(Cert_ID('User1Certificate'), CreditCardType)) AS CardType, CONVERT(VARCHAR, DecryptByCert(Cert_ID('User1Certificate'), CreditCardNumber)) AS CardNumber, CONVERT(VARCHAR, DecryptByCert(Cert_ID('User1Certificate'),Notes)) AS Notes FROM Customer

Because DecryptByCert returns varbinary data, you usually need to convert it to some other data type, here a varchar. This returns the clear text, as shown in Figure 8.

Figure 8** SELECT Using DecryptByCert **

A very common type of encryption, one performed with asymmetric keys, is frequently called public key encryption. Asymmetric keys in SQL Server 2005 use the RSA algorithm with key sizes of 512, 1,024, or 2,048 bits. You can encrypt the private key generated either with a password you specify or with the database master key. You can also import the keys from a disk file, an executable file, or a .NET assembly loaded in memory. The following code shows a couple of different ways to create the keys. The first uses a secret managed by the user and the second uses the database master key managed by SQL Server:

-- Create an asymmetric key pair with the private key protected by a -- user-supplied password. CREATE ASYMMETRIC KEY User1AsymmetricKey AUTHORIZATION User1 WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'AVeryVerySecretPassword' -- Create another that is protected by the database master key CREATE ASYMMETRIC KEY User2AsymmetricKey AUTHORIZATION User1 WITH ALGORITHM = RSA_2048

Once the keys are available in the database, you can use the EncryptByAsymKey function to encrypt the data. Like the EncryptByCert function, the first parameter is the ID of the key you want to use, which you can obtain with the AsymKey_ID function. There is no need to use the same key for every encrypted field in a record, although that's the approach used in Figure 9.

Figure 9 Encrypting Data

INSERT INTO Customer VALUES (2, 'Joe Public', 'North Pole', EncryptByAsymKey(AsymKey_ID('User1AsymmetricKey'), 'MasterCard'), EncryptByAsymKey(AsymKey_ID('User1AsymmetricKey'), '8765-4321-1234-5678'), EncryptByAsymKey(AsymKey_ID('User1AsymmetricKey'), 'A total sucker. Sell him up!')) INSERT INTO Customer VALUES (3, 'Jane Doe', 'Ester', EncryptByAsymKey(AsymKey_ID('User2AsymmetricKey'), 'Discover'), EncryptByAsymKey(AsymKey_ID('User2AsymmetricKey'), '9999-8888-7777-6666'), EncryptByAsymKey(AsymKey_ID('User2AsymmetricKey'), 'Favors shoes of every style and color.'))

Use the DecryptByAsymKey function to decrypt each column's data, once again, using the AsymKey_ID function with the name of the asymmetric key used to encrypt the data. In Figure 10, User1AsymmetricKey was created with a password, so you need to pass that password as the third field of the DecryptByAsymKey function call. You'll usually need to use the CONVERT function to convert the varbinary data to human-readable (see Figure 10).

Figure 10 Decrypting with DecryptByAsymKey

-- View the decrypted data. Have to pass in the password used to protect -- the private key. SELECT CustID, Name, City, CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User1AsymmetricKey'), CreditCardType, N'AVeryVerySecretPassword')) AS CardType, CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User1AsymmetricKey'), CreditCardNumber, N'AVeryVerySecretPassword')) AS CardNumber, CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User1AsymmetricKey'), Notes, N'AVeryVerySecretPassword')) AS Notes FROM Customer WHERE CustID = 2

For data encrypted with User2AsymmetricKey (see Figure 11), which was created with the database master key, there is no need to pass in any credentials because SQL Server is managing the key for you. Hopefully you are starting to see that it is far easier to let SQL Server manage keys for you.

Figure 11 Decrypting without Passing Credentials

-- View CustID 3. No need to pass password since protected with database -- master key. SELECT CustID, Name, City, CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User2AsymmetricKey'), CreditCardType)) AS CardType, CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User2AsymmetricKey'), CreditCardNumber)) AS CardNumber, CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('User2AsymmetricKey'), Notes)) AS Notes FROM Customer WHERE CustID = 3

If SQL Server creates the asymmetric key pair, you can get the public key for transmitting to others with the sys.asymmetric_keys catalog view, which exposes the public key in the public_key field. The following code returns the public keys associated with each asymmetric key pair you create:

SELECT Name, Public_Key FROM sys.asymmetric_keys

Certificates and asymmetric keys provide industrial-strength encryption for SQL Server data, particularly when you need to move the data outside of the database and protect it en route. But if you need to protect the data on the server and not worry about it in other locations, symmetric keys are a good choice. This is a fairly common database scenario, where data is stored securely, decrypted on the server, sent to the client and displayed to an authorized user. Symmetric keys require far fewer processing cycles than other forms of encryption. Because it is so well-suited to database applications, symmetric key encryption in SQL Server is quite flexible.

Creating symmetric keys is similar to creating certificates and asymmetric keys. The CREATE SYMMETRIC KEY statement has several options. One option is how the key is itself encrypted when stored in the database. Referring back to Figure 5, you can protect a symmetric key using a certificate, asymmetric key, user-specified password, or another symmetric key. Others include the encryption algorithm used, whether to use a user-supplied passphrase to generate the key, and an optional identity phrase. This phrase generates a GUID that is used to tag data encrypted with a temporary key. SQL Server supports the majority of the most widely used symmetric key algorithms, including DES, triple DES, and AES. The algorithm must be installed in Windows on the server.

When considering the options for creating symmetric keys and whether to manage the key yourself or let SQL Server do it for you, it is important to consider how the optional password is protected. If you specify a password to encrypt the key, triple DES is used to derive a key from the password, no matter what algorithm you elect to use to encrypt the data with the resulting symmetric key. It is quite possible, then, to use stronger encryption for data than that used to protect the password protecting the key.

Figure 12 shows a few of the many options for creating symmetric keys. The first example creates a key with triple DES secured by an existing certificate, giving User1 ownership of the resulting key. Notice that you don't have to explicitly open the certificate in order to use it, unlike a symmetric key. The second sample creates a similar symmetric key, but instead of a certificate, the user has chosen to manage the key by supplying a password. The third sample omits the AUTHORIZATION clause so that the key is owned by dbo, and uses the RC4 algorithm. This sample uses an existing asymmetric key to protect the new symmetric key. The last sample creates another symmetric key owned by dbo that uses DESX to encrypt data, but is itself protected by another symmetric key. Before you use the existing symmetric key to encrypt the new key, you have to explicitly open the existing key.

Figure 12 Create Symmetric Keys in SQL Server 2005

-- Create a key using Triple DES secured by a certificate and associate -- it with User1 CREATE SYMMETRIC KEY User1SymmetricKeyCert AUTHORIZATION User1 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE User1Certificate -- Create a key using Triple DES secured by a password and associate -- it with User 2 CREATE SYMMETRIC KEY User2SymmetricKeyPwd AUTHORIZATION User2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = 'AGreatPassword' -- Create a key using RC4 secured by an asymmetric key and owned by dbo CREATE SYMMETRIC KEY GenericSymmetricKeyAsym WITH ALGORITHM = RC4 ENCRYPTION BY ASYMMETRIC KEY User2AsymmetricKey -- Create a key using DESX secured by another symmetric key and -- owned by dbo OPEN SYMMETRIC KEY User1SymmetricKeyCert USING CERTIFICATE User1Certificate CREATE SYMMETRIC KEY GenericSymmetricKeySym WITH ALGORITHM = DESX ENCRYPTION BY SYMMETRIC KEY User1SymmetricKeyCert CLOSE SYMMETRIC KEY User1SymmetricKeyCert

Inserting and selecting data encrypted with symmetric keys is similar to the method used for certificates and asymmetric keys, but is a bit more involved, as you can see in the sample code download (available on the MSDN®Magazine Web site). The code uses the EncryptByKey and DecryptByKey functions to convert the data, along with the Key_GUID function to retrieve the ID of the specified key. You have to explicitly open a symmetric key before using it, since you can't create such a key that is encrypted by the database master key. The sample creates and uses a GenericSymmetricKeySym key. Because this key is itself protected by the symmetrical key User1SymmetricKeyCert, you have to first open the symmetric key that protects the key you want to use. This demonstrates that you can have multiple symmetric keys open in a database at any time. SQL Server knows which key to use to decrypt the data; you don't have to specify it in the DecryptByKey function.

One of the interesting benefits of the SQL Server 2005 implementation of encryption is that it provides a way to let users see only their own data. By using the AUTHORIZATION clause as I've done in many of the samples in this article, you can give ownership of the key or certificate to a user who can then grant rights to other users to see the data.

Conclusion

SQL Server 2005 has an unprecedented level of support for security in a database engine. It provides a rich set of features developers can use to create secure applications that can access data on a secure server. Highly granular permissions let you practice the security principle of least privilege, granting a user just the permissions necessary to do a job and no more, preventing attacks that take advantage of bloated permissions. These and other security enhancements in SQL Server 2005 provide all the tools you need to weather today's sophisticated attacks on your data.

Don Kiely, MVP, MCSD, is a senior technology consultant and developer who focuses on security, particularly in the development of distributed Windows Forms and ASP.NET applications. He also writes about technology, speaks at conferences, and trains others. Reach Don at donkiely@computer.org.