Is using integrated security (SSPI) for accessing SQL Server better for web applications?

Solution 1:

I'd say there are only two valid reasons to use SQL auth:

1. You are connecting from outside the domain, so integrated auth.
2. You're running a TPC-C benchmark and every cycle counts. SQL auth is a tiny bit faster.

For the scenario you're proposing (the the web server host is completely compromised) nothing can protect you. The hacker can do on the DB server at a minimum everything the web server can do. And I'd say that defense in depth can teach you to minimize the loss in such case: reduce the DB rights of the account used by ur web server to the absolutely bare minimum required and nothing more. Second make sure if the web server host is compromised it cannot be used to elevate privileges higher than the web server account (ie. there is no other service on the WWW host that uses credentials with higher privileges on the DB than the WWW account). These are basic security principles and have nothing to do with the authentication scheme used.

While the sql auth vs. windows auth gives neither a clear advantage in your scenario, there are other issues to consider:

1. Centralized policies enforcement: you have one place to set up your password policies, including password lifetime and expiration, account termination etc.
2. Control over impersonation and delegation of trust. Once sql auth is used in a trust delegation chain, all bets are off as that is not real 'delegation' and thus is no longer under the restrictions your policies impose
3. Auditing: sql auth is not even seen by your LSA so your entire auditing infrastructure is simply bypassed. You need to explictly add the records SQL produce about sql auth events, but is mixing apples and oranges as those events have different source, provider and schema in the event log

One last note: the TDS protocol exposes the sql auth password in clear text over the traffic, but that is usually mitigated by requesting SSL encryption of the traffic.

So why do you see still sql auth WWW hosts that store password in clear in web.config? Those are the bad developers/admins, don't be one of them.

msdn.microsoft.com/en-us/library/aa378326(VS.85).aspx

technet.microsoft.com/en-us/library/ms189067.aspx

Solution 2:

If you don't use SSPI, you're hardcoding the username and password into the source files.

If you're hardcoding the username and password into the source files, all your employees have access to it.

This is relatively insecure. A disgruntled ex-employee could use the information maliciously. A visitor might see the code up on a screen somewhere. Or the source code might accidentally get out in the wild.

The advantage of SSPI is that the password is never stored anywhere in the clear.