Secure Architecture for an SQL / Web Server

There are many ways to hack a Web server. One cannot assume that database servers are unassailable
fortresses. So what should one do if a Web server which derives data from a database needs to be made
accessible? The most obvious answer to this question is to run like hell to a calmer job. Luckily there are
other, more rational answers. This article attempts to show the reader how to find the answers.
The Aims
Let us give a great deal of thought to what you should expect from a Web server based on
information from databases. Of course, the list does not contain all aims and it is rather general, so
anyone can add his particular demands but let us try to sketch a minimum set of these requirements.
Firstly, one usually begins with a small project, and then business starts to grow and the site is
expected to grow along with it, so the first thing you would expect is scalability.
Secondly, if you offer and show something to the world, it must work. Discontinuities in the
presentation of your offer imply that nothing is being offered. After all, no freight means no money. It
doesnt matter if this is real money gathered for each site read, or each banner loaded, or if it is virtual,
potential money, which you could gain by building your products brand. In general, you should expect
your server to be accessible.
Is that all? Nearly. There are two more odds and ends.
Security. Imagine your business running at its best, your products are selling well when, suddenly,
the fact that someone has broken into the security system of a profit-giving server comes to light. You
will lose your benefit, your clients, and your products will lose their branding. You may even forfeit the
trust of those who wanted to become your clients. Noting but losses. But wait a moment... if your site is
not designed to sell anything then... No, your companys image would suffer heavily. An insecure Web
server may be a gate into the company. And if the gate is at the same time a connection with a database...
I need not add anything further.
These are the basic aims. There is one more, very closely linked to security. Therefore, you must
always be in control of the cash in your hand and of expenses. Generally, you have to watch the system
maintenance outlay cost. Anyway, most systems that serve a growing company tend to get complicated
and become uncontrollable. Uncontrollable - that's the word. Such a situation must not develop. You have
to bear in mind the manageability of the systems all the time.
The Architecture the first encounter.

Fig. 1. The simplest way of connecting to the Internet. A configuration regarded as an unsafe one, which
is not recommended.
Figure 1 shows the simplest way to connect to the Internet. As you may see, everyone works in a single
network, and they have comfortable access to database updates (those in the SQL database), as well as to
the substance of the websites. Perhaps there is even a direct connection to the base of production or
1

storage data within the website. You economize on servers and licenses. The solution seems to be perfect.
Actually, you couldn't be more wrong. Such a (mis-) approach should be crossed out with a thick red line
so that no one would try using this architecture for designing active websites.
Where is this negative approach coming from? This is the reason:
The concern of scalability. Every increase in the load of the SQL /WWW server, whether its coming
from static substances or from database operations (in both cases the load resulting from publishing
websites is mentioned), tugs at a necessity to upgrade the server or buy a new one. You could say, Oh
well, we have more traffic on our sites so we must increase our expenses. Of course, greater traffic costs
more but why should you spend money without knowing which parts of the system need additional
financing? The memory modules that a Web server is equipped with may be cheaper than those used in
an SQL server. And hard drives? The only difference is the fact that an SQL-server hard-drive is far more
expensive than a Web-server drive. By the way, you cant really expect much traffic with the use of one
Web server. Especially when an SQL server gives it an extra load. This means that after having spent so
much money, you will realize that you have chosen the wrong approach since the beginning.
Availability is another option which is inadequate when this type of network architecture is used.
Please note that this type of architecture means the whole system must be halted if one of the systems
parts needs to be altered, e.g. if a patch for the MS SQL server is to be installed. If the functions of the
SQL and the Web server are separated, at least there is the possibility of a message saying, The system is
being inspected. It will start working in approximately ten minutes. In addition, if one SQL server is
used for both bookkeeping and warehouse software, patching it at your will is impossible. However, this
is necessary for the stability of a Web and SQL service. What can one do? Which should be sacrificed?
Security will be completely forgotten if you build a network like the one presented in Figure 1. If
one server takes care of publishing websites and is also used as an SQL database, an exceptional range of
dangers typical for both of them will exist. A potential attacker has a choice: will he first hack into the
SQL and then exploit the IIS features or will he start by finding the newest IIS bug which will allow him
to freely access the SQL and the intranet? Only the hackers imagination will decide what you will lose your companys reputation or its finances. Finally, one more thing should be mentioned. Studies indicate
that most hacking attempts come from the inside of corporate networks. This means that most attackers
are also employees. Why make hacking easier for them? Trust is a good thing of course but surveillance
is even better. Moreover, some attackers may be unconscious of what theyre doing. A situation when an
authorized user runs a closing of the month operation on the companys SQL is a good example. A server
loaded with this operation and also with the burden of the SQL and Web server (as a result of hosting the
companys website) starts to slow down. Not only does it worsen the quality of bookkeeping operations
but it also makes the website publishing work even slower. The Denial of Service attack has a very
similar effect.
Manageability. The last of the goals seems to have been secured. It is easy to manage a multiple
server network. However, before you think that this matter has been solved, another fact comes to light.
This type of architecture dissuades you from testing your new site or changing the SQL or Web server
configuration because both of them use the same system and a change that is suitable for the MS SQL
won't necessarily work for the Web server. This solution also leaves no space at all for one of the
management processes - the hardening of an internet-accessible system. I can assure you that correctly
hardening such an MS SQL server would effectively prevent it from working.
As you can see, simple and presumably cheap architecture achieves none of the expected
objectives. Running it becomes expensive too. This should be altered and the modification should be
something more than a firewall added at the contact point of the corporation network with the Internet. In
this case no type of firewall would manage to improve the company's security. It is true that setting up a
firewall would mean sufficient control over incoming traffic, so that a potential hacker would have to
start his attempts at the Web server. However these steps would not differ from those probably taken
before having installed the firewall. The firewall mentioned would, neither separate the server from the
corporation network, nor achieve any of the earlier mentioned targets. The modifications should be
deeper.
The Architecture the second encounter
2

