• Article

SQL Server 2008

Security

Rick Byham

 

At a Glance:

  • Enhancements to encryption
  • Enhancements to authentication
  • Security auditing
  • Policy-based management

SQL Server 2008 delivers many enhancements and new features that are designed to improve the overall security of your database environment. It adds key encryption and authentication capabilities and introduces a new auditing system to

help you report on user behavior and meet your regulatory requirements.

In this article, I'll give you an overview of the most important changes you'll find in store for SQL Server® 2008 security. One of the first things you might notice is that the SQL Server 2005 Surface Area Configuration tool has been discontinued. The protocol options that were exposed by the Surface Area Configuration tool are now available in the Configuration Manager tool. However, enabling and disabling features is now done using the new SQL Server 2008 Policy-Based Management framework.

Enhancements to Encryption

There are two major improvements in the area of encryption. First, SQL Server can now use encryption keys stored on an external third-party hardware security module. And second, data stored in SQL Server can be encrypted in a method that is transparent to the applications that connect to the database. This means database administrators can easily encrypt all of the data stored in an entire database without having to modify existing application code.

The first improvement is made possible by the new Extensible Key Management (EKM) feature, which is available in the Enterprise, Developer, and Evaluation editions of SQL Server 2008. EKM enables third-party vendors of enterprise key management and hardware security module (HSM) solutions to register their devices in SQL Server. Once these devices are registered, users can use the encryption keys stored on these modules.

These vendors can even expose advanced encryption features (such as key aging and key rotation) in these modules. In some configurations, this allows for data protection from database administrators who are not members of the sysadmin group. The T-SQL cryptographic statements can then encrypt and decrypt data using the keys stored on the external EKM device.

Another new feature, transparent data encryption, allows you to encrypt database files without having to alter any of your applications. It performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is secured with a certificate stored in the master database of the server. The diagram in Figure 1 illustrates the architecture that enables transparent data encryption.

Figure 1 Architecture of transparent data encryption

Figure 1** Architecture of transparent data encryption **(Click the image for a larger view)

This is useful for protecting data at rest. While you can take several precautions to help secure your database, such as encrypting confidential assets and placing a firewall around the database servers, the physical media on which the database is stored (even backup tapes) offers a different vulnerability. A malicious user could steal this media and potentially access the stored data.

Transparent data encryption, however, lets you encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This can help your organization comply with many laws, regulations, and industry guidelines regarding the appropriate protection of data.

Transparent data encryption allows software developers to encrypt data using Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) encryption algorithms. Encryption of the database file is performed at the page level, with pages being encrypted before they are written to disk and then later decrypted when read into memory. Backup files of databases that have transparent data encryption enabled are also encrypted by using the database encryption key.

To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. Without the certificate or asymmetric key, the database can't be restored. So be sure to retain any keys as long as you may need access to related backups.

Enhancements to Authentication

As you probably know, Kerberos is a network authentication protocol that is used to provide a highly secure means to mutually authenticate client and server entities (or security principals) on a network. Kerberos helps users mitigate security vulnerabilities, such as luring and man-in-the-middle attacks. Relative to Windows® NTLM authentication, Kerberos is more secure, more robust, and offers better performance.

To authenticate a connection mutually using Kerberos, the Service Principal Names (SPN) of a SQL Server instance must be registered in the Active Directory®, and a client driver must provide a registered SPN when connecting. In SQL Server 2008, Kerberos authentication has been expanded to all network protocols, including TCP, Named Pipe, Shared Memory, and Virtual Interface Adapter (VIA). By default, the client driver automatically infers a correct SPN for a SQL Server instance to which it connects. You can also explicitly specify an SPN in the connection string parameter for better security, control, and troubleshooting.

Internet Information Services (IIS) no longer provides access to ASP.NET, Report Manager, or the Report Server Web service. In SQL Server 2008, Reporting Services handles all authentication requests through a new authentication subsystem that supports Windows-based and custom authentication.

Reporting Services now hosts the Microsoft® .NET Framework and ASP.NET technologies built into the SQL Server common language runtime (CLR), and Reporting Services also uses the HTTP.SYS capabilities of the OS. The Report Server includes an HTTP listener that accepts requests that are directed to a URL and port that you define during server configuration. URL reservations and registration is now managed directly by the report server through HTTP.SYS.

