SQL Bad Practices

Some days ago, I was asked to do a quick security check on a web application. After reviewing authentification mechanisms best practices, we quickly looked at how the application was connecting to its databases. As with a lot of web app, the application was connecting to the database through a unique login.

Positive points first:

Windows Athentication mode was used (√)
 The application is using the Windows Athentication mode to connect to its databases. This is great since you don’t have to include usernames or password within your code. Moreover, Windows has all sort of policies that you an enable to make your application more secure, such as Password Complexity policy or Password expiration policy. Just make sure SQL Server enforces it (CHECK_POLICY=ON at the login level – see ALTER LOGIN).

Another benefit is that no clear text password will be transferred through the communication protocol, (as it is when SQL Server password is used), preventing attackers from sniffing passwords. More details here.

Allow remote connections to this server was unchecked (√)
Since the web application is running on the same server as SQL Server, there is no need to accept remote connections, because the connections will always be handled by the web application. This prevents all intrusions from external applications or individuals.

SQL Server port was blocked by the Firewall (√)
By default, SQL Server listens to TCP port 1433 for remote connections. Blocking this port on the Firewall level adds a layer of protection.

HOWEVER, there was two major security issues:

To much permission was given to the login (x)
An important principle in computer security is the principle of least privilege. In this case, this principle means that the login should have had the minimal permissions needed by the application. However, the login was given administrative privileges (sysadmin server role) when he should have had only read/write permissions to some tables and execute permissions to some stored procedures.

The login used to connect to the database was the IIS anonymous built-in account (x)
Like a lot of web application, IIS anonymous authentication was used and the application was connecting through a single login. By default when IIS anonymous authentication mode is used, the ISS web application is running with the IUSR_YourComputerName windows user (IIS anonymous built-in account). So database permissions were given to that login.

This is a bad practice. If you give permissions to SQL Server to this login, you end up with a big security flaw: everyone who is able to run an IIS application will have access to your database (or worse, they could administer your whole SQL instance as in this specific case). Other web applications won’t even need a password because IIS automatically impersonate  this account!

The same is true if you have an application running as a Windows service and you decide that you want to connect to SQL Server using Windows authentication while your service runs under the Local System, Network Service or Local Service accounts. If you give these special accounts access to your database, any service (current or new) will be able to access your data without even providing a password…

So what should be done ?

1- Create a local account:

2- Give this user minimal permissions to your database:

3- In IIS, modify the Anonymous Authentication credentials from IUSR to Application pool identity. More details here.

4- Create an IIS Application Pool for your web application and set its Identity to the local user you just created:

Your web application will now be running under your local account and be able to connect to its database using Windows Authentication mode. You can do the same if your application is a Windows service: depending on your needs, you can modify the account identity under the Log On tab or impersonate your local account when you wish to interact to your database.