clutch - networking security issue, please read

Hi Clutch,
I write you because I know you seem to have quite an extensive knowledge of Windows networking and such. I read your responses on a regular basis, and respect you for the help you've offered. So here is my issue, and Id like your input.

Currently, I have a windows NT 4.0 network. Im planning the migration to Win2k, and hope to have that done by years end. Here is my delimma. I have a network of 3-4 server, 60 desktops. Of the servers, I have a sql server 7.0 server which we use for heavy database management and storage. This box is a Dell 6300 with dual xeon processors, raid 5 drives, 2gb ram. We also have a identical server in a co-location site. We have 2 firewalls, one on the LAN side, and one on the co-located server site. Between the firewalls, we have an encrypted tunnel, but the tunnel is uni-drecitional. We can send ANYTHING to the co-located server, but the co-located server, cant send anything back. The logic behind us is that its ok for us to send it info, but if the server is compromised at the co-located site, we dont want the hackers or whatnot to have access to our lan. NOW, we have signed contracts with a client of ours to design and host their pages, but in order to do so, it requires real time inventory status. So, ideally, we'd like the web server to be able to contact the SQL server on the lan to process queries. and return values to the web server. But our goal is to do this in the most secure way possible.

Things that I can think of:
the easiest would be to allow the web server to send out on the SQL port ONLY to our server here. have the firewall ONLY allow SQL packets to ONLY our sql server. that is my question to you, do you consider that safe or not?

next, we can get into leased lines between the two, but that seems expensive and difficult.

Let me know what you think. If anything else pops up in your head as a viable solution, Id really like to listen.

Let me know.

jeff

First of all,

Secondly, it sounds like you have a well thought out system for your network. It seems like you desire to store all the backend tier info on your SQL box, and have the web app query and return across both of your firewalls, right? If you lock down the responding traffic in the method you describe (only let it respond to the proper IP), it sounds rather strong. I do have a couple of questions though:

1. What is the nature of the information? Is it customer records (like account info) or some sort of proprietary information?

2. What is the speed of the link between them?

3. How many records do you plan on returning per session?

One thing that I am not sure of (and I am actually in the process of learning) is the nature of SQL server in its replication. If you have a major amount of records to exchange back and forth, would it be possible to just have an SQL box at the co-location site replicate the database between itself and your SQL server? This would probably increase performance significantly, but would make the records more prone to "aging" (where the records on one database will not be inline with the other until their sync job kicks off) and may alleviate concerns to some extent about the security of traffic between the sites, as the exchange would ONLY occur at prescribed times, and some systems will allow for logons (like NT/2K) to only be usable within a certain time span.

Now, while that may be a bit extreme (and probably not necessary), there are only a couple of things to worry about in using live transmissions between the web app and your SQL DB.

