Understanding SQL Server encryption (by password)?
There are really only two options:


Automatic key decryption Ie. the key hierarchy includes an encryption by the service master key (usually through the database master key) and the engine is able to decrypt and or encrypt the data when needed. This protects against accidental loss of media, but anyone with access to the running server has access to the encrypted data (subject to access permissions, not subject to cryptographic protection). Transparent Data Encryption TDE is the high-end version of this type of access.
Explicit session provided decryption key The application must ask the user for the key decryption password and open the keys explicitly in each session it uses. The engine itself cannot decrypt the data. This protects against any unauthorized access to the data by cryptographic means (even if one can access the engine and has access rights to read the data, he cannot use the data w/o actually knowing the password). Needless to say having the application prompt the user for password is annoying at best on desktop apps, and down right impossible in web apps (the web apps needs to store the password to use it between requests, and that's a huge problem).


These are the only alternatives. Never ever is there any need to have the password stored in any configuration file or stored procedure. Any scheme that requires to store the access password somewhere is doomed from the get go and you need to rollback to page one of your design and start from scratch.