Adp to SQL 2000 Internet connection basics

[Marcus]

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

 

[Sylvain Lafontaine]

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.

 

[Marcus]

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.

You mean to set my variable (usually containing the LAN SQL server name) to
"a.b.c.d,1433" ??

For using two different ports (one external, one internal) using port
mapping on the firewall, I don't know.

No problem here, my router acting as the Internet gateway has some rules to
port-map and change port numbers. I've built a rule where requests routed to
Internet port 5555 are routed to LAN server on port 1443.
If the stntax is correct, for Internet connections I would set my variable
to "a.b.c.d,5555"

 

[Marcus]

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]

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.

 

[Marcus]

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.

Hi there Sylvain, while I have solved the connection problem using your
advise, I have noticed that stored procedures have some problems, that is
they don't execute at all when launched remotely... is there a particular
reason, in your opinion, why this happens? The soar while in LAN....

The error is:


"Run-time error '2812' Could not find stored procedure 'SPInc'.


here's the offending code:
***********************

spstring = "exec SPInc '" & _
fGDataFine & _
"' , '" & _
fGDataInizio & _
"'," & _
Me.Anno & "," & _
Me.Mese & "," & _
AnnoPrec & "," & _
MesePrec & "," & _
spOrderBy


Me.RecordSource = spstring
**************************

I

 

[Sylvain Lafontaine]

Sorry but I cannot see of any reason.

If you don't use the same account to access the sql-server remotely, then
maybe you simply have a permission problem here.