Security Considerations
When considering security in Microsoft® SQL Server™ 2000, replication is similar to other applications in SQL Server 2000. Your determining factors will be a balance between how secure the data needs to be, and how accessible the data needs to be for your environment.
Additional security issues need to be considered in the following areas:
- SQL Server Agent.
- Location of snapshot files.
- Testing agent connectivity.
- Security mode of the Publisher.
SQL Server Agent
The SQL Server Agent service (SQLServerAgent) at the client should not use the LocalSystem account. It needs to use a standard domain account. The SQLAgent account is the security context under which the Snapshot Agent, Merge Agent, and Distribution Agent are running by default.
The account used by the SQL Server Agent is defined at the time SQL Server 2000 is installed and can be changed at any time.
On the Microsoft Windows® 98 operating system, SQL Server Agent and the replication agents run under the security account of the user logging on to the Windows operating system. On Microsoft Windows NT® version 4.0 and Microsoft Windows 2000 operating systems, the replication agents run under the login or security context of the SQLServerAgent service. Neither the SQLServerAgent service nor the SQL Server service needs to run under a Windows 2000 Administrator account.
Each agent connects to one or more servers (Publisher, Distributor, or Subscribers depending on the agent) and must have a valid login to that instance of SQL Server to complete the connection. For more information, see Agent Login Security.
Location of Snapshot Files
The folder in which the snapshots are stored must be available to all Subscribers on the network. To ensure secure access to the initial snapshot files of your replicated data, it is recommended you use an explicit share instead of an administration share (for example, C$) for which you cannot grant specific permissions. The administrative share is used as a default only because it will always exist on Windows NT 4.0 and Windows 2000 (but it cannot be accessed except by an administrator account).
When configuring distribution, you can define the default location for all snapshot files. After creating a publication, you can define the location of the snapshot files using the publication properties dialog box.
Testing Agent Connectivity
When implementing replication, make sure that the replication agents can communicate with all servers involved in the replication topology. One way to test agent connectivity is to log in to the required server and database using SQL Query Analyzer or osql using the same login that the replication agent will be using (or typically the login that SQL Server Agent is using).
You must be a SQL Server 2000 system administrator to enable the server for replication. After replication is enabled, you do not need to be a SQL Server 2000 system administrator to set up publications and subscriptions, or to invoke or schedule the replication agents. You must be in the db_owner role to create publications. Anyone who is added to the publication access list (PAL) can create pull subscriptions to that publication (but only to that publication).
Security Mode of the Publisher
Connections to a server (Publisher, Distributor, or Subscribers) can use Windows Authentication or SQL Server security. Windows Authentication is generally preferred for greater security and ease of use; however, connections to Windows 98 servers must use SQL Server security because Windows Authentication is a feature only on Windows NT 4.0 and Windows 2000.
It is recommended that the Subscriber connection have dbo permissions in the subscription database to make sure the proper permissions are granted, and for overall simplification; however, dbo permissions are not required.