Key Ways to Secure ASP.NET Applications with a SQL Server Back End

Securing an ASP.NET application with a SQL Server back end is an enormously complex task. People have written books about building secure ASP.NET applications and about securing SQL Server systems. However, relatively little has been written about protecting data by stepping back and looking at the entire application ecosystem. Thus, I'll discuss how to secure that ecosystem, including the communication channels between the ASP.NET application and SQL Server, with the aim of protecting data both in motion and at rest in the database. This is still a large topic, so I'll concentrate on what I think is most important, and what developers and DBAs often overlook.

A Secure Starting Point

SQL Server databases and servers are remarkably secure when you first install them. Unfortunately, most DBAs insist on actually doing something useful with SQL Server, which involves punching all kinds of holes in the security of a server or instance. They add logins, add databases with code in them, grant access to users and applications, and do other things that make SQL Server useful but far less secure than it started out in its pristine, newly installed state.

Because ASP.NET provides a blank slate for an application, it doesn't start out secure. However, it provides plenty of features that let security-aware developers create secure web applications. Security starts with application conception and continues through the entire span of its life.

I'm going to assume that you've already hardened your ASP.NET application, your SQL Server installation, and any intermediate tiers, including the servers on which they run. In other words, I'm assuming that you're protecting servers to a degree appropriate for the sensitivity and value of the data in the system and you have policies and procedures in place to constantly review and evaluate your security in the face of changing environments and emerging threats. Microsoft has made an enormous amount of free information available on how to do these things on MSDN and TechNet, and there is even more on third-party websites. If you haven't done this step yet, go do that right now because your data and servers are probably already under attack. Here are a few good places to start:

Secure Communications and Connections

There are plenty of ways to secure communications and connections from an ASP.NET application to SQL Server to make the database server both harder to find and harder to connect to. One way is to use a port number other than default port 1433 or a port determined at system startup for named instances. You can use SQL Server Configuration Manager to set the port for all IP addresses listed in the TCP/IP Properties dialog box, as shown in Figure 1. Be sure to delete any value for the TCP Dynamic Ports property for each IP address. You might want to disable the SQL Server Browser service as well or at least hide the SQL Server instance so that the Browser service doesn't reveal it to any applications that inquire which ports the server is listening to. (One reason to not disable it would be if you have multiple instances of SQL Server on the host because it "maps" connections to instances.) You can hide an instance in the properties page for the instance's protocols, as shown in Figure 2, although this just means that SQL Server won't respond when queried by client applications looking for a list of SQL Server machines. Making these kinds of changes is security by obscurity, which is arguably not very secure and shouldn't be your only security measure. But they do place speed bumps in the path of attackers trying to find an instance of SQL Server to attack.

Figure 1: Configuring the TCP/IP port on which SQL Server listens for connections

Figure 2: Hiding an instance of SQL Server from the SQL Server Browser service

An even better option is to create an application-specific endpoint that listens on a nondefault port, with the connection permissions set to the Windows login under which the application is running. This way, a specific application connects to SQL Server through a dedicated port, which implements a kind of application-specific firewall around the SQL Server instance.

Listing 1 shows sample T-SQL code that creates an application-specific endpoint and assigns connection permissions. You can download this code by clicking the Download button at the top of page. Before you run it, you need to configure a SQL Server instance to listen to port 9450, change Puppy to your machine's name, and add the User1 and User2 Windows logins. Then, run the code in SQL Server Management Studio (SSMS) as User1 and User2 to explore how each can connect to the instance. User1 will have to connect as Puppy,9450 to use that port and won't be able to connect directly to Puppy (where Puppy is the name of your machine or instance). Conversely, User2 can only connect to Puppy through the Public role and can't use the port number. As this example demonstrates, by using an endpoint, you can use multiple ports and limit who can connect through specific ports.

Using IPsec or SSL when using HTTP protocol