Before the final fight for safe network architecture you should sketch a scheme of what you need, based
on what you have inferred.
We already know that the company network should be separated from internet-connecting servers.
These servers should be separated from the Internet and from possible attacks. This implies this division:
The exterior network, the outer zone, which connects directly to the Internet. The addresses would
be translated here and this zone cuts off every unexpected network penetration.
The demilitarized zone (DMZ) where you can find filtered traffic from both the inner and the outer
network. This zone does not allow any connections directly into the internal network.
The internal network, which allows ordinary traffic resulting from the companys needs, such as the
traffic caused by preparing data for the servers, installed in the demilitarized zone, to pass through. Only
necessary traffic is let through to the DMZ. For example, updating the server data or reading the log files
or data inserted by guests on the site.
Gathering these inferences eventually gives you Fig. 2.

Fig. 2. A secure Internet connection.

The scheme is ready. Now it is time to decide which servers should be standing in the trusted zone
and which should be in the limited trust zone. After dividing the servers into these groups one will have
to cope with another problem - managing them? The internal network is a space where the integrity of the
stored data is definitely secure so it seems to be a natural place for the basic database server. This will
raise another doubt. If the SQL server was set up in the internal network then a special rule would have to
be programmed on the barrier between the internal network and the DMZ. This rule would be necessary
so that the web server could update its data every time it is needed. The rule would weaken the barrier by
allowing connections initialized by the DMZ. One may agree to this and weaken the barrier (which
would cut off any traffic initialized in the DMZ from the internal zone) or one may agree to the costs of
replicating the SQL server in the DMZ. Security experts point out that the second solution is better. The
servers which should find their place in the trusted zone are called staging servers. They store the up-todate and correct substance that is used for updating the information stored by the servers in the DMZ
whenever such an update is needed. Obviously, a server from the inner network initializes the update. It
may be caused by the need to install a new version of the site or the destruction of the contents of one of
the servers in the DMZ, e.g. because of a crash. A similar process should be used when updating the
databases. In this case, however, you should download the updated databases through interaction with the
website users. The internal network should also have its own domain controller so that authorizations
could be efficiently managed inside it. The demilitarized zone is an area where data processing, i.e. web
servers and helping servers should be installed. The helping servers are: a DNS server, a domain
controller for the web server and the SQL server (or multiple ones). Further on in this article I will
explain how to connect the web and the SQL servers. I should also emphasize the fact that placing the
domain controller in the DMZ is not a mistake. Theres method to my madness.
The external network should contain no servers at all. It should only be composed of
indispensable network devices and (depending on what security policy youve chosen) the sensors of the
IDS system.
These explanations can be defined by further developments to the sketch in Figure 2.
3