Security Auditing

SQL Server Audit is a new feature that will let you create customized audits of database engine events. This feature uses extended events to record information for audits, and it provides the tools and processes you need to enable, store, and view audits on various server and database objects.

SQL Server Audit is also faster than SQL Server Trace, and SQL Server Management Studio makes it easy to create and monitor the audit logs. You can audit to a more granular level now, capturing SELECT, INSERT, UPDATE, DELETE, REFERENCES, and EXECUTE statements for individual users. Furthermore, SQL Server Audit is fully scriptable with T-SQL statements CREATE SERVER AUDIT and CREATE SERVER AUDIT SPECIFICATION and their related ALTER and DROP statements.

To set up auditing, you create an audit and specify the location where the audited events will be recorded. Audits can be saved to the Windows Security Log, the Windows Application Log, or to a file at a location that you specify. You name the audit and configure its characteristics, such as the path to the audit file and its maximum size. You can also select to have SQL Server shut down if auditing fails. And if you need to log the audited events to more than one location, you just create more than one audit.

The next step is to create one or more audit specifications. A server audit specification collects information about the instance of SQL Server and includes server scoped objects, such as logins and server role membership. It also includes database information that is managed in the master database, such as the right to access a database. When you define an audit specification, you indicate which audit will receive the monitored events. You can define multiple server audits and multiple server audit specifications, but each server audit can include only one enabled server audit specification at a time.

You can also create database audit specifications that monitor events in a single database. You can add multiple database audit specifications to an audit, but one server audit can enable only one database audit specification per database at a time.

The SQL Server audit action events used for server audit specifications are grouped into collections of related audit action events. These are exposed as audit action groups. When you add a group to the audit specification, you monitor all the events included in that group. For example, there is an audit action group called DBCC_GROUP that exposes the DBCC commands. The DBCC commands, however, are not available for auditing individually.

There are 35 audit action groups available for the server, some of which are closely related to one another. For example, there is a SUCCESSFUL_LOGIN_GROUP, a FAILED_LOGIN_GROUP, and a LOGOUT_GROUP. There is also an AUDIT_ CHANGE_GROUP Audit Action Type you can use to audit the auditing process.

Database audit specifications can also specify groups of audit action events collected into database-level audit action groups. In addition to the audit action groups, database audit specification can include individual audit action events to audit data manipulation language statements. These events can be configured to monitor the entire database or just specific database objects. The SELECT audit action, for instance, can be used to audit SELECT queries for a single table or an entire schema. These events can also be configured to monitor actions by specific users or roles—such as all db_writers.

You can use the SELECT audit action, for example, to audit SELECT queries for a single table by user Mary or the FINANCE_DEPT database role or the public database role. Clearly, this offers a lot of control and flexibility for creating the audits you need.

Dependency Reporting

Dependency reporting has been improved with a new catalog view and new system functions. If you use sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, and sys.dm_sql_referenced_entities, you can report on cross-server, cross-database, and database SQL dependencies for both schema-bound and non-schema-bound objects.

New Database Roles

There are changes to the database roles included in the msdb database. The db_dtsadmin role has been renamed to db_ssisadmin, the db_dtsltduser role has been renamed to db_ssisltduser, and the db_dtsoperator role has been renamed to db_ssisoperator. To support backward compatibility, the old roles are added as members of the new roles when servers are upgraded.

In addition to these changes, new database roles have been added to support new SQL Server 2008 features. In particular, the msdb database includes new roles for server groups (ServerGroupAdministratorRole and ServerGroupReaderRole), Policy-Based Management (PolicyAdministratorRole), and the data collector (dc_admin, dc_operator, and dc_proxy). And the management data warehouse database also includes new roles for the data collector (mdw_admin, mdw_writer, and mdw_reader).

FILESTREAM Security

SQL Server now includes support for FILESTREAM storage, which lets SQL Server applications store unstructured data, such as documents and images, on the file system. This, in turn, means that client applications can benefit from the streaming APIs and performance of the file system while still maintaining transactional consistency between the unstructured and corresponding structured data.