When data is in motion between the application and SQL Server, you can protect it using IPsec or SSL when using the HTTP protocol. IPsec is best used with internal network communications, but that works for ASP.NET applications because it's likely that your web and database servers are local to each other. If you have to communicate data externally over the Internet, you should configure your network to use SSL. Either way, your data will be encrypted as it moves across the network. You can generate a certificate or key in SQL Server or use an externally generated certificate or key, such as one from a trusted Certificate Authority (CA).

With SSL, the data is encrypted within the protocol layer and is available to most clients, except for clients using DB-Library or Microsoft Data Access Components (MDAC) 2.53. You can also use SSL for server validation when a client requests encryption. To do so, you need to set up the web server to trust the root authority of the certificate used by the server with the SQL Server instance. After you've installed the certificate on the server, configure the SQL Server instance to use it in the properties page for the instance's protocols, as shown in Figure 3. Afterward, you can set the Force Encryption property on the Flags tab to force encryption for all clients (a setting of Yes) or make it optional (No). Be sure to check SQL Server Books Online (BOL) for the requirements of the certificate you want to use for SSL to make sure it complies.

Figure 3: Configuring SSL for a SQL Server instance

IPsec is another option for encrypting data in motion. IPsec is a set of security protocols for transferring packets across a network. It provides in-depth protection against various network-based attacks, data corruption, data theft, and user-credential theft. IPsec is provided by Windows on both the client and server ends of a connection, and it doesn't require any configuration by the DBA to use it. Typically, Windows systems administrators configure IPsec using the Windows Firewall with Advanced Security Microsoft Management Console (MMC) snap-in, which is available from the Administrative Tools section in Control Panel. IPsec takes some expertise to set up correctly, but TechNet provides documentation about what to consider and what to do.

If you use a storage area network (SAN) for off-server data storage and SAN communications to access it, you might want to encrypt the data as it moves across the network. It's possible to transmit that data in the clear, which poses a security risk for highly sensitive data because it is susceptible to sniffing by people in the SAN network.

Extended Protection to Prevent Credential Forwarding Attacks

SQL Server 2008 R2 introduced a security enhancement, Extended Protection, for one particular type of attack. Extended Protection isn't really a feature in SQL Server. Instead, it takes advantage of the Extended Protection feature that Microsoft introduced in Windows 7 and Windows Server 2008 R2.

Extended Protection is designed to prevent credential forwarding, which is used in man-in-the-middle (MITM) attacks. (It goes by other names as well, such as credential relaying.) Credential forwarding isn't an attack on its own but rather a technique used to take advantage of other types of attacks when they succeed, such as spoofing and luring attacks. Phishing often takes advantage of credential forwarding after the victims respond to the phish and click a link in an email message that looks like it came from a legitimate company, such as a bank.

Here's how credential forwarding works in general Web terms. It involves three participants: a client (you and the client software, such as a browser or custom application), a legitimate server, and an attacker. Let's say the attacker uses the website attack.com and your bank's site is mybank.com. The attacker uses some kind of attack, such as a phishing email, to lure you into accessing the attacker's website, thinking it was your bank's website. The attacker then sends a request to the legitimate bank website.

Being a secure site designed with reasonable Internet security in mind, the bank website responds to the attacker with a WWW-Authenticate header, essentially asking the attacker who he or she is and to provide proof of that fact, most commonly by providing a username and the password associated with it. The attacker doesn't have a legitimate account at the bank, so it turns around and passes that same authentication request header back to the client. Thinking you're on the bank's website, you dutifully enter your username and password. Then the attacker sends your credentials to the bank website and can do whatever you can do in the online bank. The bank thinks it's dealing directly with you, as "you" transfer the account's balance to some other country. Figure 4 shows a simplified version of the interactions.

Figure 4: Understanding credential forwarding

MITM-attack interactions

I used a web interaction as an example because that's the easiest way to explain what happens during credential forwarding. But the same kind of MITM-attack interactions can happen when you're using any software to access just about any kind of server, including a SQL Server database. These kinds of attacks are even scarier when the whole attack process is automated. You, as the victim, don't even have to be involved.

