I was wondering about the performance impact of using Windows Authentication to connect to a SQL server in a Winforms application versus using SQL Authentication. In our current model, everyone uses the same username and password in the connection string stored in the application. Everyone using the application is always on the same domain, either in the physical office, or connected through VPN. At any one time, about fifty people could be using the application.
I was told there could be problems with connection pooling since connections are separated into pools by connection string and Windows identity.
Best Answer
Molotch is correct. This would typically be of concern with three-tier applications with huge numbers of connections, where an application server connects to the database on behalf of the clients. In that scenario, when using integrated authentication, there would need to be multiple pools, due to a pool cannot be shared across identities.
"...Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration. Connections are separated into pools by connection string, and by Windows identity when integrated security is used. Connections are also pooled based on whether they are enlisted in a transaction. "
In other words, leveraging the benefit of connection pooling is more obvious when using sql security, not integrated security.
SQL Server Connection Pooling
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx