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?

Answer 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

Answer 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.

Answer 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.

Answer 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.

Answer 5

The best thing to do is limit what they can do If/When they break into the web server. That means granting only the SQL rights required for the application to function. It's much easier to give the application DBO rights, but it makes he DB much more vulnerable in the event of a successful attack on the webserver.

Answer 6

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.

Answer 7

The question is which is "better"? Which is hard to answer since it relies on the context, values and priorities of the questioner.

Personally, I like SQL auth.

• AD is another thing to run and support and manage service accounts.
• AD needs to be available in your hosting environment.
• AD would make it hard to move to the cloud or hybrid cloud.
• AD makes it easy to add service accounts into groups they should not be in by admins in some other part of your organisation.
• SSPI doesn't bypass the issue of encrypting your connection string since you should encrypt your SQL hostname in config.
• SQL auth is simple and just text configuration, easy to deploy.
• Setting App Pool identities is another thing to automate and then hide the username and password in those automation scripts for each environment.
• Using two connection strings, makes it easy to use rolling passwords, so you can update the password without downtime.

Last point: you code your connection manager class to try each connection string, that way you can change the password on the first in config, push the change out, and it'll failover to the second connection, then you update the password on MSQL and the first one will be used again. A final config change is needed to set the second password the same as the first, ready for next time.

Answer 8

If the users will not be manipulating the database directly (via other client tools such as SQL Server Management Studio) then I will typically just create a single SQL login for the application and grant it the access it needs. At that point the user is restricted in what they can do as allowed by the web app interface.