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

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)I may be missing some details somewhere but here are a few
thoughts/questions:
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.
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.
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.
joe.
"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)|||>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.

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

>"Alan Silver" <alan-silver@.nospam.thanx> wrote in message
>news:gRxjBXJ5YuPCFwup@.nospamthankyou.spam...
>
Alan Silver
(anything added below this line is nothing to do with me)|||Inline
joe.
"Alan Silver" <alan-silver@.nospam.thanx> wrote in message
news:GvcKeYAwtCQCFwwU@.nospamthankyou.spam...
> Thanks, I have commented below...
>
> 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.

> 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.
>
> 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
> (anything added below this line is nothing to do with me)|||>Inline
Thanks Joe, some useful comments there. My ISP pointed out that they are
moving everything to .NET as the security is much better there. If I'm
going to do any major rewriting, it would probably be better to go that
way.
In the meantime, I might look at using the server component like you
mentioned. I guess this is a sort of DCOM approach, no? Given that my
data access is all modular, I would only have to change one common
module and then recompile all the client apps, so it might not be too
bad.
Thanks again for the comments.
Alan

>joe.
>"Alan Silver" <alan-silver@.nospam.thanx> wrote in message
>news:GvcKeYAwtCQCFwwU@.nospamthankyou.spam...
>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 tha
t
>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 t
o
>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 reall
y
>don't change hugely from a rich client though the execution is very
>different.
>
>
>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
(anything added below this line is nothing to do with me)