vpn or dsn

[Robert Blackwell]

We have an employee in South Carolina that does shipping for my company.
They operate out of thier home and ship using FedEx software. The shipping
system can import and export shipping data through ODBC. I work out of
California with our actual office and we use an msacces db for our ordering
system. We also ship FedEx out of CA using the same software but since our
shipping computer is connected to our LAN I can create a system DSN to our
main db no problem and with no real risk of compromising data since our db
can't be accessed from the outside.

What I want to do is allow the computer in SC to export shipping data to our
computers.... which is going to be shipping address, package
details/tracking numbers. I've thought of a couple of ways 1 way seems
pretty secure to me but requires more steps, the second way only requires
one step but is less secure.

Option 1: SC creates a DSN to a (my)sql server that runs at my office. Then,
take that data and update it into our main access db. Alternatively, I could
create a website to display this information rather then importing it to our
db but my goal is to have as much info available in our main db to minimize
time looking for stuff.

Option 2: our router supports IPsec/PPTP so I could create a vpn and map a
network drive to our access db so they could make a DSN and export directly
to the db.

At first I was more concerned at option 2 because of the fact that its
connecting directly to our main db, and with that DSN you gain access to all
of our customers info including thousands of credit cards (credit card data
is not encrypted in the db, if it was I would be concerned still but not as
much). Now though, I'm realizing that the SC computer runs Windows XP Home
edition which doesn't support IIS so I don't believe it would be possible
for an outsider to get access to this DSN without them installing and
customizing apache server...which I know they wouldn't even know where to
begin.

Please let me know what you guys think.

 

[Ron2005]

Perhaps a simplistic way of doing it, but

1) Pull the shipping data table out of your primary db and put it in
its own.

2) Give them an application that loads the data into that table. (they
would never see the other tables)

3) Change the links in your other apps to get the shipping info from
the new db.

Just a thought to generate other thouths.

Ron

 

[Robert Blackwell]

Okay, basically you are suggesting what I described in Option 1....

Create a dsn for a DB that only has shipment data, then once we have that
info, import into our main db.

 

[Albert D.Kallal]

Option 2: our router supports IPsec/PPTP so I could create a vpn and map a
network drive to our access db so they could make a DSN and export
directly
to the db.

You can't do the above due to performance reasons..and if you fix the
performance, then you can't do the above due to reliability reasons.

I explain WHY a mdb file will corrupt with a serous of diagrams in the
following article of mine:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

The above article assumes you split your database...I explain that here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Ron2005]

I am probably not seeing the complete picture here, but if the the
shipment data is in that separate table/db, there is no need for
"importing" the data, it would simply be linked to by whatever programs
needed to use it. No Import involved.

I am assuming in all cases here that the application has already been
split: ie all the tables in one db and the forms etc in a separate mdb
that each user has a copy of. My suggestion, for security/privacy sake
is that you end up with 3 mdbs. 1) mdb with all tables except shipement
2) shipment mdb 3) application mdb.

That way network rights can help control whether the remote site can
see the private information.

Ron

 

[Robert Blackwell]

Albert, I read your article. It certainly brings up some issues I didn't
think about regarding the speed.. but coming from a web authoring
background, I had a differnet perspective and was already thinking (in
option 1) of what you wrote later in your article which was about
replication/synching through mysql or sql server. How would my approach
differ from me creating a website that uses ODBC. I mean for speed dsnless
seems to be the way to go but I've built some asp sites that use DSNs and
they have worked just fine. And I know on a website the scripts are executed
server side, but we're not talkin hordes of data... perhaps only 20-50
records or so a day. I would still be able to create a DSN to connect to the
sql server. Now, if I do go the sql server route, I dont need the vpn
because I can create a dsn directly to the server. The only reason I
considered the vpn was to create a dsn to an msaccess source.

I guess I'm kind of ruling out my 2nd option now because I definitely would
not want to jeapordize that db through a shakey vpn but also because the dsn
would have free access to all records in that db.

Now, I'm not trying to try and make my reasoning sound any better, I'm still
trying to see if its not only viable but secure, with emphasis on secure.
For example, the SC computer uses a single pc on XP Home and since home
doesn't have IIS I don't see any potential security risk... additionally, it
takes a nicely customize apache install to use DSN and I know they wouldn't
have a clue on how to set that up. Are there any other vulnerabilities that
might allow somone to access the DSN on that computer remotely?

So, again, here's the scenario:
The SC computer would export shipment data once at the end of each day
(20-50 records or so). Once the data is received on our computers... I had
intended to update that info into our main db.

I'm still open to all suggestions at this point, this is still R&D for me
and I'm not in any particular hurry.

 

[Albert D.Kallal]

I am probably not seeing the complete picture here, but if the the
shipment data is in that separate table/db, there is no need for
"importing" the data, it would simply be linked to by whatever programs
needed to use it. No Import involved.

yes, but typically VPN's over the internet are subject to minor, and
temporary breaks. You might have missed the transporter metaphor I used in
that article..but your solution does not deal with what happens when you
have a temporary dis-connect...and parts of the file don't make the trip
back to the mdb file?

That way network rights can help control whether the remote site can
see the private information.

They might get away with read only access. And, the issue still remains that
of a good high speed internet connection is still 100 times slower then your
crappie cheap office network....

 

[Robert Blackwell]

So what if I scrap VPN idea and just create a DSN to an SQL express server
that I host. Realistically end of day would export 20-25 a day but maybe 50
at most on a busy day. With an SQL server there isn't going to be a
corruption problem as you documented with the MDB so that problems
eliminated. The only real issue I see now is the potential for disconnects
and I'm still not saying I *know* but I'd feel comfortable betting that the
connection would be solid enough for that data to update successfully.

What do you think?

 

[Albert D.Kallal]

So what if I scrap VPN idea and just create a DSN to an SQL express server
that I host.

in fact, setting up a VPN, and using that to connect to sql express is a
great idea. The VPN simply gives you a secure (hopefully) connection to that
network. I suppose you could also use a SSH tunnel also (often called a poor
mans VPN).

So, setting up the VPN is a GREAT idea...just do NOT open a file share
*across* this VPN..as it will not be reliable.

So, you can continue to use a DSN'less connection to sql server with
ms-access (and, I suggest you do this..as this eliminates having to setup a
DSN on each pc..that is just pain anyway).

So, sure...setup a VPN...once you done that...then the clients can connect
to sql server. It is just the file share that we need to eliminate over a
flakey connection....With using sql sever...the connection can be dropped,
and while the data might not make it...parts of the physical file are NEVER
transferred over the network,...and thus a break in the connection will NOT
damage the data file (in this case the sql server data files).

I'd feel comfortable betting that the connection would be solid enough for
that data to update successfully.

What do you think?

Excellent ......yes...the minor breaks in conneciton are not going to be any
probem if you use sql server........

It is the file being used OVER the connecton we need to elimonate.

 

[Robert Blackwell]

Sounds like I got a plan then. Thanks for the feed back and the good
articles you wrote.