When a client application (often a browser) authenticates itself to a server using SSL, the server creates a Transport Level Security (TLS) channel and uses it to perform the authentication. This process results in two session keys: one generated by SSL to protect the transmissions between the client and server, and another key used by the authentication process. There's no binding between the two types of keys. This lack of binding is what enables the credential forwarding process.

Looking again at Figure 4, notice that there are two SSL channels created: one between the client and attacker, and another between the attacker and bank. The client's credentials transmit over the first SSL channel, and the attacker retransmits them over the second channel. The bank's server verifies the credentials without ever knowing that the channel over which it received them came from the attacker and not the client. Again, there's no binding between the two types of keys, so there's no way that the bank server could know that the attacker rather than the legitimate bank account holder was sending the credentials. So, SSL alone isn't a solution to this problem.

Microsoft's solution is Extended Protection, which is used to authenticate by means of Windows Authentication. Extended Protection sets up an outer channel secured by TLS and a client-authenticated inner channel, passing a Channel Binding Token (CBT) from client to server. This token binds the outer channel to the client-authenticated inner channel, merging the CBT of the channel between the client and attacker with the authentication information that's ultimately sent to the server. A server with Extended Protection enabled (in this case, the legitimate bank server) compares the CBT of the inner channel with that in the authentication information in the outer channel. Because the CBT is specific to the channel destination, the CBT that the server gets from the attacker won't match the CBT in the client credentials. When they don't match, the bank server refuses the authentication.

The technique of using channel bindings to secure channels of communication is described in the Internet Engineering Task Force (EITF) document RFC 5056, which you can find at tools.ietf.org/search/rfc5056. You don't need a detailed understanding of the protocol's details to use Extended Protection, but the information is there if you're interested.

One nice aspect about Extended Protection is that Windows handles most of the messy details for you. All you have to do is use a version of Windows that supports Extended Protection, mainly Windows 7 or later and Server 2008 R2 or later. For earlier versions of Windows, you need to install a Windows Update patch and enable Extended Protection by changing a registry setting. The Microsoft article "Extended Protection for Authentication"  provides the details.

Authentication

Authentication is the process of verifying that a principal-such as a user or process that needs access to SQL Server databases-is who or what it claims to be. A principal needs unique identification so that SQL Server can determine what permissions the principal has, if any. Correct authentication is a necessary first step in providing secure access to database objects.

SQL Server supports two paths to authentication: Windows Integrated authentication and SQL Server authentication. Windows Integrated authentication relies on Windows to validate users' identities when they log on to Windows. Permissions to access SQL Server objects are then assigned to Windows logins and groups. This type of authentication is available only when SQL Server is running on a version of Windows that supports Windows NTLM or Kerberos authentication, which is all versions starting with Windows 2000.

SQL Server authentication has SQL Server take care of authentication entirely on its own. In this case, you can create unique usernames and passwords. A user or application connects to SQL Server and supplies those credentials for access. SQL Server then assigns permissions to that login either directly or through membership in a role.

Frankly, if you want to implement strong security for the database and application, you need to implement Windows Integrated authentication using Kerberos. It's far more secure than SQL Server authentication, even with the enhancements introduced in SQL Server 2008 to support Windows password policies. Windows Integrated authentication is much more robust than SQL Server authentication because Windows transmits login credentials over the network in hashed form only. If you're going to use Windows Integrated authentication, make sure that you have the SQL Server instance configured to Windows Authentication mode, as shown in Figure 5. Besides relying on Windows for authentication, there's another reason to use this mode: the dreaded sa login.

Figure 5: Configuring a SQL Server instance to use the Windows Authentication mode

The sa, or system administrator, login is included mainly for backward compatibility with older versions of SQL Server. The sa account is mapped to the sysadmin fixed server role, so anyone using sa is a full system administrator, with irrevocable rights over the entire server instance and all the databases in it.

