SQL Connection String Security

Question

We have a internal asp.net website running on IIS with Windows Security. From what I understand (and observe) this means that each thread runs using the credentials of the user who connects to the website.

The website connects to a SQL Server 2014 database. We use a SQL Server username and password to make the connection. Having a look at MSDN I see that using SQL Server authentication is not recommended.

The password for the SQL Server account logging on. Not recommended. To maintain a high level of security, we strongly recommend that you use the Integrated Security or Trusted_Connection keyword instead. SqlCredential is a more secure way to specify credentials for a connection that uses SQL Server authentication.

Since removing Windows Security for the website is not an option, the only options I see to follow the MSDN advice is:

1. Ignore MS advice and keep using SQL Authentication as we are

2. Give every website user the same access to SQL Server as the current SQL Server credential has. This means if any user connects directly to the server they can run their own queries instead of being limited to what we have coded. Maybe some sort of firewall rule could fix this. The business is not a fan of this idea (I'm not either)

3. Give every user connect access (no other permissions) and use an application role to provide permissions when the user connects through the website.

4. Every time a connection is made to SQL Server, it should be done on a thread that impersonates a domain user who has access.

None of these options sounds any better than using a SQL Server username and password.

What is the recommended way to connect to the database?

Answer 1

We have a internal asp.net website running on IIS with Windows Security. From what I understand (and observe) this means that each thread runs using the credentials of the user who connects to the website.

Correct.

The website connects to a SQL Server 2014 database. We use a SQL Server username and password to make the connection.

OK, so during this process you loose the Windows/NTLM security tokens passed to the website.

[... options ...]

Application roles are the formal solution for this task. Option (4) will also work, but has overhead during authentication. Still, I'd go for for the "don't care" option and this is why:

I've implemented the TDS protocol some time ago. (Details of the protocol can be found on MSDN here: https://msdn.microsoft.com/en-us/library/dd357628.aspx ). Basically there are two ways that authentication takes place:

• Using a standard challenge-response protocol (e.g. username/password authentication);
• Using a integrated security, AD/kerberos authentication;
• In all cases you can opt for TLS/SSL encryption.

From a security perspective, it's secure enough. That is: I wouldn't expose my SQL Server directly to the internet, but that's mainly because that doesn't make sense to me...

I've also seen the comment from Microsoft before, which also puzzled me back then for a few different reasons:

• If you do a integrated security, it has to check the Active Directory for every connection. That might be fine for a few users, but for a lot of users, I wouldn't prefer that.
• SQL Azure doesn't support integrated security as long as they have this comment online. If it really was very insecure, that doesn't make any sense.
• The main advantage is that you don't have to put a username and password in your web.config . Then again, I wouldn't expose a SQL server to the internet directly anyways, and if a malicious person has access to your web server, he can do a lot of nasty things to your database anyways (either directly or indirectly).

There are cases where windows authentication makes more sense, specifically if you use role based security in your database to limit the access of individual roles to schema's, etc. In these cases, it makes little sense to use a single account.

To be perfectly honest, I can also explain comments like this from another perspective: the complete licensing model of Microsoft is based on CAL's, which is checked by the number of AD accounts. By pushing everyone towards using integrated security, it's much easier to enforce this licensing model.

So, to make a long story short, I wouldn't bother with it and simply stick to username/password authentication.