Security-Generally, the only ways that somebody would get information from the DB would be to either:
A. Access the SQL server directly, but you would refuse logon based on IP (spoofing could be used to send traffic in *maybe*, but it would only respond to the correct IP and not the hacker's thanks to the firewall), and
B. Montior the traffic between the web app and the SQL box, but that would entail having a sniffer somewhere between the web app server (since that's the context most dynamic apps will run in, and not from the client) and the SQL box. The odds of this happening are low, and I am not well versed in the encryption methods that would be available for this mode of record exchange.

Performance-If you pass a large amount of records back and forth between the web app and the SQL box (like paging records in ASP, vs a Stored Procedure) you could get a major slow down in the app over time.

This is based on what I know of security, but I am sure there are much brighter people on this board in this subject than I will ever be. However, it would seem like your method should work rather well. If you do plan on exchanging information such as credit card info and the like, I would strongly suggest you talk to a professional consulting firm in your area, as they might be able to point you to VPN type tunneling (such as Shiva or even the Routing and Remote Access function of Win2K, where you can assign a key to the web server, and allow the SQL box to respond to it) such as IPSEC that can not only encrypt the traffic, but make the server seem "invisible" to systems trying to query it.

I hope that helps a little, if at all...

Thanks Clutch,
Your words are helpful. And to answer your questions:

1. The nature of the data that will be transferred is that of rather simple inventory type data. Nothing too crazy to worry about. The biggest concern is allow any traffic to our LAN here.

2. Currently, the speed we have between our two SQL servers (note: it sounded like you didnt understand that we do in fact have 2 sql severs. One on the LAN side, and one on the Co-location site). I also looked into the replication of the two, but from what I understand, it requires enterprise versions, which the license on a dual processor on a web server is very expensive. Its still an option, but also like you said, you still have periods between replication time, which is an important issue Im glad you brought up. Anyway, the speed is an ADSL accoutn rated at 1.54mb So its decent, but like anything, a high amount of requests will kill the bandwith.

3. I am unaware of how many records per session. Most likely only 1. And of course it would be desireable to lower this to as little as possible, as well as keep record length into account. Like I said, this stuff should be relativly small.

Another thing, we currently have a very sercure IPSEC tunnel between the two firewalls. So, all that data is encrypted, so I feel this is relatively safe. I just wanted to write everything down, and have someone else look at it for a bit so I can eliminate leaving any holes open. Also with the very nature of SQL server, and our given application, little traffic should be send across.. The biggest being the records requested.

Thanks, your comments will help in the design of the app. I appreciate your time.

j

If you are already using IPSEC, then you are set in my eyes. You sound like you know what you are doing, and you should have great success. As for replication, I haven't messed with either version of SQL enough to have a good grip on it, but both versions of SQL 2K do support it (I know you have SQL 7, but in case you were thinking of upgrading...). Here is where I am getting our copy of SQL 2K Enterprise (single proc license):

http://www.coast2coastaz.com/pcs1msft.htm

The price is actually $4350, but that is a HELLUVA lot cheaper than the $17,800 that CDW quoted us. We got Exchange 5.5 from them last year, and they have had really good service.

Now, here is the listing of the differences between them both main versions:

http://www.microsoft.com/sql/howtobuy/choosing/default.asp

AlecStaar,
Wow, thanks for the great info. Im going to refer back to it when I have a moment to take notes. Thanks again!

jeff

Just a couple thoughts -- generally the slowest part of a web app is the database connection stuff, so that's the first place to optimise. (especially the getting rid of Access part).

The SQL Server driver supports connection pooling, so make sure that you are opening and closing connections only when necessary (not in app or session scope). Also, use Disconnected, Forward-only recordsets because those are cached on the client (webserver) side.

Other forms of recordsets may require a long-lived connection to and maybe a lock in the database. Something like rs.open(query, conn, adOpenForwardOnly, adUseClient, adCmdText); Do updates through stored procedures rather than recordsets if possible.

Now that's something I haven't really thought of before. Right now, I am only doing single record updates anyway, but have you observed much of an improvement using SPs in such a small scope?

Well if you get a two-way (<-- not the correct technical term) recordset, the db server has to maintain a lock on the data and hold the connection open because you *might* update it.

Even though it's only a short amount of time during the page processing, the server doesn't know this and still has to do the setup/removal of the lock. Furthermore, there's more chatter between client and server. ("Have you locked this data?" "Yes" "OK - Do this.") This will affect scalability quite a bit.

It's better to get the data client-side, read-only, drop the connection, do the processing, open the connection again, execute a SP or SQL statement to update teh data, and then drop the conneciton. No locks then, except on the serverside in the stored procedure (where it's potentially optimised).

Since the connections are pooled, opening/closing connections isn't much overhead.

Hi guys, quite frankly I did not read all of the posts above since they are too long. But from I understood.

I would suggest that instead of transmitting between your LAN and web SQL. you should set a replication service and for this yes you do need SQL Enterprise whic is about GBP£12000 But believe me it is worth it. about footing the bill. as far as I am concerned it is actually your customers concern.

I use a similar system for an online job agency company. Where we have installed SQL enterprise on the servers and they are replicating each other in real time . New CV's or user data is entered in to the web server which keeps it foritself but sends a copy to Local one for DB clerks to process or modify which tha asks for it back to update it. This works so far very fine.

About the cost of SQL, as I said it your customer who has to foot the bill even if this is not the case it is a worthwhile investment since there is no limitation to for how many customers you can use it for.
If this is your wish all you have to do is pop in another box. and there is even an upgrade version i believe.

About the security up to the point which i followed the conversation you were talking about IP filtering and that you already have IPSEC. Well as you already know you do not really need anything else. If you like to you can go for server certificates or even PGP encryption between the servers but your performance will suffer seriously in this case.

Another thought just occurred to me is. Why don't you outsorce your web SQL Enterprise? If you are in a colocation I am sure there is an ISP with SQL box? just rent it from them. I dunno about yours but in ours we always share servers in times of need

I just read the last of the post. Sorry guys I just repeated what you have said. Just ignore me.!