When to use encryption in SQL Server 2005?

Since it is possible to encrypt data before saving it, what is the benefit of using encryption in SQL Server 2005?

You can either use third party tools to encrypt your data and send encrypted data to the db or you can use db encryption provided by MS.

Some of the benefits of server-side encryption include:

• Often easier to set up than from the client
• Potentially better performance
• More encryption options available, such as certificate-based encryption and different encryption routines
• Encryption is centrally managed

Always remember that you pay a performance penalty (sometimes - severe perf penalty!) for the encription.
So encrypt only what you really need. Dont even think to encrypt everything 'just in case'

The encryption is going to encrypt sensitive data like SSN, credit card #, and other similar data.
Besides the performance penalty, what are other disadvantages using SQL Server encryption? My understanding is that symmetry key is stored with the database. What happen if the symmetry key is corrupted? Does encryption require using Windows Authentication?

Yes, but don't forget: if you encrypt SSN, then you can't use an index on SSN.
And I am almost sure that there are many WHERE SSN=@SSN in your application. All such conditions will require FULL TABLE SCAN with a decription of record by record.
Also, based on what I found, Symmetric keys are much much slower.
Regards

Thanks. There is no where clause using SSN but there is where clause using credit card #.

Encrypted Columns and SQL Server Performance
http://www.sqlsolutions.com/articles/articles/Encrypted_Columns_and_SQL_Server_Performance.htm

If you encrypt a column that is frequently used in WHERE clauses, try this trick. Add a second column that is a one-way hash of the encrypted column's original value. Now, you can do a WHERE hashed_col = hash(var). You can place an index on this column, and now you can use indexes on an encrypted column.

Sorry, that is not what I meant. I meant that you can place an index on the hashed column. That way the WHERE clause is sargable. So, you still encrypt the data in the encrypted column, but by adding a hashed column, you are able to perform searching on this encrypted column by using the hashed column using an index on the hashed column..

But if you store a hash, then you can decrypt SSN just because there are not so many combinations.
For other data, if you store HASH value, then you can not claim 'I am using AES-256 encryption' for example, because your encryption is much weaker,