VB app connect THROUGH proxy to a remote SQL database

[Gshell]

I have a VB.net app that connects to a remote SQL server via TCP/IP. Most
folks can connect just fine. However some enterprise users are in a
corporate environment that will only allow connections through a corporate
proxy server.

The VB app does the connect to the remote SQL server via a fully routeable
IP address over port 1433.

Having no such experience, I have no idea what I need to do to my app to
allow that. And what sort of interaction I will personally need to have (if
any) with thier IT department to configure the proxy server in any way. The
user mentioned that "Only permissible outbound connection is through proxy
server and it is only allowed with TCP/IP port 80 and it is only possible by
authenticating with Domain ID and password." I would assume that means the
connection from the VB app to the Proxy server is over port 80 and that
somehow the proxy server will know (or be told) to then route to IP address
of the SQL Server over port 1433.


Can anyone offer any insight or links that might point me in the right
direction?

Thanks!

 

[Mr. Arnold]

I have a VB.net app that connects to a remote SQL server via TCP/IP. Most
folks can connect just fine. However some enterprise users are in a
corporate environment that will only allow connections through a corporate
proxy server.

The VB app does the connect to the remote SQL server via a fully routeable
IP address over port 1433.

Having no such experience, I have no idea what I need to do to my app to
allow that. And what sort of interaction I will personally need to have
(if
any) with thier IT department to configure the proxy server in any way.
The
user mentioned that "Only permissible outbound connection is through proxy
server and it is only allowed with TCP/IP port 80 and it is only possible
by
authenticating with Domain ID and password." I would assume that means the
connection from the VB app to the Proxy server is over port 80 and that
somehow the proxy server will know (or be told) to then route to IP
address
of the SQL Server over port 1433.


Can anyone offer any insight or links that might point me in the right
direction?

It seems to me that your application needs to consume and become a client
application to a Web service application that you have designed to work
with SQL Server. I see no reason why you cannot remove all of the SQL code
from your VB application and place it in a Web service. Your VB application
would consume the Web methods of the Web service dealing with SQL server and
use them as if the code was in the VB application itself.

You would have inbound/outbound over HTTP TCP 80 for your application, while
the Web service application uses TCP 1433 to access SQL server to send data
from your VB application or receive data from SQL server with the Web
service using a Dataset, Datatable or a collection of Business and DAL
objects etc, etc on the back-end to pass data between the two applications.

That's one way you can do, because otherwise, they may not let you do
anything else from a proxy standpoint.

 

[Gshell]

Thanks for the suggestion. I know that is a possible solution, but not one we
are interested in using at this juncture. That is a good long term solution,
and one we are pursuing, but I am need of a short term fix that doesn't
require the additional infrastructure required for a web service enablement.


Gary

 

[Mr. Arnold]

Thanks for the suggestion. I know that is a possible solution, but not one
we
are interested in using at this juncture. That is a good long term
solution,
and one we are pursuing, but I am need of a short term fix that doesn't
require the additional infrastructure required for a web service
enablement.

You can talk to the firewall and proxy administrators. but based on my
experience in dealing with them, they are not going to bend for you. They
are not going to open TCP 1433 for SQL server to the outside world nor are
they going to be doing any routing to it either. A solution is going to be
needed behind those solutions that is going to make contact with SQL server
with your solution making contact with the solution.

If it goes any other way, then the administrators do not know anything about
securing a company's network infrastructure.

 

[Gshell]

Thanks! I appreciate your help and candor.

I suspect that what the person was trying to convey when they said "Only
permissible outbound connection is through proxy server and it is only
allowed with TCP/IP port 80 and it is only possible by authenticating with
Domain ID and password." Was that the outbound connection from the proxy has
to be over port 80. Which then loops me right back to a web service.

Thanks again,

Gary

 

[Guest]

Having no such experience, I have no idea what I need to do to my app
to allow that. And what sort of interaction I will personally need to
have (if any) with thier IT department to configure the proxy server
in any way. The user mentioned that "Only permissible outbound
connection is through proxy server and it is only allowed with TCP/IP
port 80 and it is only possible by authenticating with Domain ID and
password." I would assume that means the connection from the VB app to
the Proxy server is over port 80 and that somehow the proxy server
will know (or be told) to then route to IP address of the SQL Server
over port 1433.

You should build a web service to interface with your database server.

 

[Mr. Arnold]

Thanks for the suggestion. I know that is a possible solution, but not one
we
are interested in using at this juncture. That is a good long term
solution,
and one we are pursuing, but I am need of a short term fix that doesn't
require the additional infrastructure required for a web service
enablement.

The other solution you might be able to sale someone is to put that
application on a Windows Terminal server. That way, users log on to the
terminal server and will be in their own desktop session, as if that
application was being ran from the desktop on their client machine. The
Windows Terminal server is behind the firewall and the proxy.

 

[Gshell]

Now that's an interesting idea and thinking outside the box! I had not even
thought of that.

But your last line confused me. How would the Windows Terminal Server be
behind the end users firewall and proxy server? I would have to have Terminal
Server running at MY site, since the SQL database server is also at my site.
And if I did this, am I not likely to have the same issue with the Windows
Terminal Server cleint on the end users machine needing to punch through the
proxy server?

Gary

 

[Mr. Arnold]

Now that's an interesting idea and thinking outside the box! I had not
even
thought of that.

But your last line confused me. How would the Windows Terminal Server be
behind the end users firewall and proxy server? I would have to have
Terminal
Server running at MY site, since the SQL database server is also at my
site.
And if I did this, am I not likely to have the same issue with the Windows
Terminal Server cleint on the end users machine needing to punch through
the
proxy server?