Fig. 3. Simplified network topology for secure hosting of dynamic websites.

To make things easier, this figure does not contain the doubled elements of the network (the switches and
the fire-over firewalls). The domain controller of the secure internal network has been intentionally
omitted, as well as the connection with the corporation network, which should be linked with the safe
internal networks switch. The traffic should be filtered by a firewall and it should pass through an
encrypted VPN channel unless it is a local area connection. The corporation network filter rules should
define which users on the companys network are authorized to access particular servers (if possible try to
have an extra access server for the webmasters). The traffic from the corporation network workstations
should be cut off from direct access to the safe SQL server or the staging server (this should be a rule!). It
must also be said that these servers accept traffic only from other servers in this case the filtered traffic
from chosen servers on the corporation network. The necessity of initializing the connection through the
servers from the safe internal network and refusing traffic initialized by every server from the corporation
network may be another safeguard.
Does such a network fulfill all the tasks described in the beginning of this text?
Lets check it out:
The scalability task. This has been fulfilled and you can increase the system capacity related to
functions of higher computational power relatively cheaply (for only the price of the hardware involved).
Upgrading the web servers will allow new, more memory-demanding applications to be run. Adding new
web servers to the NLB cluster will allow you to handle the growing traffic. Higher demands towards the
SQL server may be handled by doubling it (with the use of a fault-tolerance cluster, i.e. Microsoft Cluster
Service). You can partition its data, dividing it among many databases or SQL severs, too.
Availability. This task has been fulfilled. When this architecture is used, every element of the
network may be doubled in such a way that a crash will not affect the web site users in any noticeable
manner. With such doubling one can easily perform maintenance operations even in prime time. To start
installing updates or patches you only have to switch the traffic from one server to another. The systems
maintained in this way have a chance of being as safe as possible at that particular level of knowledge.
Security. While taking advantage of the possibility of defining strict rules of communication inside the
4

