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

Encryption enhancements in SQL Server 2005

SQL Server encryption used to be cumbersome and incomplete, as it failed to secure data at rest. Not any more, says CISSP Kevin Beaver. He explains SQL Server 2005 encryption enhancements and best practices.

Before the days of SQL Server 2005, you could encrypt passwords, data in transit and, well, just about everything except the one thing that's most important: data at rest. These older encryption features also tended to be cumbersome, which prohibited developers and DBAs from taking advantage of them.

They finally got security right!

Apparently, Microsoft finally seems to have gotten its security measures straight with its new DBMS, though time will tell how it stands up to new threats and security concerns. Combine one part increased security awareness, one part Microsoft's Trustworthy Computing and one part government control (in the form of California Senate Bill 1386, HIPAA, GLBA, etc.) and what do you get? What else but the solid and secure by default SQL Server 2005.

SQL Server 2005 now has a reduced "surface area," increased granularity in user permissions and many services disabled out of the box. There's even a built-in Surface Area Configuration Tool to configure SQL Server the way you need to -- not just how Microsoft thinks security should be configured. What more could we ask for?

How about encryption of actual data at rest? It's here!

Now developers and DBAs don't have to perform extensive application overhauls, utilize old or undocumented encryption functions, rely on Windows' Encrypting File System (EFS) for quasi-database protection or purchase expensive third-party encryption add-ons. Just let SQL Server 2005 do the work. It's got its own key management infrastructure to manage encryption keys for users and groups as well as digital certificates for code signing, SQL Service Broker and Web Services via SSL. It supports both symmetric and asymmetric encryption with widely used algorithms such as Triple DES, RC4 and AES.

It's one thing to have reduced attack surface, stronger user management and so on, but it's quite another to offer up encryption services for the actual data. When breaches occur (and odds are they will eventually by an external attacker or malicious insider), encryption can add a strong last layer of defense to protect sensitive information you can't afford to have compromised. Sort of like a bodyguard with a gun, it's that last line of defense that you hope is in place when you need it.

Another neat security benefit of SQL Server 2005 -- albeit less valuable than data at rest encryption -- is that SQL-based network communications are now encrypted by default. Better safe than sorry, especially when it comes to finding insecure wireless networks or tools like Cain & Abel in the hands of miscreants with physical access to your network.

I've been on my "focus on securing data at rest, not data in transit!" soapbox for years now. Encrypt the database -- that's what's getting attacked and that's where the money is. It's a very good security device for those who need it.

You've got to be serious

So, this leads me to a couple more points. Notice I said "for those who need it." Database encryption is not for everyone. I highly recommend against jumping on the SQL Server encryption bandwagon just because it seems like a good idea or because a best practices auditor with checklist in hand says you need it. You've got to go into this informed and prepared.

Your first step is to determine what's really at risk. Does your SQL Server system house sensitive information? Yes? That's fine and good, but what about the security context of SQL Server vulnerabilities you may have discovered? Not all security vulnerabilities are alike. You have to take into consideration the location of your database server(s) and related applications. Are they publicly accessible? Are they segmented onto a private network with no other internal or external access? Look at your users, too. Who has access to the database and applications? What about the data? Is all data in your database sensitive or is it just a column or two? Finally, do state, federal or international laws or regulations exist that affect your industry or business? There may not be.

Answering those questions may be enough to determine if anything is at risk. You may also want to formalize your analysis using risk-modeling tools, such as Amenaza Technologies Ltd.'s SecurITree, which can help provide contextual insight into what's at risk and what you can do about it. You may be surprised to find little or nothing to be concerned with. Remember that information security is about minimizing business risks. Part of a successful security program is to compare the costs of implementing security controls with the benefits they can provide.

If you do determine that information risks are present in your SQL Server environment, be sure to classify your data (i.e., public, private, confidential) and only encrypt the data that needs to be protected. I'm glossing over data classification concepts here, and it's not an IT-only issue so be sure to get others involved -- such as HR, legal counsel, a person or two in management and any compliance officer(s) you may have.

Also consider that encryption will not protect SQL Server from denial-of-service attacks, poorly written applications or authorized insiders with malicious intent. It's not the security solution -- it's just another layer of defense.

Finally, don't forget about the performance and administration required for database encryption. Neither is trivial. If you choose to move forward with SQL Server 2005 encryption, check out Microsoft's white paper on real-world scenarios and best practices for implementing and managing encryption with SQL Server 2005.

To encrypt SQL Server 2005 data or not is the question. I suggest you proceed with cautious optimism. Done the right way, it can be a good thing.

About the author: Kevin Beaver, CISSP, is an independent information security consultant, author and speaker with Atlanta-based Principle Logic LLC. He has more than 18 years of experience in IT and specializes in performing information security assessments. Beaver has written five books including Hacking For Dummies (Wiley), Hacking Wireless Networks For Dummies, (Wiley) and The Practical Guide to HIPAA Privacy and Security Compliance (Auerbach). He can be reached at

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.