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

Question

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.

Answer 1

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.