network one must achieve a satisfactory level of security. This involves a suitable distribution of roles
among the systems. In this case hacking into one of the barriers won't compromise the whole network;
such a hack will be limited to several devices or systems. For instance, breaking the first firewall will not
cause a catastrophe. The hacker would only have access to the network cards of the web servers which
have been placed outside and that have been protected as well as possible. In this case the servers'
protection may be pushed to the limit because they only keep the URL's data; therefore the process will
be efficient. In effect, the only threat to the whole system is to be flooded with unfiltered
TCP/UDP/ICMP packets of the published network cards of the web servers. Of course, despite the
existence of the firewall barrier the attack may be addressed directly at the web servers, with the use of
the newest (or most fashionable) gap in the IIS security locks. This case is a bit more dangerous but
besieging a single server is no victory for the hacker because all of the server-database connections run in
trusted mode. This means that there is no APS site that could give the attacker a password or account
enabling him to penetrate the system further. On the other hand, there is a good chance that another server
from the NLB cluster would answer the attacker's query, causing slight confusion. The attacker can't even
dream of going further, to any of the DMZ servers which only work in the internal network (SQL, DC,
etc.). The correct hardening process of the external systems makes sending their packets into the network
impossible. Please notice how far the aggressor is from the trusted internal network, the true heart of the
system. Attack attempts from the corporation network are not definitely successful because a good set of
rules does not allow direct communication with the servers. Moreover, even if the firewall is broken from
the corporation network's side, all communications should stop at once since the servers will demand
appropriate IPSEC packets. Those will not be available because the attacker will have just turned off their
source. Well, it seems that we have just designed a perfect system. This may be true but you should never
fall into a routine and become too self-confident. The history of successful hacking attempts shows that
every system can be hacked with the right amount of expertise and funding.
Manageability. It seems that the system in Figure 3 also fulfils this task. Every element of the
system is available and can be analyzed. Using this type of architecture makes it relatively easy to specify
the updated and future burdens, as well as to define its sources and therefore to determine the expenditure
necessary for a state of equilibrium. So, an architectural scheme of safe co-existence of SQL and web
servers seems to have been worked out. Is that all? Unfortunately not. Something should now be said
about matters which are above the average administrators influence, but which may efficiently annihilate
all of his work on the servers safeguards.
Writing a safe code
As discussed earlier, there is one element capable of annihilating all of the expenditure on safe
architecture. The ASP site and the ASP-generated elements' code, which are being executed during the
processing of the web site, are the magical annihilators. It seems that the best way to create active web
sites is by dividing the whole Web-presented application into layers: the layer of presentation, the layer of
business logic and the layer of data. On the one hand this type of division allows quick changes in any of
the layers with no need to rebuild the whole application. On the other, it is a good basis for constructing
safe Web systems and adjusting them to various data loads. The presentation layer is placed in the web
servers themselves. This layer consists of elements of the ASP code, which are responsible for presenting
the data. These are obviously subject to the Webmaster' needs and his demands concerning the look of the
website. At the same time they are safe for the administrator. This layer is also responsible for deriving
the data from the form. Further processing of the data takes place in another layer called the business
logic layer. Usually this layer's elements work as single components (registered at Component Services).
They are called out from the website just as any other component, e.g. the FilesystemObject. As I have
mentioned, the business logic layer components are registered as separated elements. Every one of them
may work on an administrator-defined account (on a domain or locally). Every one of them may use its
own, account-attributed rights, however nothing further. Please note the existence of an ASP site,
working as an IWAM_machine or an IUSER_machine (depending on the executed fragment of the site's
code) with standard, low rights for these accounts in the system. Hacking the server will give the attacker
guest's rights. However the system has been well protected, so the guest will not be able to damage much
and he will look for a more hospitable host. How is it possible then, that at such a low level of
authorization, you can call a connection with the base to download data for the presentation? The ASP
5

code does not have to contain the SQL account and password. It only needs to call the right component,
which also does not know its password or account. However with the use of a trusted connection within
the database, as well as working in the context of the Component Services defined account, it will receive
(i.e. the data layer) the necessary information from the database. This is clean and clear data flow, fully
controllable from the administrator, leaving unequivocal fingerprints on the system. These fingerprints
are unequivocal because they come from one account through the whole data path. It is important to look
out for the SQL server's mode of work. It may only accept trusted connections. The data should be
carefully verified and searched for elements, which are dangerous for the SQL server. This should happen
before the data enters the data layer. Unfortunately, it is very easy to smuggle your own commands onto a
protected SQL server with all the risks involved. Even the most complicated and the most expensive
architecture of the systems and applications will not protect you from such mistakes. Everything that
comes from the network must be verified, and one must not assume that something has never happened
as yet. Someone will eventually show up with a certain combination of characters, which enable him to
enter. Someone will finally get something he shouldn't have.
Those of you who are interested in pursuing in depth the question of creating safe web server
applications, should read the following books: Designing secure Web-based Applications for Microsoft
Windows by Michael Howard andWriting Secure Code by Michael Howard and David LeBlanc.
See Also
An Overview of Common Database Architectures 23 Jan. 2013 Bradley Morgan
Kerberos in a Sharepoint Environment 30 July 2008 Jesper M. Christensen
Web Applications Exploits 16 June 2011 George Chetcuti
Is There an Alternative to Reperimeterization? What Will You Tell The Judge? 16 April 2008 Debra
Shinder
Building Your Firewall Rulebase 24 Jan. 2013 Lance Spitzner
Microsoft Private Cloud - Overview of Hypervisor Security 19 Oct. 2011 Deb Shinder
On Virtualizing Network Security Devices 14 Aug. 2008 Debra Shinder
What is SQL Injection? 24 Jan. 2013 Acunetix
Microsoft SQL Server 2000 Authentication Mechanisms 26 Nov. 2003 Przemyslaw Kazienko & Piotr
Dorosz
Linux Administrator's Security Guide - Network services - DNS 24 Jan. 2013 Kurt Seifried