Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server security: Enhancements in encryption, authentication and auditing

SQL Server's security capabilities have advanced considerably since SQL Server 2000, with significant changes made to encryption, authentication and auditing. Find out what the key changes are and what the current version can offer your customers.

Service provider takeaway: SQL Server's security capabilities have undergone big changes in recent years. Service providers can learn what's changed from version to version and why the security enhancements to SQL Server 2008 can help drive the upgrade cycle. 

SQL Server security has evolved appreciably over the past decade, driven by hacking, permissions requests from customers and auditing requirements. Service providers should become familiar with these SQL Server security features to educate their customers and to deploy the features at customer sites. This tip details the evolution in encryption, authentication and auditing capabilities, from before SQL Server 2000 to the current version, SQL Server 2008.


Ten years ago, the majority of hackers were in it for the fame; they wanted to create a name for themselves and impress peers by defacing websites or hacking into private networks. Nowadays, it's all about money. There's an underground market for "cards" (credit card numbers and related information) and "fulls" (a full set of information required to commit identity theft). Both sets of information have a short shelf life and live in databases. Theft of both cards and fulls is very difficult to track and is often noticed weeks or months after the occurrence. In response to this growing threat, businesses have taken on the balancing act of encrypting as much of their data as possible while not significantly degrading database performance.

In SQL Server 2005, Microsoft added encryption support through the use of the following functions:

  • EncryptByPassPhrase and DecryptByPassPhrase: encryption via passphrase.

  • EncryptByCert and DecryptByCert: encryption via certification.

  • EncryptByKey and DecryptByKey: encryption via symmetric key.

  • EncryptByAsmKey and DecryptByAsmKey: encrypting via asymmetric key.

More on SQL Server
The changing database consolidation landscape

SQL Server database security

One problem with this approach is that the stronger the encryption method, the slower the decryption process. EncryptByPassPhrase provides the weakest form of encryption and is the fastest, while EncryptByAsmKey provides the strongest encryption but is the most resource-intensive and consequently has the slowest decryption algorithm. Secondly, encryption based on symmetric key, asymmetric key and certificates are nondeterministic algorithms. In other words, they have a time component; each time you issue them, the encrypted value will be different. This means that you can't index encrypted columns, since, for instance, the word "aardvark" could be encrypted differently each time a function is called. (You can find more information about this topic on this MSDN blog.)

If your customers are using SQL Server 2005, you should educate them about which encryption algorithm -- or combination of algorithms -- will work best for them. For example, a passphrase that's encrypted with an asymmetric key algorithm will likely be the best combination for fast decryption and strong encryption. For very strong encryption requirements, you should suggest using an asymmetric key. You should also help clients determine what portions of their data should be encrypted. Many architects encrypt only a portion of their data -- for instance, last name or all but the last four digits of a Social Security Number or credit card number. This approach requires minimal decryption and protects against a "fulls" hack.

In SQL Server 2008, Microsoft added several features to strengthen encryption, including:

  • Transparent Data Encryption (TDE): This feature encrypts the entire database, tempdb, backups and the database transaction log with a key (called a Database Encryption Key, or DEK). As the data is read or written to or from the database, it is encrypted or decrypted. This allows the entire database to be strongly and securely encrypted and quickly decrypted with a minimal performance hit. In SQL Server 2005, you have to encrypt an entire column; if you need to find a particular value, you need to do a table scan to decrypt the entire column. With the entire database encrypted, the encryption functions described above aren't needed.
  • External Key Management: SQL Server 2008 also allows for external key management. There are a variety of software and hardware products that manage keys and certificates, but SQL Server 2005 didn't integrate well with those tools; it was a manual operation to export the keys and certificates. SQL Server 2008 integrates with these external key management products, enabling easier separation of keys from their data, which is required by some compliance mandates. External Key Management also means that users can not only manage keys and certificates, but also age, expire and regenerate them. While this can be done in SQL Server 2005, it is a manual process and quite cumbersome.

Many IT departments are undecided as to whether they should upgrade from SQL Server 2000 to SQL Server 2005 or go straight to SQL Server 2008. Some of SQL Server 2008's encryption features will make a move to that version compelling or even necessary for your customers.

Go to page: 1 - 2 - 3


Next Steps

Are you ready for the approaching SQL Server 2005 end of life?

Dig Deeper on Database software management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.