The Replicator Service interacts with SQL Server security for these purposes:
To read from or write to Data Replicator control tables
To read from a SQL Server object that is a replication source
To write to a SQL Server object that is a replication destination
In each of these situations, the Replicator Service must log in to SQL Server using a valid login and password. The Security properties of the SQL Server and of the DSN affect which login and password will be used and how they will be validated. SQL Server can operate in one of two security modes: Windows Only authentication and SQL Server and Windows (Mixed Mode) authentication.
Be sure the authentication method selected for a SQL Server DSN matches how the SQL Server is configured. Data Replicator uses the DSN to determine which authentication method is required by a particular SQL Server.
When the Replicator Service attempts to log in to a SQL Server that is using Windows Only authentication, SQL Server uses the current Windows login. If the Replicator Service is set to log on as System Account, the SQL Server uses the built-in sa SQL Server account. As with all SQL Server logins, any Windows login used for the Replicator Service under the Windows Only authentication mode must have appropriate permissions for the operations the Replicator Service will perform.
When Data Replicator attempts to log in to a SQL Server that is using SQL Server and Windows authentication, also known as Mixed Mode, the user ID and password that the SQL Server checks depends on the kind of connection being established and on the authentication mode assigned to the user ID. Data Replicator supplies different user IDs for different types of connections:
For a control database connection, Data Replicator supplies the login and password set by the Data Replicator Configuration program.
For a source connection, Data Replicator supplies the login and password set in the Replicator Manager Source Properties dialog for the source associated with the current subscription.
For a destination connection, Data Replicator supplies the login and password set in the Replicator Manager Destination Properties dialog for the destination associated with the current subscription.
Refer to your SQL Server documentation for more information about managing security levels and authentication modes.