Encrypting data in SQL Server: Dos and don'ts

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 ...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Management and Administration Password cracking tools for SQL Server Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V SQL Server Database Modeling and Design Optimizing SQL Server indexes –- even when they're not your indexes Top tips and tricks for SQL Server database development Managing the development lifecycle with Visual Studio Team System 2008 A first look at Visual Studio Team System 2008 Database Edition Testing transaction log autogrowth behavior in SQL Server Top 10 SQL Server Tips of 2008 Tutorial: SQL Server indexing tips to improve performance Tutorial: Learn SQL Server basics from A-Z SQL Server database design disasters: How it all starts Can you shrink your SQL Server database to death? SQL Server Security Password cracking tools for SQL Server Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server SQL Server security made simple and sensible Blog: Protect your databases from the internal threat Setting up SQL Server Service Broker for secure communication The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 The fine line between not encrypting your databases and breach notification

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary binary tree  (SearchSQLServer.com) block  (SearchSQLServer.com) data structure  (SearchSQLServer.com) DDBMS  (SearchSQLServer.com) entity-relationship model  (SearchSQLServer.com) initial extent  (SearchSQLServer.com) primary key  (SearchSQLServer.com) segment  (SearchSQLServer.com) tablespace  (SearchSQLServer.com) view  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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!

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.