I thought that everything was at the client's site and you were wanting to
use your application on client machines that were not part of the client's
company internal LAN infrastructure.

You also might want to look a the Citrix Terminal server software that can
be installed on a Windows server and the Citrix client software installed on
the client's machine. That way, the client can use his or her Web browser on
their client machines as an individual session to make contact with your
Citrix terminal server software listening on HTTP TCP port 80, as individual
desktop sessions on your end.

They can't say that outbound on port 80 through their proxy is not being
used. <smile>

It might be a win solution for you.

 

[Gshell]

The application is installed on an end users laptop. It then connects to a
SQL server in my office. That server does have the capacity to also be a
Windows Terminal Server for this purpose. But I suspect the client for
windows terminal server would not use port 80. But it sounds as if the
Citrix stuff WOULD work via port 80.

Again, thanks!

Gary

 

[Alex Clark]

While a lot of people are recommending you go through the not-insignificant
task of redesigning your entire DAL/mid-tier to work via a web-service, I
actually would recommend talking to IT first and seeing if there isn't a
quick (and still secure) alteration they could make for you.

If I understand you correctly, the client app and the SQL server are on two
separate networks and there is a proxy between them. From what I can
gather, incoming connections to the SQL server are not the problem - it's
outgoing connections from your client apps which have only an HTTP proxy to
go through that's causing the headaches. Had IT configured the client-apps
network with a router rather than just an HTTP proxy, things would be
fine --- please correct me if I'm wrong.

Real routers, as opposed to HTTP proxies, generally allow outgoing
connections on *any* port unless specifically configured otherwise. There
is no real threat in allowing a client from behind the router/firewall to
initiate an outbound connection to a remote server; many corporations use
this configuration.

 

[Gary Shell]

You understand the porblem just fine. However convincing the end users IT
department to make such a change would shall we say be "pissin' in the
wind". That's not going to happen, unfortunately. They are a HUGE
corporation and are not going to redo things to accomodate my app.

Gary

 

[Alex Clark]

That's a bummer. You might be able to extract just the core elements of
your DAL (i.e. just the SQL helper functions that handle actual
communication to the SQL Server instance) out into a web-service and
redirect your clients calls to it?

I feel your pain in terms of inflexible IT admins thinking the network is
their baby. The whole "HTTP proxy is way more secure than allowing outgoing
connections willy-nilly!" line is amusing to say the least...

Regards,
Alex

 

[Mr. Arnold]

I feel your pain in terms of inflexible IT admins thinking the network is
their baby. The whole "HTTP proxy is way more secure than allowing
outgoing connections willy-nilly!" line is amusing to say the least...

It's their show. One denies all outbound traffic and only allow outbound on
approved ports. Apparently, in that organization there are only allowing it
on HTTP port 80, and the the user must give a user-id and password to go to
approved sites with a browser with the proxy sitting there.

It doesn't take much for a user with a browser to click on something with
the happy fingers that will click on everything under the Sun that will lead
to a compromise of a machine and a possible compromise of the company's
network.

http://www.eweek.com/c/a/Security/Hundreds-Click-on-Click-Here-to-Get-Infected-Ad/

 

[Alex Clark]

It doesn't take much for a user with a browser to click on something with

the happy fingers that will click on everything under the Sun that will
lead to a compromise of a machine and a possible compromise of the
company's network.

Undoubtedly, but that's why it's up to IT to use group policy settings to
secure the desktop. Preventing all outgoing traffic becomes irrelevant if
the user can never install unruly ActiveX controls, bring in their own
infected software on flash drives, or download and run any file from any
site. Proxies have their uses, but I would never rely on one for keeping my
network clean. I find they're best suited to filtering HTTP traffic to
approved sites and/or monitoring what your employees are surfing to during
their lunch hours.

Unfortunately, as you say, it's their show. Whatever they've been
indoctrinated with, they tend to follow it rigidly and steadfastly refuse to
be flexible, no matter how much of a benefit it could be for the
organisation they work for. That's why it's always such a pleasant surprise
to happen upon the occasional IT admin who's ready and willing to help!

 

[Grok]

Undoubtedly, but that's why it's up to IT to use group policy settings to
secure the desktop. Preventing all outgoing traffic becomes irrelevant if
the user can never install unruly ActiveX controls, bring in their own
infected software on flash drives, or download and run any file from any
site. Proxies have their uses, but I would never rely on one for keeping my
network clean. I find they're best suited to filtering HTTP traffic to
approved sites and/or monitoring what your employees are surfing to during
their lunch hours.

Unfortunately, as you say, it's their show. Whatever they've been
indoctrinated with, they tend to follow it rigidly and steadfastly refuse to
be flexible, no matter how much of a benefit it could be for the
organisation they work for. That's why it's always such a pleasant surprise
to happen upon the occasional IT admin who's ready and willing to help!

As a vendor it is not your place to ask IT for routing exceptions. You
have a business contact to whom you are selling the solution. Give
them the requirements of your application. They submit the formal
written request to their IT, which probably will result in a meeting.
It then becomes the gargantuan company's IT department job to
accomodate the business needs of their customer.

Vendors should avoid any appearance of dictating security policy to a
company's IT department. Suggest they should change because other
companies do it this way and you'll learn about getting stone-walled.

 

[sloan]

Faced with the "Port 80" only scenario....using a webservice as a middle man
is the route I would take.

HOWEVER.

I would write WCF code. And then provide a WebService ENDPOINT to that code
(instead of writing just a Webservice).

...

Here is a starter/helper blog entry:

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!158.entry

If you need non DotNet clients...you would not follow my article exactly.

Check www.idesign.net for WCF samples as well.