I'm trying to set up merge replication between a SQL Server 2000 SP4 / Win2k box and a MSDE SP4/WinXP box and I'm finding that the initial snapshot isn't being produced.
The SQL Agent runs as a windows user that has admin privileges, the snapshot agent impersonates the SQL Agent. The SQL Server login for the agent account has the server role "System Administrators" and database access to all db's.
When I turn on logging and try to start the agent, the following is written to the log:
Microsoft SQL Server Snapshot Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SVRName-POS-POS_Test_Suburb-1
Connecting to Distributor 'SVRName'
SourceTypeId = 5
SourceName = SVRName
ErrorCode = 18456
ErrorText = Login failed for user 'SVRName\SQLAgent'.
The process could not connect to Distributor 'SVRName'.
Most of the posts I've seen regarding problems like this are due to subscriber authentication issues (not up to that, this is the initial snapshot) or windows/domain user permissions.
I tried to run the snapshot agent from the command line and received the same failure message except this time it was the user Administrator being disallowed access.
This system usually uses SQL Server auth for logins which is why the following odd alteration wasn't evident during normal operation: Someone had denied access to the BUILTIN\Administrators group.
The problem's now gone but this does raise a question. If the BUILTIN\Administrators group had been denied access and this stopped the snapshot agent authenticating when it posed as the agent, how does the agent work normally?
I realise that I authenticate it when I start the Enterprise manager by entering the sa password but doesn't the agent runs at other times too?
Last edited by gvee; 03-12-08 at 20:40.
Reason: edited post title