Microsoft rebuilt security from the ground up in SQL Server 2005 and has evolved database security in each new version since then. SQL Server 2012 continues that trend with some interesting new features, such as new server-level roles, new hashing algorithms, and new permissions. It also adds contained databases, which let users connect to the database without authenticating at the server level. This is a great feature that solves problems when you move databases, but it sounds scary from a security perspective. There are other new security features, along with nips and tucks to existing features. There is nothing revolutionary or radically new in this release, just further tightening and adapting to the myriad ways organizations use the database server.
In this article, I'll explore some of the best new security features in SQL Server 2012. My goal is that by the time you reach the end, you'll have everything you need to decide for yourself whether your databases will be better protected in SQL Server 2012 or if security is getting way too onerous. Security in SQL Server 2005 made that version a mandatory upgrade, but it's entirely possible that you might conclude that security alone isn't a good enough reason to move to SQL Server 2012.
So, what's new in SQL Server 2012 security? I'll limit the discussion here to the database engine and cover the most interesting features, which can be broken down into four categories:
- Security management features, including default schema for groups and user-defined server roles
- Authentication features, including contained databases and authentication without logins
- Data protection features, including encryption enhancements
- Auditing features
Security Management
The enhancements to security management in SQL Server 2012 aren't big, sexy features, but the SQL Server community has been asking for them for a long time. One feature that received a lot of support through the Microsoft Connect site is default schemas for groups. This feature fixes a big hole in how SQL Server works with schemas, eliminates implicitly created schemas, and reduces the chance of using the wrong schemas in queries.
In SQL Server 2005, default schemas for users were introduced to ensure that the correct schema is used when querying, creating objects, and performing other operations. But a problematic side effect of these default schemas is that you can easily end up with lots of implicitly created schemas in a database. The Default Schemas.sql file in 142461.zip demonstrates this problem if you want to see it in action. You can download 142461.zip by clicking the Download icon in this article.
SQL Server 2012 adds the ability to define default schemas for groups as well as users, which goes a long way toward solving the problem of having implicitly created schemas and makes security management easier. You specify the default schema for a group using the CREATE USER or ALTER USER statement. For example, if Tribe is a Windows group on the Gulkana machine that's associated with a server login of the same name, you can use a statement like the following to make carolSchema the group's default schema:
CREATE USER Tribe FROM LOGIN [Gulkana\Tribe] WITH DEFAULT_SCHEMA = carolSchema;
Another long-awaited security management feature is user-defined server roles. SQL Server has provided user-defined database roles for granular database-level permissions for a long time. With custom server roles, you can finally achieve granular server-level permissions.
In previous versions of SQL Server, the only way to grant some kind of permission to users was to assign them to a built-in server role, which usually had way too many permissions. Making everyone a sysadmin became a horrible but common practice. Although this practice violated the principle of least privilege in a big way, it was often a practical necessity. Fortunately, this isn't the case in SQL Server 2005 and later because you can apply permissions at a more granular level. You can assign just about any specific server-level permission to a user. However, you can't group those permissions into a server role. SQL Server 2012 solves that problem with its support for user-defined server roles. Creating a new server role is as simple as using the CREATE SERVER ROLE statement:
CREATE SERVER ROLE LimitedDBA;
You can then grant and deny any server-level permissions you want. For example, the following code grants the CONTROL SERVER permission to the new role -- akin to granting sysadmin privileges -- then denies a few permissions to narrow down the server role privileges:
GRANT CONTROL SERVER TO LimitedDBA; DENY ALTER ANY LOGIN TO LimitedDBA; DENY ALTER ANY SERVER AUDIT TO LimitedDBA; DENY ALTER ANY SERVER ROLE TO LimitedDBA; DENY CREATE SERVER ROLE TO LimitedDBA; DENY UNSAFE ASSEMBLY TO LimitedDBA;
This is a very flexible way to grant permissions to users who are members of a group. The User-Defined Server Roles.sql file in 142461.zip contains additional code that demonstrates how to add a user to the LimitedDBA server role.
Authentication
SQL Server 2012 introduces the contained database, which isn't directly a security feature but implements a new authentication feature. Briefly, a contained database solves the problem of moving a database from one server to another. In the past, you had to move not only the database but also server-level objects such as logins and SQL Server Agent job information. Getting everything configured on the destination server was a royal pain, particularly because you had to re-create server logins and remap SIDs. With contained databases, you can avoid all these hassles.
At the heart of a contained database is a new authentication feature that lets you create a SQL Server user in a database with a password or a user associated with a Windows user account without requiring an associated login. Authentication takes place directly against the database, and a successful authentication results in a token that grants access to that database only. This provides a tightly scoped and narrow security boundary around the database so that the authenticated user can only perform database-level operations.
To demonstrate how this new authentication feature works, I created two scripts -- PubsContained.sql and Contained Databases.sql -- which you can find in 142461.zip. You first need to run PubsContained.sql to create a contained version of the old pubs sample database. To create the PubsContained database, the script uses the code:
CREATE DATABASE PubsContained CONTAINMENT = PARTIAL;
Setting the CONTAINMENT option to PARTIAL results in a contained database, whereas setting this option to NONE creates a regular database, which is the default.
Contained Databases.sql contains code to enable the contained databases feature in a SQL Server instance. (It's disabled by default.) This script also shows how you to create a SQL Server login at the database level. It's about as simple as you'd expect:
CREATE USER floyd WITH PASSWORD = '%5JiD2s^6^Y^$u26q7YL';
In addition, Contained Databases.sql shows you how to migrate user logins to the contained database using the new sp_migrate_user_to_contained system stored procedure.
Enabling contained databases on a server instance can complicate login authentication quite a bit. Figure 1 shows a simplified view of the process and how SQL Server decides whether to attempt authentication at the server or database level. There are several paths that lead to server-level authentication, but there's quite a gauntlet to run through to authenticate against a contained database. A key issue is whether the connection request specifies an initial catalog and, if so, whether that catalog is contained or not.
One important thing to notice in the diagram is that if authentication fails at the database level, it doesn't fall back and attempt server-level authentication, even if there's a server login with the same name. Also, a database is really only partially contained (Microsoft refers to them as both contained and partially contained), so it can still have server-level logins. The new contained databases feature can make diagnosing authentication problems far more complicated because of the many paths that require checking.
Data Protection
SQL Server has long had internal cryptography features, such as password hashing, but SQL Server 2005 made cryptography available for data protection as well. It added cell-level encryption and hashing support in order to provide data protection. SQL Server 2008 added some nice enhancements, including transparent data encryption and some improved hashing functions. In SQL Server 2012, Microsoft made a few small but useful encryption enhancements.
One nice change is that the HashBytes function now supports Secure Hash Algorithm-256 (SHA-256) and SHA-512, which provide significantly stronger hashes. A related change is that passwords are now hashed with SHA-512 instead of SHA-1. This raises the issue of how SQL Server will migrate existing passwords to the new algorithm when you upgrade an instance to SQL Server 2012. In general, the server will migrate individual passwords to SHA-512 whenever it has access to the clear-text password, such as when a user logs in for the first time after the upgrade or when a user changes a password.
Microsoft has wisely deprecated the RC4 encryption algorithm, which is now supported only when the server's compatibility level is set to 90 or 100 (SQL Server 2005 or SQL Server 2008) for backward compatibility. RC4 has a number of vulnerabilities, with one being that it wasn't salting the key. Consequently, encrypting a value repeatedly resulted in the same encrypted text. This makes breaking the encryption far easier than it should be. Even if you're maintaining compatibility with older versions of SQL Server, don't use RC4.
When you import certificate keys from an external source, the maximum length has increased from 3,456 bytes to 4,096 bytes. This should make it easier to use keys from third-party certificate authorities in SQL Server.
Service and database master keys now use AES256 encryption instead of Triple-DES (3DES), resulting in stronger encryption, including for backups. When you migrate databases to SQL Server 2012, it maintains 3DES encryption, but you can regenerate the master keys to upgrade their encryption.
Finally, the CREATE CERTIFICATE statement now has a FROM BINARY option that lets you directly create a certificate from the metadata of an existing certificate in SQL Server. You no longer need to export the certificate to a file to create a new certificate.
As you can see, there's nothing earth shattering in these data protection enhancements. But by dropping some unsecure features and strengthening others, SQL Server 2012 provides state-of-the-art cryptography.
Auditing
DBAs will be happy to hear that Microsoft has given auditing a lot of attention in SQL Server 2012. Probably the biggest news is that basic server auditing is now available in all editions of SQL Server, not just the heavy-metal editions. This means that you can create an audit specification across all databases in any edition. But the news is mixed: Individual database auditing is only available in the Enterprise, Developer, and Evaluation Editions. So, if you work with Developer and Evaluation Editions, make sure that your production server will be an edition with the features you need.
The nice thing about the new auditing capabilities is that you'll be able to rely less on SQL Trace for auditing-like features. This is advantageous for several reasons:
- You can create multiple audits and targets.
- Auditing performance is better than traces.
- An audit restarts with the server instance, so it persists its state.
- An audit is resilient.
- Authorized users can turn off a trace, but auditing will record who stopped an audit, preventing some kinds of insider sabotage.
An important enhancement to auditing is that it's now resilient to failure when there are problems such as a failed network connection. SQL Server will keep trying to write audit records until any problem is resolved by buffering the records.
It used to be that write failures might result in lost audit records. You could use ON_FAILURE = SHUTDOWN, but shutting down the server instance is a bit draconian and not ideal for a production database. In SQL Server 2012, you can at least specify FAIL_OPERATION so that the application gets an error and the transaction rolls back; non-audited operations can still continue. This might prove to be a mixed bag with seemingly random operations succeeding and failing, but it can work well in highly secure server instances where auditing is mandated. One issue is that if there's a problem with audit initiation at instance startup, the server instance won't start, although you can force a start with the -f option on the command line.
Auditing now supports a cap on files without a rollover. In older versions of SQL Server, you could have an indeterminate number of log files or you could specify a rollover after the server creates a predefined number of files. In the first case, you could run out of disk space. In the second case, you could lose audit data. On a well-monitored server, you could manage these options, but SQL Server 2012 provides another option: capping the files without a rollover. This lets you control the amount of information saved without losing any audit records.
To cap the files without a rollover, you use the MAX_FILES option on the CREATE SERVER AUDIT statement. When you choose that option, SQL Server will block and roll back any operations when you reach the cap, until you clear the files. This is a better option in some situations, particularly when you have a mandate to preserve all audit records, but the server still requires careful monitoring.
The Auditing.sql file in the "Download the Code" zip file at the top of the page contains code that demonstrates how to create audit objects and define where the details are sent. For example, the following code creates an audit object that sends the results to the Windows application event log and fails audited operations when there's a problem writing audit events:
CREATE SERVER AUDIT SQLServerAudit TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000, ON_FAILURE = FAIL_OPERATION); GO
You can then create an audit specification using an audit event group, which specifies the kinds of audit events you want to capture. This code specifies the capture of failed login attempts:
CREATE SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec FOR SERVER AUDIT SQLServerAudit ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON);
Finally, this code enables the audit:
ALTER SERVER AUDIT SQLServerAudit WITH (STATE = ON);
In SQL Server 2012, you can also create user-defined audit events, which means you can write whatever you want to the audit log. To do so, you'd follow these steps:
- Create and define an audit using code like that in Listing 1 (below). This code first creates an audit object. Next, it creates an audit specification that defines the USER_DEFINED_AUDIT_GROUP as the auditable action to capture. Finally, the code enables the audit.
- Add your custom information to the audit log. You can use the sp_audit_write system stored procedure in code such as:
EXEC sp_audit_write 9999, 1, N'Something in Denali succeeded!'
As you can see, the stored procedure takes three parameters: a user-defined event id of type smallint, an @succeeded bit value argument that indicates whether the action succeeded or not, and an nvarchar(4000) text value with custom information about the event. If USER_DEFINED_AUDIT_GROUP isn't enabled, SQL Server will ignore sp_audit_write.
Another potentially nice new auditing feature is filtering, which uses the same filtering mechanism as Extended Events. Filtering provides you with fine-grained control over what SQL Server writes to the audit log. The server generates all the specified auditing events, then uses the filter to determine whether to write an event to the log. Because of how it works, it's better to create audit events for specific objects instead of filtering them, but filtering can give you control over what's written.
For example, suppose you want to create an audit that recorded all events of a certain type except for those associated with a specific login, which has a server_principal_id of 266. You could use the code in Listing 2 (below) to create a server audit with a WHERE clause to filter out events where the principal has an ID of 266.
Yet another nice auditing enhancement is that you can now use T-SQL stack frame information to tell whether a query executed from a stored procedure or directly from an application. Here's a simple example from the Auditing.sql sample file. Assuming that you've already created a server audit named TSQLStackFrameAudit, you can create an audit specification like the following to capture audit events on SELECT statements executed by anyone against the dbo.Orders table in the Northwind database:
CREATE DATABASE AUDIT SPECIFICATION TSQLStackFrameSpec FOR SERVER AUDIT TSQLStackFrameAudit ADD (SELECT ON OBJECT::dbo.Orders BY public) WITH (STATE = ON);
You can then execute queries against the Order table, using code like the following, which accesses the table both through a stored procedure and a direct query:
EXEC dbo.[Sales by Year] '1998-01-01', '1999-12-31'; SELECT * FROM dbo.Orders;
To see the events and T-SQL stack frame when the Orders table is accessed through the stored procedure, you can view the audit log in the \Security\Audits node in SQL Server Management Studio (SSMS). Right-click the TSQLStackFrameAudit node and select View Audit Logs from the pop-up list. In the Additional Information column, you'll find the stack frame information, as shown in Figure 2. (Figure 2 displays only a small portion of the event information available.)
In this article, I mainly showed you how to write code to perform various actions. You can use SSMS to perform many of these actions as well. For example, Figure 3 shows how you can create the TSQLStackFrameSpec audit specification using the GUI.
Evolutionary, Not Revolutionary
As you've seen, there is little that's revolutionary or radically new for security in SQL Server 2012. But there are a lot of nice enhancements that continue to refine server and database security. Is security alone enough of a compelling reason for an upgrade? Maybe not, unless one of the new or enhanced features protects you against a specific threat your data faces. However, if your data is precious and you want every tool at your disposal for security, move to SQL Server 2012 as quick as you can.
Listing 1: Code to Create and Define a Server Audit
CREATE SERVER AUDIT SQLServerUserDefinedAudit TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE); GO CREATE SERVER AUDIT SPECIFICATION TestUserDefinedAuditSpec FOR SERVER AUDIT SQLServerUserDefinedAudit ADD (USER_DEFINED_AUDIT_GROUP) WITH (STATE = ON); GO ALTER SERVER AUDIT SQLServerUserDefinedAudit WITH (STATE = ON); GO
Listing 2: Code to Create a Server Audit with a Filter
CREATE SERVER AUDIT FilterAudit TO APPLICATION_LOG WITH (QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) WHERE server_principal_id <> 266; ALTER SERVER AUDIT FilterAudit WITH (STATE = ON);
