Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
    
There is no application server in my scenario. Clients are connecting directly to the SQL server. Are you saying that there would be a noticeable drop in client-side performance by changing to Integrated Security? –  Cuthbert Jan 8 '13 at 21:14
    
No. In addition to agreeing with Molotch, that it is essentially irrelevant in your scenario, I was providing information about scenarios when connection pooling is relevant. –  Greg Askew Jan 8 '13 at 21:41
    
Got it. Thank you for clarifying. –  Cuthbert Jan 8 '13 at 22:05
add comment

Connection pooling is done on the client side, not server side, so no problem there. Each client will open and maintain a certain amount of connections to reuse through the applications life time depending on provider (ADO.NET, OLE DB, ODBC, native client) and settings.

On the server side the sql instance is limited by the @@MAX_CONNECTIONS setting which is independent of number of logins or authentication method used.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.