Wednesday, September 26, 2007

ASP.NET Impersonate Application Pool Account To Access Database

In an ASP.NET web application, we use a fixed account to connect to SQL server. Although we could encrypt the user name and password in the connection string, admin still suggested to use Windows Authentication so that there's no any user credential information stored in the file system.

One simple way to achieve that is create an application pool for the web app with a service account, and grant the permission for that service account inside the SQL server, then wrap all ADO.NET code inside a System.Web.Hosting.HostingEnvironment.Impersonate() block:
    using (System.Web.Hosting.HostingEnvironment.Impersonate())
{
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(spStoredProcedureName, connection);
//... other ADO.NET code
}
}