Dec 10, 2012
Thanks for your opinion.
You are right for the web.config. I was thinking the same but I was just too lazy to explain it.
If I use a session table to save the current users connstings then I have to create another DB ('central' DB) with a session table.
That means before each call to the client DB I have to call the 'central' DB and retrieve the connstring.
Every request - at least two calls to a DB server.
What about getting the full connstring value on the login and encrypt/decrypt it.
Carry it through the layers as an encrypted string, and before the DB call (in the DAL layer) decrypted it.
Still not very elegant and efficient.
Dec 10, 2012
That is definitely an option. However, security would be my primary concern, and although your site (theoretically) will have the only keys, there is still added risk. Having your connection string fly back and forth, even encrypted, is a vulnerability. Disgruntled employees could easily take a copy of the keys in retaliation for being terminated.
Weigh the cost of that initial connection string fetch against the decryption and I don't think you'll find that extra hit that substantial. Either way, your going to have that added overhead of coming up with a usable connection string. When weighing performance versus security, I would always choose security. Clients would prefer to hear that you have to upgrade your servers to improve performance, than an apology that you system allowed someone to get their data.