Board index » delphi » Developing on MS SQL from remote site

Developing on MS SQL from remote site


2004-02-13 05:22:16 AM
delphi15
Greetings,
Our MS SQL Server is moving off-site. I have developed an in house business
application using Delphi to do specific business related task. The internal
client's use MS client connectivity tools to access the database server.
This works great because the SQL Server is currently internal within our
firewall.
Moving offsite will pose a problem because all client's access will now be
external with the server residing behind a firewall at a remote site. We do
not want to open a port for SQL.
Is there a way for in house reports to run against a SQL Server behind a
firewall at a remote site without allowing access to the sql server port.
I have thought of 3 possible short term solutions.
1. Put the applications on a second box within the same domain and behind
the same firewall as the remote SQL Server and have the users use Terminal
Server or PCAnyWhere to access the application server box.
2. Port the applications to .NET ( This is virtually impossible at this
point ).
3. Use the replication, publication, subscription, linked server, etc., etc.
functionality built into MS SQL Server to replicate data to an in-house MS
SQL Server for reporting purposes. I have not experimented much with the
above items and am not aware of all the pros and cons with doing so. Could
someone please give some advice.
Thanks,
Ross B.
 
 

Re:Developing on MS SQL from remote site

"Ross B." <XXXX@XXXXX.COM>writes
...
Quote
Moving offsite will pose a problem because all client's access
will now be external with the server residing behind a firewall
at a remote site. We do not want to open a port for SQL.
[SNIP]
Why is opening a port for SQL a problem? You could open it only to the IP
range assigned to your current office.
--
Ray Marron
 

Re:Developing on MS SQL from remote site

We may be forced to do it this way. My thinking is to assign it an unusal
port number. This is more an IT issue I am just reasearching different ways
of going about the move. Is it possible to route all outgoing traffic
through one IP address? We have a second database that is clustered, we are
keeping this one in house. The external server has access to the internal
database via a assigned IP.
I have forgone trying to keep up with both software and hardware so my IT
knowlege has been dumbed down. Is there a way to assign one IP address
inside our office and have all users pipe throught this one IP with access
to the opened port on the external server?
Ross B.
"Ray Marron" <marron+XXXX@XXXXX.COM>writes
Quote
"Ross B." <XXXX@XXXXX.COM>writes
news:402beeae$XXXX@XXXXX.COM...
...
>Moving offsite will pose a problem because all client's access
>will now be external with the server residing behind a firewall
>at a remote site. We do not want to open a port for SQL.
[SNIP]

Why is opening a port for SQL a problem? You could open it only to the IP
range assigned to your current office.

--
Ray Marron



 

Re:Developing on MS SQL from remote site

Why not just setup a VPN network gateway to the remote site. It will burrow through the
Firewall with no problem at all.
Dennis Passmore
Ultimate Software, Inc.
 

Re:Developing on MS SQL from remote site

"Ross B." <XXXX@XXXXX.COM>writes
[SNIP]
Quote
Is there a way to assign one IP address inside our office
and have all users pipe throught this one IP with access
to the opened port on the external server?
If your firewall supports NAT you should be all set - it will be the "one"
external IP address that the remote server sees and allows DB connections
from. Normally, everybody inside would just point to the real IP address &
port of the external server. Depending on it is capabilities, it could also
be configured to act as a proxy, where internal hosts making requests to the
firewall's port 5432 (PostgreSQL's default port, I don't know MS' offhand)
would be rerouted to the remote database's proper IP/port. This adds an
extra layer, but if the remote server moves or changes config, you only have
to update it at the firewall instead of on each desktop.
Chances are, if they know how to get the remote one to talk to your local
cluster, those same people will know how to do the reverse and let requests
from your firewall hit their db!
Good luck!
--
Ray Marron
 

Re:Developing on MS SQL from remote site

<Dennis Passmore>writes
Quote
Why not just setup a VPN network gateway to the
remote site. It will burrow through the Firewall with
no problem at all.
Oh yeah... What Dennis said!
It may be easy to allow access to the db remotely as I described, but if
you've got sensitive info on the db, you don't really want it going over the
wire in plaintext!
--
Ray Marron
 

Re:Developing on MS SQL from remote site

On Thu, 12 Feb 2004 16:22:16 -0500, "Ross B."
<XXXX@XXXXX.COM>writes:
Quote

Moving offsite will pose a problem because all client's access will now be
external with the server residing behind a firewall at a remote site. We do
not want to open a port for SQL.

Is there a way for in house reports to run against a SQL Server behind a
firewall at a remote site without allowing access to the sql server port.

Ross,
I work with a setup like this:
I have a Secure Shell server on a Linux host at the remote site, where
the SQL server is located. The remote site uses a private address
range behind a NAT firewall. The firewall has a rule that sends
traffic to the public SSH port on to the internal privately addressed
SSH server.
On my machine (NT4), I run a SSH client ( I use Putty) and create a
tunnel through to the remote SSH server. On my machine, I use all the
usual MS SQL tools (EM etc) and as far as they are concerned, they
connect to a local port.
I have had some problems with the NAT connection within the firewall
timing out if it sees no traffic over an extended period, and I have
to generate a bit of traffic simply to prevent this
Now it may be better to do this with a VPN, but the firewall hardware
at each end is different, and I have been unable to get one working so
far.
regards
Richard
XXXX@XXXXX.COM