You should never use the sa login for access to a database in an application. Doing so could give attackers administration-level control over your database server if they were able to get control of the application. This has been an easy way to attack servers, so you shouldn't enable SQL Server authentication. If you ignore this advice, at the very least give sa an insanely long and complicated password, and rename and disable the login using code like that in Listing 2. But it's far better to not enable it in the first place. Although it takes more work to use applications with Windows Integrated authentication, the security benefits are dramatic.

The Principle of Least Privilege

Starting with SQL Server 2005, Microsoft made permissions far more granular than in earlier versions. It used to be that an administrator often had to assign a user to a fixed server or database role to enable the user to perform a single type of action on specific objects, which usually granted the user a far too broad set of permissions. This violates the security principle of least privilege, which means that you give a user exactly the permissions required to accomplish a task-no more and no less.

By adhering to the principle of least privilege, you take a major step in securing a database. If a user is allowed to only read product information in a database, the user won't be able to intentionally or accidentally delete the contents of the Product table. You're essentially building a tight container around what the user can do. Similarly, you need to define what permissions an application needs to perform its work, and grant the application only those permissions. You can also create a custom database role to grant the application what it needs and no more.

When defining the application's permissions, you need to answer one crucial question: When (not if) an attacker gets control of any aspect of the application or SQL Server, what will the attacker be able to do? If the application has the role of database owner, the attacker now owns your data. If all the application can do is read the product table-and only the product name and list price-then the attacker really hasn't gained much through the attack, particularly because that information is probably already publicly available on your website. It can be a pain to assign explicit permissions to the various database objects used by the application, but this is probably the most important action you can take to protect your data.

Check Security Continuously

The hardest thing about securing an entire application, front to back, is that once you've put in all the time and effort to make it as secure as possible, you're still not done. Over the entire lifetime of that application, you have to continuously check and monitor its overall security to make sure it's still secure and address new threats that didn't exist when you first deployed the application. You have to guard against new developers or DBAs changing the application or database in insecure ways, such as deploying new features that are poorly implemented from a security standpoint or taking insecure shortcuts.

You can make ASP.NET applications secure, but it takes a lot of work and vigilance. I've touched on only a few ways to secure your applications. In addition, you always have to customize security for your environment. What constitutes a threat for the applications I work on might be irrelevant for the applications you work on.

Listing 1: T-SQL Code to Create an Endpoint and Assign Connection Permissions

-- Create logins in SQL Server.CREATE LOGIN [PuppyUser1] FROM WINDOWS;CREATE LOGIN [PuppyUser2] FROM WINDOWS;-- Create the endpoint.CREATE ENDPOINT AppEndPoint1 STATE = STARTED AS TCP (  LISTENER_PORT = 9450,  LISTENER_IP = ALL) FOR TSQL();-- Resulting message: Creation of a T-SQL endpoint will result in the revocation-- of any 'Public' connect permissions on the 'T-SQL Default TCP' endpoint.-- If 'Public' access is desired on this endpoint, reapply this permission using-- 'GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]'.-- Set connection permissions for Public.GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to Public;REVOKE CONNECT ON ENDPOINT::AppEndPoint1 TO Public;-- Set connection permissions for User1.GRANT CONNECT ON ENDPOINT::AppEndPoint1 TO [PuppyUser1];DENY CONNECT ON ENDPOINT::[TSQL Default TCP] TO [PuppyUser1];DENY CONNECT ON ENDPOINT::[TSQL Named Pipes] TO [PuppyUser1];DENY CONNECT ON ENDPOINT::[TSQL Local Machine] TO [PuppyUser1];

Listing 2: T-SQL Code to Rename and Disable the sa Login

-- Rename sa login.ALTER LOGIN sa WITH NAME = Floyd;-- Disable the renamed sa login.ALTER LOGIN Floyd DISABLE;