FILESTREAM data must be stored in FILESTREAM filegroups—this is a special filegroup that contains file system directories rather than the actual files. These directories, called data containers, provide the interface between database engine storage and file system storage.

In terms of security, FILESTREAM data is secured like any other data—permissions are granted at the table or column levels. The only account that is granted NTFS permissions to the FILESTREAM container is the account under which the SQL Server service account runs. When a database is opened, SQL Server restricts access to the FILESTREAM data containers, except when access is made using the T-SQL transactions and OpenSqlFilestream APIs.

Policy-Based Management

SQL Server 2008 Policy-Based Management provides a new system for managing SQL Server. You can create policies to test and report on many aspects of SQL Server, and policies can be applied on a single database, a single instance of SQL Server, or on all the SQL Servers that you manage.

With Policy-Based Management, you can test the SQL Server configuration options and many of the security settings. And for some security settings, you can create policies that detect database servers that are out of compliance and then take steps to force them into compliance.

In SQL Server 2008, a number of features that are not essential are disabled by default to minimize your exposure to a possible attack. You can use Policy-Based Management to selectively enable any additional features that you need. You can then evaluate your configuration on a scheduled basis, getting alerts if the configuration settings are found to not match the policy.

Policy-Based Management groups together related properties and exposes them in components called facets. For example, the Surface Area Configuration facet contains properties for Ad Hoc Remote Queries, CLR Integration, Database Mail, OLE Automation, Remote DAC, SQL Mail, Web Assistant, and xp_cmdshell. You can create a policy that enables CLR Integration but disables all the other features. Your policy can include complex condition statements, such as disabling Database Mail on all instances of SQL Server unless they are named Customer_Response.

Once you have created the policy, you can then evaluate the policy on all your servers to produce a report that tells you which servers do not comply. Press the Configure button and all the noncompliant instances will be configured with the policy settings. You should also schedule the policy to run periodically to monitor the status of your servers. Surface Area Configuration facets are provided for the Database Engine, Analysis Services, and Reporting Services.

Note, however, that this Policy-Based Management is not intended to be a security enforcement mechanism. In most cases, a user with sufficient privileges can either issue statements that violate a policy or bypass the policy and perform reconfiguration actions that might violate your security policy. SQL Server 2008 Policy-Based Management should be regarded simply as an aide in monitoring your SQL Server security settings.

Facets vary in their ability to enforce settings, depending on whether their related DDL statements can run in non-autocommit modes. Sometimes a facet can force a configuration setting onto an instance of the Database Engine, but an administrator can still reconfigure the settings. Some facets can be enforced by a server trigger—this can prevent low-privileged users from changing the setting and reduce the chance that an administrator will accidentally change the setting. In this case, the administrator would have to temporarily disable the policy before changing the setting. Other facets can only report on the status of a property but can't change it. This is the case with a policy that checks the key length of a symmetric or asymmetric key (as shown in Figure 2).

Figure 2 Facet for asymmetric keys

Figure 2** Facet for asymmetric keys **(Click the image for a larger view)

There are facets for most types of database objects, many of which have security uses. For example, the login facet can determine whether the password policy is enforced for each login, and the stored procedure facet can detect if all procedures are encrypted. Other facets test the properties of users, schemas, cryptographic providers, common criteria compliance, and C2 auditing.

Windows Server 2008

SQL Server 2008 is fully tested with Windows Server® 2008, which ships with the firewall turned on. Now is a good time to review how to configure the firewall settings. And Windows Server 2008 also provides User Access Control, which you may have experienced with Windows Vista®. This restricts the privileges you automatically receive as an administrative user. These features will affect all versions of SQL Server.

In Conclusion

Security continues to be an area of deliberate improvement for SQL Server. Encryption and authentication enhancements provide new capabilities, and the new auditing system and SQL Server 2008 Policy-Based Management give you new tools to monitor the status of security compliance.

Rick Byham joined Microsoft in 1995. He worked as a SQL Server Support Engineer at Customer Support Services and then joined the SQL Server team at Microsoft Learning. He moved to the SQL Server Books Online team as a technical writer in 2003 where he is currently responsible for security documentation. Rick can be reached at rick.byham@microsoft.com.

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