First, encrypting the entire table means that access is going to be slow since you have to decrypt. Indexing doesn't help, and that can be an issue. If you always perform access for just a row or two and the table isn't large, then that's fine. However, I'm not sure why you'd encrypt key columns. Usually you want to encrypt the data only that's sensitive.

The password can't ever be stored anywhere in the database or it can be compromised by the sysadmin. That's why I say this has to be controlled from the front end. You'd set this up, have the user enter a password, encrypt the data and then send it on the wire.

You could use temp keys in SQL Server as well. This isn't simple, but it's not too hard either.