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

Encrypting data in SQL Server: Dos and don'ts

This article discusses what steps you should and should not take when encrypting objects and data in SQL Server.

There have been all too many instances in the news lately where someone has gained outside access to a database...

-- usually for a turnkey commerce solution -- discovered that sensitive information in the database such as user names, passwords, credit card numbers or addresses were all stored as plaintext. Consequently, one of the first design decisions most people mull over when creating a database-driven solution is how to encrypt stored data to keep it safe from prying eyes.

What kind of support is available in SQL Server for encrypting objects and data? It might be wise to start by talking about what SQL Server does not have, or rather, what you should not do for the sake of encryption in SQL Server.

First, SQL Server has two built-in cryptography functions -- namely, pwdencrypt() and pwdcompare(). And, there are two undocumented functions that SQL Server uses to manage password hashing: pwdencrypt() hashes a password for storage; and pwdcompare() checks a provided string against the hashed string. Unfortunately, this hash function is not very secure and can be broken using a dictionary-attack scheme (available as a command-line application!).

The functions change from revision to revision in SQL Server, which is another reason not to use them. Passwords hashed with earlier versions of SQL Server will not be decryptable with later versions, so if you rely on them in one version and then upgrade, all of your encrypted data is useless unless you decrypt it first -- which rather defeats the purpose of encryption in the first place.

Second, you might be tempted to create a custom encryption solution for your database, but there are three good reasons not to do this:

  1. Unless you are an encryption expert, odds are any encryption system you create from scratch will not provide more than a trivial level of protection. Unsalted, one-way password hashes or "ROTx" forms of encryption are easily defeated with a little work.


  2. If your encryption breaks due to your own incompetence, then your data is ruined. You did keep unencrypted backups of everything, right? (And even if you did, isn't that a security hole right there?)


  3. It's scarcely worth your time when there are professional-level, industrial-strength encryption solutions available off the shelf. Devote your time to building a good, solid database, not reinventing the wheel.

What, then, would be good ways to encrypt data?

For starters, Microsoft has a native cryptography solution of its own, the CryptoAPI. For lightweight encryption where military-grade security isn't an issue, it has the advantage of being relatively easy to implement: The administrator can install an ActiveX control called CAPICOM that provides CryptoAPI functionality in a T-SQL stored procedure. CAPICOM supports a variety of two-way encryption and one-way hashing schemes, so the administrator can pick what's best suited to the application in question.

If you're not fond of using Microsoft's solutions, there is a slew of good third-party ones as well. A company called ActiveCrypt Software LLC makes XP_CRYPT, a SQL Server add-on that performs encryption in views, procedures and triggers via extended stored procedures and user-defined functions (in SQL Server 2000). You can download a free version of the application that supports unlimited MD5, DES and SHA1 hashing; other encryption models are fixed to certain bit depths. (The full version is unlimited.) XP_CRYPT is available as an ActiveX control as well (in a limited free version) for use in your own code. For ASP programmers, a component called AspEncrypt provides an easy way to integrate advanced encryption into your code.

What about encrypting the database files themselves or providing security on the transport level? For the former, you can always use the Encrypting File System in Windows Server. You will have to keep backups of the encryption keys, however, in the event of a problem or else the data will be lost. For the latter, there's IPSec and SQL Server's own SSL encryption, both native to SQL Server and Windows. Your main concern should be to avoid storing sensitive data in unencrypted plaintext, since pulling unencrypted data from the database is one of the easiest attacks around.

Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

Next Steps

Learn how to set up the SQL Server data encryption feature

This was last published in June 2005

Dig Deeper on SQL Server Database Modeling and Design

Join the conversation


Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

This is a good article. I found it informative, and taught me a few things I'd never thought in terms of SQL Server before.
Nice article. Covers some good info most users may never have thought about.