There have been many books written on security and encryption, and there is much talk about security these days. I will not bring anything new with this post to the general topic of security, but I would like to present some ideas in condensed format.
The main point I want to make is that we cannot discuss security without defining what are the valuables to protect, against what type of access do we want to protect them, and who are the attackers against whom we need to protect. Without knowing this information, no security measures can be meaningfully evaluated. It may not be possible to block all attack methods, but if some of them are much more expensive than what the attackers can afford (money, time, physical resources, knowledge), then this cost can be sometimes considered a good enough mitigation. Beware, however, of underestimating an attacker's capabilities; many security solutions have failed because they have underestimated the resourcefullness of the attackers. When thinking about software security, a very important point to keep in mind is that software is relatively cheap to produce when the technical knowledge is available; hence, once someone figures out a way to break software X, that attack can be mass-distributed virtually for free and people who would not have been able to break product X by themselves, will now be able to do it with minimal effort. The task of designing a secure system is very difficult because a single security hole is sufficient to compromise the system. To make things even harder, secure systems are rarely supposed to operate alone, they usually have to interact in complex ways with other systems that have their own security considerations, so security is as much a matter of engineering as it is a matter of administration and deployment.
Encryption plays an important role in many security schemes. But encryption by itself is just a security tool; it is not sufficient to provide security. Unfortunately, encryption is sometimes expected to resolve all security problems. Nothing is farther from reality. Encryption can provide solutions for a number of security problems but it cannot resolve all of them and will also introduce some problems of its own. One of the most obvious encryption issues is key management. Encryption will protect a secret such that it can only be recovered by providing another secret - the decryption key, but how do we protect this other secret? Using encryption again will leave us with the same problem for a different key.
So, to summarize the previous two paragraphs, security is a sum of several factors that cannot be ignored and encryption is just a tool, not a magical provider of security. Note that what I discussed so far are very general ideas, they are not related to the security of a particular product or to the details of a particular encryption scheme.
Now, I would like to more specifically discuss the security obtained through encryption in SQL Server 2005. The data encryption keys in SQL Server are protected in two ways: through permissions and by being encrypted using other keys. These two layers of protection provide defense in depth for the keys: the permissions should be enough, but if they can be bypassed, the encryption on the keys should provide a second barrier. The encryption of the keys can be made by other keys or by a password, forming an encryption chain. Ultimately, this chain must end either with the service master key encryption or with a password encryption. As I mentioned in previous posts (part 1, part 2), the service master key is protected using DPAPI. From the server point of view, the access to a symmetric key is limited depending on where the encryption chain ends (see, for example, my earlier comments on using symmetric keys). If the encryption chain is rooted at the service master key, decryption can always be performed by the server automatically, and access to the key is only restricted by permissions. If the chain is rooted at a password, direct access is restricted to those with knowledge of the password (I mentioned "direct access", because "indirect access" could be given through signed code, for example, by embedding an open key statement in a signed procedure and granting execute to others on the procedure). However, these access limitations should be interpreted carefully, they are not security guarantees. I'll try to clarify this point through the next paragraphs.
I have received several questions lately inquiring about ways to protect data in SQL Server against the machine administrator and whether the encryption features can help. We could encrypt the data and if nobody would access it, the machine administrator would not be able to decrypt it. But if clients will connect to the server to access the data and decrypt it on the server machine, then the machine administrator, having full control of the box, can patiently wait for such a moment to read the decrypted data (or the password that protects the encryption key) from the machine memory. So, while the machine admin does not have direct access to the encrypted data stored in the server, he could easily gain access by simply attaching a debugger to the server process. The root problem here is that if the data can be "seen" by the server on a machine, then the machine administrator can be assumed to be able to access it as well. Of course, it is possible to protect against the machine administrator by doing the encryption/decryption off-site and only storing the encrypted data within the server, but this is a solution that will not fit all scenarios.
What about the protection of data against a sysadmin, could that be achieved? A sysadmin would have a much harder time circumventing data encryption if he has restricted access to the operating system, that's for sure. But a sysadmin still has a lot of power because he can do anything that can be done inside the server that would just require a permission check. Even if sniffing a password from memory can be very hard to achieve by a sysadmin, there are various other ways through which he could attack a system from inside. For example, by profiling the server he could try to collect passwords that are unsafely processed by applications. Or he might examine the code of stored procedures that use hard-coded passwords to open symmetric keys. Or he might just execute malicious code to compromise the server integrity and try to disclose sensitive information. Encryption is the best protection that can be obtained for data against a sysadmin, but it should not be considered bulletproof.
Encryption provides strong security for protecting data at rest. But encryption does not protect and cannot protect data that is currently worked on that has to be in decrypted form. Such data has to be protected by securing the machine and operating system on which the database resides. It's not possible to have a secure database setup on a poorly administrated machine and it is very hard to protect against the machine or database administrators themselves, which are powerful system "insiders". Only by protecting all parts of a system, can a system be considered secured.