Typical SQL Server authentication approach for Internet-facing app server

Question

I work on an Internet-facing, ASP.NET-based product that uses SQL Server 2005. Most customers deploy our software using the traditional approach with the app server (IIS) located in a DMZ, and SQL Server behind a secondary firewall.

We would like to pick one type of SQL Server authentication. From a security perspective and/or a customer perspective, what is Integrated or SQL Server authentication preferred?

I've read the related posts, and if it matters, our app uses SQL directly, as well as stored procedures. Our application implements a security model, so we don't need to be able to distinguish between users within SQL Server.

Thanks, Scott

Answer 1

Either will do. I've been told Integrated authentication is a little more secure, as it doesn't pass sql login information across the network. The type of authentication isn't as important as what permissions the account accessing the database is given.

If you are going to give this to clients to install, I would probably go with sql authentication as that won't require another system to authenticate the user (like active directory).

Mithcel brings up a good point about letting clients configure it. I have seen places where SQL authentication is expressly forbidden, and others (like where I work now) where using Active Directory accounts isn't possible.

Answer 2

Working in an environment where you provide a product to users, I would leave it up to them with regards to how they configure the connection. Integrated Security and granting the ASP.NET worker process is typically known as the more secure route, however, some environments have restrictions on delegation that don't allow it.

Typically I see people using SQL Server accounts with strong passwords.

Answer 3

I mostly use non-integrated SQL server accounts with hard to guess account names, and very strong passwords - and then only grant the bare minimum of rights to that account.

Answer 4

Windows Auth is more secure than SQL Auth, however if the Web server is in the DMZ, then it probably isn't part of the Windows domain, and SQL Auth will be required.

As someone who has installed vendor packages before I always recommend letting the customer decide how the authentication should be done. This should include setting the username and password to something that they want so that if there are standards which need to be kept to it isn't an issue.

Answer 5

You probably would want to create a user/login in the sql server backend that has a set of limited right, say "WebUser", for you connectionstring in the web config. Make sure you do not grant DDL right to this user (unless required by your app).

But if you are 100% sure that all the users will be Windows users (part of Windows Domain), setting your SQL Server Authentication to "Windows Authentication" is the safest way to do it since that mode of authentication will inherit the policies of the domain and its users (priveledges, roles, etc).