Re: How do I have my app connect to a remote SQL Server that has port 1433 closed?

From: Joe Yong (jyongNOSPAM_at_scalabilityexperts.com)
Date: 03/22/05 

Date: Tue, 22 Mar 2005 16:41:49 -0600

Inline

joe.

"Alan Silver" <alan-silver@nospam.thanx> wrote in message
news:GvcKeYAwtCQCFwwU@nospamthankyou.spam...
> >I may be missing some details somewhere but here are a few
>>thoughts/questions:
>
> Thanks, I have commented below...
>
>>1. If you are running a web based application, why not make it a real thin
>>web client and have the relevant connection info/code reside on the web
>>server that's protected behind your ISP's firewall? That way, clients
>>point
>>to the web server and interact only with the web server. SQL Server access
>>is controlled by the web server and is invisible to your clients.
>
> I have web sites that run from databases. In order to allow the web site
> owners access to modify the databases, I have admin applications that
> install on their PCs. At the moment, those applications use a straight
> connection to port 1433 on the server.
>
> If I understand you correctly, you are suggesting moving to a web-based
> admin. Apart from the amount of work required to rewrite the applications,
> this also gives the added problem of extra pages on the site that I don't
> want anyone (other than the site owners) to see.
There are 2 different things here. If you have a custom application that is
being used to manage SQL Server, you can have a server component with the
ISP which this "Mangement App" talks to and this can listen on any port that
you want. The app then makes the connection to SQL Server from behind the
firewall. Not a pretty solution since it adds additional steps but it does
give you more control over how you want to layer things out plus it removes
the need to have 1433 exposed.

The same concept can be applied to a web based "management app" where all
the user gets is a very thin layer that hits the web server via some https
connection and issues whatever task instructions it wants (not SQL command).
The web server then makes the connection, again, from behind the firewall to
SQL Server on whichever port you choose. Do still be careful with SQL
Injections here even though the risk is quite a bit lower than sending SQL
across the wire. User responses are still a potential hole.

As far as pages is concerned, they can be "hidden" by filtering userIDs and
presenting only the objects relevant to the userID. You can also provide a
secondary login for administration. The ideas at the conceptual level really
don't change hugely from a rich client though the execution is very
different.

>>2. Having SQL Server's connection info hidden/encoded in the client
>>application hoping that nobody will ever know it exists is pretty naive.
>>It
>>may deter the average user but even script kiddies can figure out ways to
>>capture the URL information. Just looking at the outgoing stream at the
>>proxy server or turning on the sniffer will probably do the trick.
>
> Yup, that was my thought, which is why I posted the question here looking
> for a better answer.
>
> It's worth pointing out that the client app is not freely available, it is
> only supplied to customers, so it's not as much of a problem as it would
> be in other cases. Not that this removes the worry about people hacking
> the app, but it is a slight mitigating factor.
>
> Also, having the info encoded in the client app is no worse than having
> port 1433 open. They still have to find a uid and password. At least with
> the URL approach I mentioned, they need to figure out the URL, then figure
> out the parameters to send, then figure out the values to use, then find
> the Windows uid and password for the page. That's a lot more effort than
> simply bashing port 1433 with combinations of uid and password.
>
>>Btw, you can configure the default port to talk to from the Client Network
>>Utility. Just highlight TCP/IP and click on the Properties button.
>
> Surely that wouldn't really help. I imagine people looking for ways in to
> a server will just probe all ports and see if they get a response.
> Changing port wouldn't stop them for many picoseconds ;-)
>
> Thanks for the reply, any further thoughts appreciated.
So you already know putting it in the app doesn't really help. :-)

I'm not in favor of having any SQL being sent across the wire. I'm pretty
wary of SQL Injection risks.

>>"Alan Silver" <alan-silver@nospam.thanx> wrote in message
>>news:gRxjBXJ5YuPCFwup@nospamthankyou.spam...
>>> Hello,
>>>
>>> I have a few applications that connect directly to a remote SQL Server,
>>> mainly (but not only) so that web site owners can update the databases
>>> that run their web sites.
>>>
>>> The people that maintain the web/SQL server have now said (quite
>>> sensibly)
>>> that they want to close port 1433 as it is a security risk. I would like
>>> to know how my client applications are supposed to interact with the SQL
>>> Server if they don't use port 1433. The ISP told me that this server is
>>> the only one they have that has the port open, but they wouldn't tell me
>>> how to do the data access - well, they would, but they want consultancy
>>> rates for it!!
>>>
>>> So, anyone any suggestions? The ISP's only suggestion was to have a
>>> private ASP on one of the web sites, that you send the connection
>>> details
>>> and SQL, and it returns a recordset as XML. The client applications
>>> could
>>> open the recordset from the URL. They did say that they would put
>>> Windows
>>> security on the page, so anyone trying to get at it would need a UID and
>>> password, and that the actual URL should be encoded in the client
>>> application so no-one would ever know it exists, but it still sounds
>>> like
>>> a major security hole to me. I'm sure there are better ways of doing
>>> this.
>>>
>>> I'm sure plenty of people have found a secure way around this, but my
>>> searches haven't turned up anything helpful.
>>>
>>> Anyone any suggestions? TIA.
>>>
>>> --
>>> Alan Silver
>>> (anything added below this line is nothing to do with me)
>>
>>
>
> --
> Alan Silver
> (anything added below this line is nothing to do with me)

Relevant Pages