Authentication Modes

[irasmith]

I have noticed that when dealing with ASP.NET applications that need a database back end, you have the options of using either Windows integrated security or using SQL Server authentication.

 

I have done some searching on my own and I have not been able to find resources which discuss the pros and cons of each approach, which one is the suggested approach to use as best overall, and the like.

 

Can someone either point me to resources that disucss these matters or perhaps if no resources exist then drop a reply back with some of their own personal thoughts and experiences on this matter?

[PlausiblyDamp]

The main advantage of windows integrated is that if you are in a domain environment anyway then all the user / group management can be done through the existing infrastructure without the need to create and manage users and roles in both the database and the domain.

 

SQL authentication removes the need for windows integration and allows SQL to maintain it's own list of users, passwords and roles and these can be used quite easily with a connection string.

 

Be aware that the current imlpementation of SQL authentication uses a weak encryption mechanism when transmitting the password from the client to the server and a network sniffer could easily intercept this (or encrypt the SQL connection), whereas windows authentication doesn't rely on a password being sent from the client to SQL at all.

[irasmith]

Interesting to note some of the differences between the two methods. For those reasons mentioned, I can see why in an Intranet environment using Windows Authentication would be the better choice to keep the maintenance overhead low and the like.

 

In an Internet setting where you have the web application on a web server in a DMZ zone and a SQL server behind a firewall, you really have to use SQL authentication in order to make the connection to the SQL box from the web application, or at least in my mind that is how I would see it needing to be set up. You wouldn't be in a domain environment under those conditions for Windows authentiation to work or at least I don't see how you could be.

[PlausiblyDamp]

It also depends on how your web application is managing authentication - if it is simply taking a user name / password and creating the ConnectionString from this then SQL authentication seems easier; however it will result in separate connections for each user due to the differing user accounts.

If the Web application provides it's own authentication / authorisation mechanism then it could simply access the DB as a single user and take advantage of things like connection pooling to increase performance and will not lock your front end into an overly simplistic model defined by the DB schema (although this could also be acheived by having the app always login as the same SQL user regardless).

The web server wouldn't need to be part of the domain but via impersonation could logon against the domain as long as the required ports have been opened going from the DMZ to the corporate network.

[irasmith]

Would anyone happen to know at this point if the encryption is stronger within SQL 2005 when using SQL Authentication mode in a .NET based web application?