Actually this is just a demonstration of encryption, not necessarily key management.  Key management is a whole 'other topic.  Windows provides key management tools via the Crypto library; it would be possible to utilize the Crypto library to perform encryption and key management via XP's as well.  This functionality is integrated into .NET 1.1, and presumably will be integrated into the SQL CLR as well.

For SSN's, you might need to decrypt it, you might not.  If all you're using the system for is to match or compare an SSN against the data in your table you probably don't need to decrypt it.  In that case you should look into a one-way Hash function, such as MD5, etc.  However, if you need to generate reports based on SSN's, decryption will be a system requirement.

You are correct, if you decrypt on the server, you will send plaintext across the wire.  Keep in mind though, that encryption/decryption in one part of the system isn't going to keep the whole system secure.  To protect against the sending plaintext on the wire, you can use SSH or another secure transmission protocol to communicate with your SQL Server.

Basically what encrypting data on the server provides is a degree of protection if the hard drive is physically stolen/removed from the server, or if the system is hacked and the hacker gains access to the server tables directly.  As you say, there are several other areas that also need to be addressed in a 'total system' approach.

Thanks.