Re: Adp to SQL 2000 Internet connection basics

Tech-Archive recommends: Speed Up your PC by fixing your registry



The only way that I know of to connect remotely and securely to a SQL-Server
over the WAN (Wide Area Network) in an efficient way would be to use
Terminal Server or its look alike Thinsoft. The latest version of server OS
(Win2008) is supposed to offer a very good implementation of TS; even better
than the one with get today with Win2003.

Beside TS, if you want to have a good performance over the WAN, using
unbound forms could also be a good idea because this give you complete
control over the queries that are done against the SQL-Server. BTW, using
..NET instead of ADP would be a step in the same direction because bound
forms in .NET are based on disconnected recordset; thus giving you the same
kind of performance as unbound forms with ADP or MDB.

An even better idea would be to have a local instance of SQL-Server and have
it replicates or synchronises with the main server. This (or using TS) is
probably the decision that I would make if I had to connect to a sql-server
over the WAN. Setting up replication is more or less difficult but
synchronisation is not.

There are other possibilities like the SQL Nitro mentionned by A. Kelly but
I never tried it personally; so I cannot tell you anything more on this.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Marcus" <marcus@xxxxxxxxxxxxxxxxxx> wrote in message
news:SsGdnYw2EcVdwYrVRVnyjgA@xxxxxxxxxxxxxxxx
Fantastic!

I've just changed the variable I used to contain the server name with
a.b.c.d,5555 (with port mapping enabled on the router and Voilà!!!!

While the connection is immediate, retrieving records isn't that fast, but
there may be some techniques like retrieveing small record amounts or
maybe compressing data.....

Thanks Silvain!

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:O1pvwGhqIHA.1772@xxxxxxxxxxxxxxxxxxxxxxx
A lot of questions here that would be probably best answered in a
newsgroup dedicated to SQL-Server such as
microsoft.public.sqlserver.client or mps.connect or mps.security or
mps.setup or mps.server.

Second, the traffic is not encrypted by default. To encrypt it, you must
install a certificate on the server and use SSL. It's also an all or
nothing solution: you cannot a mixed environment with some connection
encrypted and others not. See:

http://support.microsoft.com/kb/316898/en-us

For your post mapping, you can create an Alias using the SQL-Server
Client Network Utility or use a comma to specify the port number as in
a.b.c.d,1433.

For using two different ports (one external, one internal) using port
mapping on the firewall, I don't know. You will have to make your own
tests. Usually, this is done by using and configuring a proxy (see
http://support.microsoft.com/kb/216415 ) but if you can configure your
server, then you don't have to do this as you can easily configure the
server to listen to another port than 1433 by editing the Server Network
Utility, see
http://msdn2.microsoft.com/en-us/library/aa197974(SQL.80).aspx for
SQL-Server 2000.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Marcus" <marcus@xxxxxxxxxxxxxxxxxx> wrote in message
news:KPidnVjmpaEMs4rVnZ2dnUVZ_gWdnZ2d@xxxxxxxxxxxxxxxx
Hi there I'd wish to test my adp application (Access 2003, working
allready on LAN) to access the MS SQL Server 2000 throught the internet.
Questions:

1) If, for security reasons, I'd like to expose a different port on the
internet side I would let the firewall do the port mapping somothing
like route all traffic from port 5555 to internal IP a.b.c.d:1433.

2) What syntax should I use in my app? (Should I set the "globalServer"
variable to "w.x.y.z:5555" where w.x.y.z is the internet ip address of
the router?

3) Is the traffic encrypted?

Thanks

This is the current code:
**********************************************************
Public Sub enableDBConnection(dbName As String)

globalDatabase = dbName

strConnect = "PROVIDER=SQLOLEDB.1;" & _
"INITIAL CATALOG=" & globalDatabase & ";" & _
"DATA SOURCE=" & globalServer & ";Persist Security
Info=True"

Select Case globalServer
Case "MyMSWindowsServerName"
strConnect = strConnect & ";USER ID=myUser;PASSWORD=myPassword;"
Case Else
strConnect = strConnect & ";INTEGRATED SECURITY=SSPI"
End Select

CurrentProject.OpenConnection (strConnect)

If globalCn.State = adStateOpen Then
globalCn.Close
End If

globalCn.CursorLocation = adUseClient
globalCn.Open strConnect
globalStrConnect = strConnect

End Sub
**********************************************************






.



Relevant Pages

  • RE: RRAS Port configuration
    ... it conencts to the internet via PPPoE and does not get issued an IP ... The server obviously gives it one of those random 169 addresses. ... I am not quite sure about your word "open port 40010". ... SBS clients or server need to access port 40010 on the internet? ...
    (microsoft.public.windows.server.sbs)
  • RE: VBscript Error on SBS2k3
    ... DHCP Server turned of SonicWALL with VPN Pass through request for IP to ... the problem should be caused by the 4125 port. ... > | Accessories and Communications and Remote Desktop Connection? ... > | 2.In Internet Explorer on the workstation you are connecting from, ...
    (microsoft.public.windows.server.sbs)
  • Re: Public Static IP Routing
    ... Right-click on your external / internet nic & select Properties. ... Surveillance), set the incoming port to 1024, the private address to ... Chad A. Gross - SBS MVP ... >> Surveillance server is already being used by something else on your ...
    (microsoft.public.windows.server.sbs)
  • RE: RRAS Port configuration
    ... I am not quite sure about your word "open port 40010". ... SBS clients or server need to access port 40010 on the internet? ...
    (microsoft.public.windows.server.sbs)
  • RE: Remote Web Workplace
    ... 825763 How to configure Internet access in Windows Small Business Server ... Port 21 enable external and internal file transfer ... Port 80 enables all nonsecure browser access, ...
    (microsoft.public.windows.server.sbs)