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

Abstract

[...] relatively little has been written about protecting data by stepping back and looking at the entire application ecosystem. [...] . Because ASRNET provides a blank slate for an application, it doesn't start out secure.

Headnote

How to protect data while it's in motion and at rest in the database

Securing an ASRNET application with a SQL Server back end is an enormously complex task. People have written books about building secure ASRNET 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 ASRNET 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 instali 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 ASRNET 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 ASRNET 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 lo 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 Tech Net, and there is even more on third-parly websiles. 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:

* Microsoft Safety & Security Center (www .mtciOsoft.com/securiiy/defauii.aspxi

* SQL Server Database Security & Compliance (www.microsoft.com/sqlserver/en/us/solutionslechnologies/mission-critical-operations/ secunty-and-coniplianee.aspx )

* ASP.NET Security Tutorials (www.asp.net/ security/tutorials)

* SQLSecurity.com

Secure Communications and Connections

There are plenty of ways lo 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 I. 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 insUmee so that the Browser service doesn't reveal it to any applications thai inquire which ports the server is listening to. (One reason to not disable iI 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 thent applications looking fora 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.

An even better option is to create an applicationspecific endpoinl that listens on a nondeíiuli port, with the connection permissions set to lhe 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 going to www.sqlmag.com. entering 140417 in the Search box. and clicking the 140417.zip hoilink. Before you run it. you need 10 configure a SQL Server instance to listen to port 9450. change Puppv to your machine's name, and add lhe Userl and User2 Windows logins. Then, run the code in SQL Server Management Studio (SSMS) as Userl and Llser2 Io explore how each can connect to the instance. Userl will have to connect as Puppv,V45Q 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 lo Puppy through the Public role and can'l use lhe port number. As this example demonstrates, by using an endpoint. you can use multiple ports and limit who can connect through specific ports.

When data is in motion between the application and SQL Server, you can protect it using IPscc or SSL when using the HTTP protocol. IPscc is best used with internal network communications, but that works for ASP.NET applications because it's likely thai your web and database servers are local to each oilier. If you have to communicate data externally over the Internet, you should configure your network Io use SSL. Either way. your data will be encrypted as it moves across the network. You can generale 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 Uie protocol layer and is available 10 most clients, except for clients using DB-Library or Microsoft Data Access Components (MDAC) 2.5?. You can also use SSL for server validation when a client requests encryption. To do so. you need lo sel up the web server to trust lhe 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 opiional (No). Bt1 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.

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 net work -based attacks, data corruption, data theft, and usercredential theft. IPsee 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 TechNcl provides documentation about what to consider and what to do.

If you use a storage area network (SAN) for off-server data storage iind 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 lakes 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-themiddle (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 suy 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 allacker 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 hank, so it turns around and pusses that same authentication request header back Io the client. Thinking you're on the bank's websile, 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 hank. The bank thinks it's dealing directly with you, as "you'1 transfer the account's balance to some other country Figure 4 shows a simplified version of the interactions.

I used a web interaction as an example because that's the easiest way to explain what happens du ring credential forwarding. But the same kind of MITM-attack inieraclions 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 browner) a u then (rea tes itself to a server using SSL. (he server creates a Transport Level Security (TLS) channel and uses it to perform the authentication. This process results in (wo session keys: one generated by SSL Io 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 seni 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 .ieif.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" (suppori.microsoft.com/ kb/968389) 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 lhen 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 !Cerberos. 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 authenticaiion is much inore 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 (his mode: the dreaded sa login.

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 dalabase 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 il in the first place. Although it takes more work to use applications with Windows Integrated authentication, the security benefits arc dramatic.

The Principle of Least Privilege

Starting with SQL Server 2005. Microsoft made permissions far more granular than in earlier versions. Il used to be that an administrator offen had io assign a user to a fixed server or dalabase role to enable the user to perform a single type of action on specific objects, which usually granted the user a far too broad sel 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 lake a major step in securing a database. If a user is allowed Io 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 dalabase ovvnei; the attacker now owns your data. If all the application can do is read the product table- and only the produci name and list price - then the aiiacker 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 Io protect your data.

Check Security Continuously

The hardest thing aboui securing an entire application, front to back, is that once you've put in all the lime 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 siti) secure and address new threats that didn't exist when you first deployed the application. You have Io 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 lakes 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 lor your environment. Whal constitutes a threat for the applications I work on might be irrelevant for the applications you work on.

InstantDoclD 140417