Skip navigation

SQL Server Denali’s Security Enhancements

SQL Server Denali's new security enhancements are enough justification to upgrade

Microsoft is hard at work on SQL Server Denali, the next major version of SQL Server. Earlier this summer, Microsoft released Denali CTP3, which is not complete but offers a lot of interesting new features. It won't be a massive, earth-shattering update like SQL Server 2005 and SQL Server 2000, but there will be a lot of new stuff to learn.

As a developer security MVP, I’ve been exploring what’s new for security in this release. You might remember that security was almost completely overhauled in SQL Server 2005, because it was the first version since Microsoft awakened to the need for security and began its Trustworthy Computing initiative. You don’t hear or see that name much anymore, but there is no doubt that Microsoft takes security very seriously. I certainly appreciate this when so much of my data and my clients’ data is stored within SQL Server. In fact, I often find that security is the most compelling reason to upgrade to the latest version of SQL Server. 

Denali will include security enhancements in four areas: security management, authentication, data protection, and auditing. I’m going to briefly explore what’s new in each of these areas. Most of these features can be administered or controlled by a DBA, but developers will have to understand the features to make applications work with SQL Server data.


Security Management

There are two new security management features in Denali. The first lets you set a default schema for groups, not just users. Schemas are a handy feature in SQL Server for partitioning database objects into namespaces and for assigning permissions across a schema. By extending support for default schemas to groups, it’s easier to use the appropriate schema in queries you’ve written. It also prevents the problem of creating implicit schemas when someone is sloppy with fully qualified names of objects. This was a large omission in SQL Server 2008, so it’s nice to see it filled at last.

The other major security management feature is user-defined server roles. At long last, you can create custom server roles and assign whatever permissions you want to them. This gives DBAs who manage the server instance and developers who need specific permissions on the server granular control over permissions. You can use a mix of GRANT and DENY permissions for a custom role to follow the principle of least privilege.



The big news with authentication is SQL Server’s upcoming support for contained databases. A contained database is a completely self-contained database that includes all the database settings and metadata required to define the database. When you define a contained database, it has no dependencies on the instance of SQL Server in which it resides, even to the extent that users can log into the database rather than the SQL Server instance, using either a SQL Server login or Windows authentication. The user doesn’t log in via the SQL Server instance at all, although there are some interactions if the user has a login at both the server and database level. Contained databases make it easy to move the database to another instance of SQL Server.

The way authentication works is to create a database user either with a username and password or associate it with a Windows login. The newly-created user is not associated with a server-level login as was required in the past, and SQL Server authenticates the user against the database. Once successfully authenticated, you get a token good for that database only, and the security boundary for that user is tightly scoped to the database. 

This contained database feature is going to open up a lot of nice scenarios for application development and database administration, so it will be interesting to see how Microsoft develops support for contained databases and how people will use them over time.


Data Protection

Encryption is one of SQL Server’s best features for providing in-depth data protection defense. Even if an attacker gets through the secure network, into the secure SQL Server instance, and gains access to your data, he or she still has to find a way to decrypt the encrypted data.

Nothing related to encryption has radically changed in Denali, but there are some nice improvements to cryptography coming . The HASHBYTES option will support the SHA2 algorithm for both 256 and 512 bits. The maximum certificate key length is growing from 3,456 in SQL Server 2008 to 4,096 in Denali. Denali will protect service and database master keys with Advanced Encryption Standard (AES) instead of Triple-DES (3DES), and it will use AES to protect key backups as well. There will be a new FROM BINARY option on CREATE CERTIFICATE that will let you use private key bits as a binary constant to create a certificate instance. (Note that some Denali documentation calls this FROM BYTES, so the name might change before release.) 

It is important to note that the RC4 algorithm is deprecated, mainly because Microsoft’s implementation wasn’t very strong. It’s still available for backward compatibility, but only when the compatibility level is set to either 90 or 100. A word to the wise: don’t use RC4 in SQL Server!



There are several new auditing features in Denali. For starters, all Denali editions will include support for server auditing, although for database auditing you’ll need the Enterprise or Datacenter editions. This means that you’ll no longer have to rely on SQLTrace as you did in previous versions.

Auditing will also be far more resilient to failure, letting Denali recover from failures to write to the log for most network or file errors. You will also be able to cap files without rollover. In previous versions of SQL Server, you could either have an indeterminate number of log files, or you could roll over after reaching a predetermined number of files. With Denali, you’ll be able to set a cap without rolling over so that you can control the amount of information without the risk of losing information. If the server reaches the cap, it blocks and rolls back operations until you clear out files. 

You’ll be able to store T-SQL stack frame information in the audit log so that you can determine whether a query was executed through a stored procedure or directly on a table. This is useful if you provide secure access to data only through stored procedures, letting you see who might be trying to access the data directly. You can also create user-defined audit events, letting you write whatever kind of information you want to the audit log. You can even filter audit events, giving you very fine control over what SQL Server writes to the log.

Whew! That’s a whirlwind tour of what is coming in Denali probably sometime next year. As you can see, the new security features continue with the work Microsoft started in SQL Server 2005, making it easier to keep your data secure.

I’ll be speaking at PASS Summit 2011 in Seattle this October, a user group–oriented conference for SQL Server professionals. One of my sessions is about Denali security. If you’ll be there, please stop by and say hello!

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.