The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL-Server over port 80?

I have a client app I want to distribute that connects to a central SQL-Server (2000) on the internet. It only occasionally needs to connect to swap some data, BUT:

A lot of firewalls block port 1433, or any non-standard ports, and I was wondering if I should change the port to 80, or if SQL2000 provides a safer way of connecting via XML over port 80, or if I shouldn't be doing this, or what.

Can anyone offer me some guidance?

Teller Send private email
Tuesday, April 25, 2006
Set up your web server or a proxy server to intercept incoming requests and then forward them, as appropriate, to your database.

For example, if you receive... can simply decrypt the parameter, cleanse it, and insert it into your SQL Server database. Responses can be sent back the same way. (If you're transfering binary data, you can also do HTTP based uploads and downloads.)
Tuesday, April 25, 2006
The risk of running non-HTTP services over port 80 is that anti-virus software will spot it and decide that as it isn't HTTP you must be doing something nefarious. (Which, in a sense, you are, as the network administrator closed these ports in the first place to stop people using protocols (s)he didn't think they'd need to be using.)

Sending XML to and from your database over HTTP, if you can do it, would be a lot safer.
Wednesday, April 26, 2006
Think webservice to do the database interaction.

There's a reason you don't completely expose your DB server to the internet.
Caffeinated Send private email
Wednesday, April 26, 2006
From a big picture perspective - why does the client software need to connect to an SQL Server database on the Internet? What's preventing you from requiring that the client machine be allowed to do so?

For example, if I wrote "professional grade" brokerage software, I would tell the customer up front that they would need to get data from server X on a periodic basis, and due to the nature of the application, they will need to open these ports in the firewall.

Alternatively, let them host the data on their own database. You would then publish loader files that they can fetch via FTP.

If you're looking for a discrete way to verify that the software is properly registered, or the laptop hasn't been stolen, there are better ways.

(I came back to this thread because I have a widget on my computer that monitors computer activity including network traffic. Last night, the widget broke because it apparently hard coded the author's IP address and used that as a ping target. When the author's web site went down for some reason, my widget could no longer complete the ping to verify network connectivity and stalled or broke. Extremely fustrating.)
Wednesday, April 26, 2006
+1 Caffeinated

web service seems like the perfect answer.
outcast (ex-developer) Send private email
Wednesday, April 26, 2006
Sounds like a webservice is the way to go. There is no way to "require" that the customer open the port--some are banks and such with very strict security.

Does a Windows XP client need a bunch of SOAP components to be installed for this?
Teller Send private email
Wednesday, April 26, 2006
Good Lord. Please tell me which banks you have for clients so that I can pull my money right away! If you don't know any more about security than this then you are in grave danger of really screwing this thing up big time.

1) Never allow direct access to your database from the Internet.

2) A web service won't protect you if you allow people to send it raw SQL to be executed against the DB. Web services should provide business interfaces to be called much like a stored procedure. Please don't simply wrap the db access into a web service and expect it to be more secure.

3) Review your desing with people who understand security risks.

4) Read up on SQL injection attacks if you don't know what these are.
Monday, May 01, 2006

Don't worry about the banks, they would have none of it.

But everything is procedure calls already. There shouldn't be any SQL-injection.

Why I don't really get is why a webservice would add more security at this point. Same procedure calls, same parameters...?
Teller Send private email
Monday, May 01, 2006
Because the web service would be a specific call that does not contain raw sql. Something like:

updateInventory(string sku, int quantity)

The user of the web service does not have direct access to the tables in the db. This provides an additional layer of indirection.

But if you allow someone to connect directly to the db over port 80 then they can not only call your stored procedures but also execute arbitrary SQL against the db. Or at the very least, they can attack the db directly by looking for security holes.

Some security purists would also say that the db server should never sit on the same machine as the web server so that there is an additional layer that must be breached in order to get to the data.

But I'm certainly no security expert. And maybe that's why talking about opening your database up to the rest of the world over port 80 scares the heck out of me.  ;)
Monday, May 01, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz