I often show the Transparent Data Encryption (TDE) feature in SQL Server 2008, and then I get asked “Is that in Standard edition?” and it’s only in Enterprise. Also TDE is very prescriptive; if you apply it to your database, it automatically applies to your backups, which then can’t be compressed (another Enterprise Edition only feature), Also TDE doesn’t work on filestream filegroups. So where does that leave you if you can’t justify Enterprise edition in your business or you can’t use it for other reasons?
You have 2 choices:
Finally if you only want to protect part of your database you can encrypt individual columns in SQL Server 2005. This would require you to re-write the access mechanisms to this data, where the other options above are transparent, provided the data is where it is supposed to be. The details on how to do apply encryption to individual columns are here. I would add that column encryption might be needed in addition to other forms of encryption described above to protect such things as credit card details , NI numbers and personal address information, from unauthorised access by parts of the business who aren’t allowed access.
So lot’s of choice, as ever with Microsoft, but also lots of advice on the sites above. There is also our very own evangelist Steve Lamb on hand for advice as well.