This document reviews techniques and best practices in securing connections between a web application server and database system. This document focuses on .Net based web applications running on IIS and connecting to a MS SQL Server database. It assumes the web application is accessible to an intranet or internet.
This document references IIS7.5+, Windows Server 2008+, and SQL Server 2012+.
Securing Connection From Web Application to Database
Below are the most common connection types between a web application to database. Each type has different use cases with advantages and disadvantages.
| Connection Type | Pros | Cons |
| Windows Integrated Security (Windows Authentication) | Supports all IIS Authentication types Credentials are not stored Easier account maintenance |
Does not work (or causes complexities) when supporting multiple web sites or databases |
| SQL Server Accounts | Supports all IIS Authentication types Supports multiple databases Supports multiple web sites |
Credentials need to be stored in a configuration file Difficult account maintenance (updating or resetting credentials must be done on database and app server) |
| Impersonation | Only available option if user credentials are needed in connecting with database Credentials are not stored |
Some additional notes:
There are three main security modes for SQL Server user login. Each of these reflect a connection type above.
- Standard Security
- Used for SQL Server Account connection types
- Windows NT Integrated Security
- Used for Windows Integrated Security connection types
- Mixed Security
- Supports both Standard and Windows NT Integrated Security
More information about SQL Server Security can be found here:
https://msdn.microsoft.com/en-us/library/aa266913(v=vs.60).aspx
Windows Integrated Security
Windows Integrated Security is recommended for most cases where a single web site is connecting to a single database. However, because the web site must run with the Windows NT service account (application pool), this presents two drawbacks:
- Cannot connect to multiple databases (unless each database uses the same Windows NT account)
- Cannot support multiple web sites using IIS Windows Authentication
- This is due to the HTTP SPN (SPN are mapped to a single web site)
Note – a possible work around for the HTTP SPN issue above is if each web site can run in the same sub domain, but have a different suffix at the end of the address. For example:
- mydomain.com à common.mydomain.com/myapp1
- mydomain.com à common.mydomain.com/myapp2
Also, not that when using Windows Integrated Security, the account should not be the same as the service account in which the SQL Server is running (if configured with a domain account).
SQL Server Account
SQL Server Account credentials must be stored in a configuration file. These are usually done in the web.config (refer to section below on encrypting these files). They can also be stored in the machine.config. This would allow all apps running under IIS to have access to those credentials. These configuration files should be encrypted.
More information about securing database connections can be found here:
https://msdn.microsoft.com/en-us/library/ms178375.aspx
More information about the IIS Authentication types can be found here:
https://msdn.microsoft.com/en-us/library/aa292114(v=vs.71).aspx
Web Application and Database on Same Server
It is considered bad practice to keep the database system and web application server on the same host/server. Some of the reasons why are:
- A compromise to one system can affect the other. If an attacker gains access to the web server, they can have access to the database system as well. (Though the attacker may not have credentials to login to the SQL Server, they will have access to the file system in which the database is writing to. The attacker would could have access to the data files).
- An increase in attack surface area. The web server resides on the DMZ, allowing external access. Having the database system on the DMZ creates an additional attack surface making the overall system more vulnerable to external attacks.
- Some other non-security based concerns with having both web application and database system on the same host are maintenance, disaster recovery, scalability and performance. Having both systems on the same host keeps them tightly coupled.
The general rule of thumb is to only expose systems or parts of systems that need to be exposed in the DMZ. All other systems should reside physically (or virtually) behind that layer in trusted zones.
Principle of Least Privileges
Use the principle of Least Privileges = every account should have the minimum privileges needed. Accounts should have access to only those resources necessary with only those rights necessary.
SQL Server Security
The following document discusses some common procedures in setting up the SQL Server’s service accounts. The document states that the use of domain accounts for SQL services may introduce more complexities and maintenance on behalf of the DBAs and therefore should be avoided.
https://msdn.microsoft.com/en-us/library/ms143504.aspx
Also, though not noted in the document, it is best practice to avoid use of the default sa (root) account on the SQL server – furthermore if the sa account is used, be sure it has a proper password set (and not blank, which some DBAs may do during initial setup).
Physical and Network layer security
Other best practices rely on things like physical security, where the physical servers are locked in secured rooms and access controlled. Access to servers are limited to only administrators. Administrator’s access are controlled using secure authentication. If using RDP (Remote Desktop Protocol), changing the default port (TCP 3389) or using an RDP gateway can further increase security.
More information about network layer security including the use of explicit ports and addresses through firewall configuration can be found here:
Encrypting configuration files
Configuration files should be secured if it contains sensitive information, such as connection strings. The most common way to secure a configuration file is to encrypt it. There are various encryption techniques available but IIS provides one directly through the IIS web server. More information can be found here:
https://msdn.microsoft.com/en-us/library/dx0f3cf2(v=vs.80).aspx
https://msdn.microsoft.com/en-us/library/ms178375.aspx
https://msdn.microsoft.com/en-us/library/89211k9b(v=vs.110).aspx
Configuration files should not be source controlled (removes sensitive data such as connection strings from the source repository). This is especially important if the source repository is public or being shared. Sometimes the configuration file may contain other non-sensitive data that is required by the application. In this scenario, the best practice is to remove the sensitive data into separate files and reference that file from the configuration file. The separate files containing the sensitive data would not be added to source control. More information can be found here:
Leveraging cloud for connection strings and other sensitive key data
Applications being deployed to Azure have the option of using Azure’s key storage, which is an in-memory storage outside of the configuration files but can be referenced by the application. More information here:
Encrypting the database
SQL Server stores its data to the file system. To further enhance security, the data storage should be encrypted. There are many different techniques to this, such as encrypting at the cell level or whole database. Each technique has different use cases and can also affect performance. The document below gives an overview of SQL Server encryption.
https://msdn.microsoft.com/en-us/library/bb510663.aspx
Securing the application
Connections from the app to the database should be done at lower data access layer and not directly exposed to the user. Connections to the database should be limited to only when it is required. The number of connections to database should be reduced and utilize connection pools when possible. More information about application side best practices in connecting with a database can be found below.
http://www.agiledata.org/essays/implementationStrategies.html