Some DB Stuff

This made us lose some hours at work recently, so I'll write it here in case it helps others. When connecting to Sql Server using a Windows Account (Windows Authentication) the authentication will always be done for the Windows User that is running the Thread that tries to open the connection. So don't try to put a different Windows User/Password in the connection string thinking that it will try to authenticate with them, cause it won't work (you can find samples of connection strings that mix both things, like this:
Integrated Security=SSPI;uid=user;pwd=password

You better forget it, as I say it won't try to use that user and password as Windows User Accounts, they are going to be ignored and it's going to try to connect via Windows Authentication with your current user. What I'm not sure is whether that attempt fails it'll try to connect with Sql Authentication using that user/password, I haven't tested it. You can verify it here.

I find this limitation quite annoying, though I sort of understand why Microsoft imposes it, it's just the same as with the runas command and not being a to give the password as argument and being forced to type it. Microsoft wants to avoid .bat files and configuration files with passwords in them. For runas all what it manages is forcing you to use another tool like psexec (that well, now is owned by Microsoft). For connecting to SqlServer it will force you to impersonate a thread under that account or go one step further and launch a second process under that account. So in the end you will end up with a password in your config file. Obviously passwords in configuration files should always be encrypted. But if you put them in clear text, you end up with just the same problem that MS is trying to avoid. Eventually this Microsoft's policy of forcing you to use impersonation is useless, it adds no real security and can give you some headaches depending on your control (or lack of control with some versions of Entity Framework) on how/when the connection is opened/closed.