Replication and User Level Security

[Guest]

I've set-up user level security on my database. Now, I want to make a replica
and email that replica to another user (who I've set permissions for.) Will
the security settings be transferred in the replica or do I have to do
something else (like separately deliver the security file).
The security newsgroup keeps sending me to the replication newsgroup but I
can't seem to find it?
Thanks,
CC

 

[Larry Daugherty]

it's microsoft.public.access.replication

 

[Guest]

Reposting to the replication newsgroup. Thanks. - CC

 

[David W. Fenton]

Emailing replicas is a one-way operation. If you are simply
initializing a remote location, it's OK to email out the replica and
then have them save it to its permanent location and then begin
editing. But you can't then turn around and email the replica back
to the mother ship for synchronizing, as you'll then end up with
loss of replicas in your replica set, which can lead to corruption
and loss of the replica set if any replication errors should develop
in the future. It also means your replica set is carrying a lot of
dead replicas.

It's not the way Jet replication was designed to work, so emailing
replicas is just a really bad idea as a method for regular
synchronization.

If, on the other hand, you're just initializing a remote location,
there shouldn't be an issue.

As to security, the remote sight needs the same system.mdw that
you've used to secure it locally. They can either set it as their
default system.mdw using the Jet Workgroup Administrator or they can
indicate the desired system.mdw with the /wrkgrp commandline switch
in the shortcut they use to start up the database. Be aware that you
have to supply the full path to MS Access executable for this to
work -- you can't just use the MDB file name.

All replicas will have the same security settings as the design
master they were created from.

 

[Guest]

Thanks for your reply. For better or worse, I've been emailing my replica for
editing and haven't seen any problems. Does your response mean that they are
some how accumulating in the background and I'm working with a flawed
database? Or would I know? Could I limp along like this as I have no other
solution at the moment for working with my remote users? As long as I'm not
compromising the master, I can live with a little data loss from a replica,
if it happens. Please let me know as your email has me concerned.

I'll try your suggestion re: the mdw file.

Thanks again,

CC

 

[David W. Fenton]

For better or worse, I've been emailing my replica for
editing and haven't seen any problems. Does your response mean
that they are some how accumulating in the background and I'm
working with a flawed database? Or would I know? Could I limp
along like this as I have no other solution at the moment for
working with my remote users? As long as I'm not compromising the
master, I can live with a little data loss from a replica, if it
happens. Please let me know as your email has me concerned.

You're creating dead replicas. As I explained it in a recent post:

A dead replica is a replica that no longer exists.

Here's how to create one:

1. create a replica. Say it's ReplicaID is 10.

2. email that replica to someone. They save the replica and use it.
As soon as they open it, its ReplicaID is changed to 11.

3. they email back a replica. when you open it, its ReplicaID is
changed to 12.

Now, keep in mind that from *your* point of view, there's one
replica here. Assuming you didn't delete the replica created in step
1, you have still have Replica 10. If you copy Replia 12 over top of
it, you've lost Replica 10 entirely -- it no longer exists.

Yet, if you look in the MSysReplicas table, there's still a line
there for each of these three replicas.

4. Now, if you synch Replica 12 with your home replica, your base
replica (call it Replica 1) now knows about Replia 11 and 12 in
addition to Replica 10 (which actually doesn't exist any longer).

5 So, email Replica 12 back, and your remote user saves it and opens
it. It now is changed to have ReplicaID 13, but it's been copied
over top of Replica 11, which now no longer exists.

So you have now lost Replica 10 and Replica 11.

6. If it is then emailed back to you and you save it over top of
Replica 12, you've now lost 10, 11 and 12, and when you open it, it
will be number Replica 14.

And so on and so forth.

Each email cycle casues you to create two dead replicas.

If there is some kind of problem in a synchronization that involves
the data in one of these dead replicas, you may be completely unable
to rectify that synchronization error until you clean up your
replica set and get rid of the dead replicas.

You can delete a replica from a replica set by attempting to synch
with it. This obviously won't work as long as there's a file with
the same name in the same location (regardless of what the ReplicaID
is). So, to delete a dead replica, rename the replica currently
occupying the dead replica's former location, try to synch with the
dead replica, and it you'll be informed that it's been deleted from
the replica set. Now, rename the replica back to the original name.

But when you llok, you'll see that the dead replica is still there
in the list of replicas.

You need to then synch around the full replica set.

Likewise, if you've had 10 dead replicas in the same location, you
need to attempt to synch with it 10 times before all 10 will be
deleted, and you may need to synch around the replica set several
times before the replica disappears from the list of replicas.

And if you still need a replica with that name in that location, it
can become even more difficult.

I think you get the point: dead replicas potentially screw up your
synchronization success and they are casued by copying or emailing
replicas. They are *very* hard to clean up once they've been
created, so it's important to avoid them in the first place.

 

[Guest]

David, Thanks so much for the detailed response. It made things a lot
clearer, believe it or not! Ok. So, what do I do? If I have to share a
database with someone remotely but not connected to a network, how do I do
it? We both need to be updating it at the same time.

And to clean up all the dead replicas (I'm up to 46), is there any other
way? I only have about 3 tables I'm working with. Could I start anew by
exporting the data and reimporting into a brand new db and do that on a
routine basis or something?

Thanks for your help. It's much appreciated.

CC

 

[David W. Fenton]

David, Thanks so much for the detailed response. It made things a
lot clearer, believe it or not! Ok. So, what do I do? If I have to
share a database with someone remotely but not connected to a
network, how do I do it? We both need to be updating it at the
same time.

Well, you need to figure out a way for the remote user to be able to
connect to your network occasionally.

If you have Internet access on both ends, it can be done via VPN
over Internet, which can be set up PC-to-PC pretty easily, using the
built-in Windows VPN client/server. Obviously, if the Internet
access in home office is always on, it makes it easier for the
remote user, as she doesn't have to call the home office and ask
somebody to connect to the Internet so she can synch, but it's still
doable in those circumstances.

If that's not possible, then you can set up dialup networking to
allow a remote user to dial into one of your PCs. That requires
either a dedicated phone line in the home office or distinctive ring
(for, e.g., using the fax line for the dialin).

And to clean up all the dead replicas (I'm up to 46), is there any
other way? . . .

Nope. You just have to attempt a synch with all the dead replicas
when they aren't there. It's a pain, yes.

. . . I only have about 3 tables I'm working with. Could I
start anew by exporting the data and reimporting into a brand new
db and do that on a routine basis or something?

Well, if you can get all the data synched, you could start the
replica set over again, but I wouldn't do that until I had a proper
infrastructure in place for doing replication correctly.

 

[Guest]

Again, great answer. Thanks. If I set up a VPN over the Internet, is that
secure? Is my data safe? I do have customer information in there. Not that
email was safe but I"m looking for a secure means to transfer the data
between us. Thanks. CC

 

[David W. Fenton]

If I set up a VPN over the Internet, is that
secure? Is my data safe? I do have customer information in there.
Not that email was safe but I"m looking for a secure means to
transfer the data between us.

Well, depends on what you're using for your VPN. If you use the
built-in Windows VPN client/server, then by default it's *not*
secure. You probably need to implemente IPSEC security on the
connection, and this is not completely trivial. But as long as you
have both ends of the VPN configured exactly the same way, it ought
to work.

If you're using some other kind of VPN, it's like secure by default,
since that's the usual justification for using something other than
the Microsoft VPN infrastructure.

 

[Jim Andersen]

Well, depends on what you're using for your VPN.

Hi David,

Have read a LOT of your postings lately
And it seems indirect synch via VPN is the thing to do. I know (knew)
nothing about VPN.

I have found a great link (for a complete newbie) on how to set up the VPN
client
http://www.windowsecurity.com/articles/Configure-VPN-Connection-Windows-XP.html

Do you have a link on how to set up the server, so the clients can connect
to it ?
A standard WinXP PC should be able to "play" server, right ?

If you would look at step 10 in the link.. "Type the host name or the
Internet Protocol (IP) address of the computer that you want to connect to,"
Do I (the server) need to buy a fixed IP ? I am in no way a IP specialist,
but when I start my PC and connect (via ADSL) to my ISP I get a different IP
each time. How would a laptop know how to reach me ?

Should I put up a webpage the laptop users should visit first with a "todays
IP is: " message ?

thx,
/jim

 

[David W. Fenton]

Hi David,

Have read a LOT of your postings lately
And it seems indirect synch via VPN is the thing to do. I know
(knew) nothing about VPN.

I have found a great link (for a complete newbie) on how to set up
the VPN client
http://www.windowsecurity.com/articles/Configure-VPN-Connection- Win
dows-XP.html

Do you have a link on how to set up the server, so the clients can
connect to it ?
A standard WinXP PC should be able to "play" server, right ?

This link explains it:

http://www.onecomputerguy.com/networking/xp_vpn_server.htm

On Win2K it's slightly different, though. The first dialog does not
have the choice of "Set up an advanced connection" -- instead it has
"Accept incoming connections" (what you want for the VPN) and
"Connect to another computer" (for serial/parallel cable
connections).

If you would look at step 10 in the link.. "Type the host name or
the Internet Protocol (IP) address of the computer that you want
to connect to," Do I (the server) need to buy a fixed IP ? I am in
no way a IP specialist, but when I start my PC and connect (via
ADSL) to my ISP I get a different IP each time. How would a laptop
know how to reach me ?

If you can't buy a fixed IP address (many ISPs sharge only $5/month
or so for that, but some are not configured for that unless you
purchase much more expensive service) you can subscribe to a dynamic
DNS service. The way that works is your PC runs a program that
checks its IP address and sends it to the dynamic DNS service. They
then map a host name of yoir choice as a subdomain of their top
domain. So, if your dymanic DNS service was DynDNS.net, your server
would be named something like grommit.dyndns.net. DynDNS.net will do
what's necessary with their name server to resolve that server name
to the current IP address.

Should I put up a webpage the laptop users should visit first with
a "todays IP is: " message ?

That's doable if you have some way of getting the IP address
automatically to a web page, but the dynamic DNS server is going to
work much more smoothly in the long run.

 

[Jim Andersen]

This link explains it:

http://www.onecomputerguy.com/networking/xp_vpn_server.htm

Great !

you can subscribe to a dynamic
DNS service.

Just the thing I was looking/hoping for.

thx for all the help,
/jim

 

[Guest]

This has been helpful for me as well. Can I get purchase a static IP from MSN?

Thanks,

CC

 

[David W. Fenton]

This has been helpful for me as well. Can I get purchase a static
IP from MSN?

Static IP addresses generally make sense on a broadband connection.
If MSN provides broadband, I'm unaware of it.

In any case, it's often the case with many providers that you can
only buy a fixed IP address if you have a business-class account,
which usually costs substantially more than the consumer-class
service (and is often much slower than the consumer offering).

This is why a dynamic DNS service comes in handy, because it allows
you to be found by host name instead of by IP address.