Windows authentication to SQL with AD username and password in connection string

Hi I'm writing an ASP.Net MVC intranet application which is accessed via handhelds and a range of browsers. My users all have AD accounts and have different permission to our SQL database views/procs depending on who they area. The database also uses CURRENT_USER for horizontal partitioning.

Because of the range of devices that need to access the application I can't rely on impersonation/delegation or the trusted subsystem. I want the user to simply input username and password (same as AD) when the above mechanisms fail.

Does anyone know how I can use AD username and password in a connection string开发者_运维百科 to access the database without creating a set of SQL mirror accounts? I can't login with network service or a sql proxy account because the database needs to be user aware.

You can't do it in the connection string.

Your best bet is to make your ASP.NET code impersonate the user you want to connect as. This can be a little tricky with ASP.NET as there is already an impersonation layer going on, but it is doable.

See the section of this MSKB article called "Impersonate a Specific User in Code" to get you started down the right path.