TechRepublic member E
Spigle
recently asked the following question in the TechRepublic Technical Q&A forum:

We are in the process
of converting a very old FoxPro system over to a MS SQL / VB system at the
company I work at. We are already utilizing the SQL database to a good extent
internally. It currently resides behind our firewall on our LAN and can only be
accessed as such for internal usage. Part of our conversion process will bring
some Web applications online, however. This is where our dilemma enters in and
we are trying to analyze the best solution to connect both our inside LAN users
and Web customers to the same database. Currently our IIS 6.0 Web server
resides on a DMZ. What would be the best way to let the Web server access the
database for the Web applications?

Beyond first blush

This is a very interesting question that at first blush
seems very straightforward, but in fact is more complex once you begin to think
about it. Any time one begins to contemplate making a database accessible to
the Internet, there are many questions to consider. The first questions I
always ask are “What kind of data are we taking about?” and “How sensitive is the information?”

My reason for asking these questions is to determine the
degree of acceptable risk associated with the data that will potentially be
compromised. If no risk is acceptable, then I am going to spend a great deal of
resources and effort to make my data as impregnable as possible. However, if
some is acceptable, then I will be diligent but not go to any extremes. For
example, if the data is a patient’s medical history, I am going to go to
extremes to protect the data, which means NOT connecting to it with IIS
or using SQL Server.

Before you go into an uproar about my last statement, I am
not Microsoft bashing. I am merely reducing risk factors. Microsoft products
can be secured and can work well in the above-mentioned scenario. However, it
is also a fact that due to its popularity, Microsoft products are more
frequently the targets of viruses, worms,
hacks, and so on. By not using Microsoft applications in low risk-tolerance
situations, I have fewer things to worry about than if I chose to use them.

Again, depending on the degree of acceptable risk, I will
then determine if encryption is necessary between the Web server and the
database, and whether the data needs to be encrypted at the database level as
well. If we want the highest degree of security, using built-in and third-party
encryption software will be the path that I will want to follow. Should the
data be less sensitive, I may not use encryption at all or use it to a lesser
degree.

Lastly, I will determine how connections will be made to my
database. If encryption is needed or desired, and/or my data is being accessed
via a client server application (as in the original question), then I will want
to employ VPN and an application-layer proxy. In addition, I may want to
include an application server in the mix that will stand between my Web server
and my database.

The answers to all of the above questions will help to determine
how I will set up my environment for accessing the data that resides inside the
network.

The setup

I am going to assume for the sake of this article that the
data being accessed in E Spigle’s
environment is not so sensitive that we need to build Fort Knox around it, and
that using IIS and SQL Server is acceptable. That being the case, one of the
best setups for this application combination looks something like Figure A.

Figure A

Network scheme

In Figure A, Web traffic, HTTP on Port 80 or 1443, is
allowed only as far as the Web server. The Web server then communicates with
the SQL Server over TCP on Port 1433. Another method, as discussed in the forum would be to use Microsoft’s ISA server as a reverse proxy
and allow it to control traffic to the SQL Server.

No matter which setup you choose, there is a laundry list of
items that you must go through to insure the security of your SQL Server
database. This information is readily accessible on the Web and involves
issues such as securing your Windows servers, securing IIS, securing SQL
Server, dealing with how the Web server “talks” to SQL server, which
accounts are used for authentication, what protocol to use, etc. Fortunately,
Microsoft has provided some help in the form of the Microsoft Baseline Security Analyzer (MBSA).

Finally, there is a slew of standards and procedures that
both your developers and DBAs should be incorporating into the application code
and the database to minimize security threats. Check out Microsoft’s Improving
Web Application Security: Threats and Countermeasures for more on this topic.

Best practices vs. acceptable risk

This brief article shows that there is quite a bit more to
the seemingly innocent question, “What would be the best way to let the
Web server access the database for the Web applications?” And that while
there may be cost constraints to adhering to best practices, ultimately it is
the degree of risk and the consequences of compromised data that should be the
deciding factors on how one proceeds.