An ASP.NET 3.5 app with SQL Server 2005 must provide single sign on on intranet environment. This is done by using the Windows Authentication mode in the web.config.
I want to use connection pooling as much as possible. I also want to use SSPI as database access in the connection string and not using impersonation (in the web.config)
Thus: is there a way of using connection pooling and single sign on mechanism for ASP.NET apps ?
You can use single sign on for your application, then have the application impersonate a single user for every database connection, ideally a service account with no log on rights.
Using Windows Authentication doesn't necessarily imply that a thread handling a request runs under that users identity. By default ASP.NET uses the worker process identity in its threads so unless you have a specific reason to enable impersonation because your security model requires it (for example you've added user groups to SQL as SQL Users and control access to SPs/Views via these users) then there is no problem here.
Assuming you are impersonating the windows user in ASP.NET processing:-
You are in an intranet environment, how many users do you have?
Connection pooling has been around for a long time to help with the scalability of an application, however although machines have improved considerably in the last decade the number users with in an organisation hasn't increased at the same rate.
IOW, are you sure that having one or two connections open per user is actually a problem?
Connection pooling is still in effect, each connection spec will have its own pool leading to lots of pools of one or two connections. Hence connection setup times will not be a problem. A 500 user system may result in 1000 connections being live but by modern standards that isn't too much of a burden like it would have been when pooling was first conceived.