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.
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.
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.