>Are you saying the app role is still used even if another client is given pooled connection?

Yes. If another connection request is made, it will be handled by a connection from the pool if one is available.

>Where is the connection pooler? If each client has a connection, how are they pooled?

ODBC, OLEDB, and the SQL Server Managed Provider all implement pooling just different enough to make things interesting.

Basically, pooling will be implemented at the source of the connection. Which makes it dependent on the architecture that you've adopted for your application. In a typical two-tier application (client interacts with database directly), the pooling will be implemented by the client. If that client is a rich client, the benefit of pooling is severly hampered since 40 client will each create their own pool.

If that client is a web server, there will only be one pool servicing any number of requests. A much better use of pooling. The same goes for the classic "n-tier" architecture when data access goes through a Data Access component.

-Mike