Securing database-to-database communications - Implementing Database Security and Auditing - page 249

Figure 8.8 shows the SQL Server snapshot replication model. The snap-

Figure 8.8
SQL Server
snapshot
replication.

shot agent runs as part of the distributor's SQL Server Agent and attaches to
the master database (the publisher) to create a schema and data files. It
records synchronization information in the distribution database and places
the data within the snapshot folder. The distribution agent runs as part of
the distributor when using push subscription (as shown in Figure 8.8). It
uses the information in the distribution database to decide where the data
needs to be replicated to and communicates with the subscriber to finish
the replication. If you use pull subscription, then the distribution agent will
be running on the subscriber.

8.5.2 Secure replication files and folders

There are numerous aspects to securing replication. When your replication
scheme involves the creation of files, you must secure the folder where repli-
cation files are stored. For example, when you set up the snapshot agent and
the distribution agent in SQL Server, you specify which folder to use, as
shown in Figure 8.9. This is a network share, and by default it is an insecure
folder. You should change the share path and configure NTFS permissions
so that only the SQL Server Agent services on your SQL Server nodes can
access and modify this folder. In addition, you might want to consider
using Windows 2000 EFS to encrypt these replication files.
These security guidelines should be followed for all types of replication
within all database environments on all operating systems—with the appro-
priate adaptations.
Not every scheme uses external files. For example, in Oracle all replica-
tion schemes use internal queues within the database, eliminating the need
for you to worry about the security at a file system level. Figure 8.10 shows