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.