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

Question

When deploying web applications (.net) to a production environment, is it better to use integrated security or does it even matter?

It seems to me that if a hacker breaks the web server, it won't really matter as they can easily impersonate the machine.

Thoughts?

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.

Solution 3

The other answers so far have been good, but I'll throw in another one: management.

Sooner or later, you're probably going to end up with multiple SQL Servers. Managing the SQL authentication between your app and multiple SQL Servers gets to be a little painful, especially when you run into security problems. If you change a Windows authentication password once, it changes right away across all your servers. If you need to rotate your SQL authentication passwords, it's more painful - to the point where you probably won't do it at all. That's a security risk.

Solution 4

I'm not 100% sure here, but I think the main point is that SQL auth is insecure, so it's better to use Windows auth. Depending on how your app is setup, you can also store the proper credentials in an encrypted form on the machine using Windows auth. I don't think that's really possible with SQL auth. You can obfuscate it, but ultimately it must be in the clear.

Also, just because a hacker can get into a server doesn't mean it's game over. A hacker might gain control of an unprivileged process but not do anything else on the server. That's why it's important not to run everything as administrator or system, but instead to use minimum privilege service accounts.

Solution 5

I'm going to preface all this by saying I'm making the assumption that you're talking about an internal web server on the internal private network.

Let's start with the impersonating the machine. If the application pool identity is Network Service and there is no impersonation in the .NET application, then yes, the web application will connect to the back-end SQL Server using the machine's computer account. And that would mean you've granted access to said machine account. Microsoft's CRM works this way.

However, if you've specified an identity, that user account will need access to the SQL Server. While you're right that if an attacker compromised the web server they effectively have the same access as the identity account, the truth of the matter is that using a SQL Server logon doesn't change anything here. Once I have access, I can modify the web application to do what I want and it will, to the maximum your security permits on the back-end SQL Server.

Now as to why to use SSPI. First and foremost, you aren't using a SQL Server based login. That means Active Directory is the sole source for security. That means you have the normal audit means to determine invalid access. Second, it means that unless there are other apps that require it, you can leave your SQL Server in Windows authentication only mode. That means no SQL Server logins are permitted. That means any attacks against sa are stopped before they even start. And finally, it makes recovery easier. If you use a SQL Server based login you'll need to extract the login with SID and encrypted password. If you're using a Windows based user account as a "service account," when you go to a new SQL Server, by creating the login, everything will be reconnected once you restore the database because the SIDs will match between login and database user.

View more solutions

Comments

[user355002]

There are so many good answers here; it was hard to pick a winner. Thank you everyone.

[user355002]

Although true, a disgruntled employee could also simply install a web page that utilizes the SSPI connection. Which is as bad as having access to the password itself...

[K. Brian Kelley]

Sure there is. A publicly exposed server, in general, shouldn't be on the domain. That means you can't use a trusted connection. SSPI is not an option.

[Joel Spolsky]

a disgruntled EX employee would no longer have access, since his/her password would have been disabled

[Luke Puplett]

Surely you need to alter the password on each web server for the worker process identity? That sounds harder to automate than a config file change. These days, all my choices are based on ease of automation.

[jjxtra]

Not necessarily, you could be using environment variables, vault or other mechanisms to inject it into a server.