Sql-server – SQL 2005 replication not working

domainreplicationsql server

I am in the process of creating replication between 2 remote servers, server 1 is the distributor and publisher and server 2 is the subscription

  1. Server 1 windows 2003 server 192.168.10.1 connected by vpn SQL 2005 domain1.local

  2. server 2 windows 2003 server 192.168.10.1 connected by vpn SQL 2005 domain2.local

When i setup up the replication all looked fine until i looked at the sync status and is said :

The Agent could not be started
An exception occurred while executing a transact-sql statement or batch
sqlserveragent error request to run job server1-username blah blah blah

From user sa refused because the job is already running from a request by user sa
changed database context to technical error 22022

I have cleared jobs in the server agent as well as restarted the service.

Could this be something to do with authentication between two non trusted domains as I can browse and even control each sql server via SQL studio but just not setup replication?

I can't seem to locate what the problem is any help would be much appreciated.

Kraig

Best Answer

This is likely a permissions problem with the distribution agent trying to write the data to the remote subscriber but it can't.

Generally speaking the replication agents are simply applications external to SQL server: snapshot.exe, logread.exe, distrib.exe. A windows account is used to start each one of these applications and you can configure which account in the replication wizards (often this will be the SQL Agent account). The permissions required for each agent are roughly as follows:

  • Snapshot.exe - connect to the publisher, read publication database, write to the pre-determined snapshot location
  • Logread.exe - connect to the publisher and distributor, write to the distribution database,
  • Distrib.exe - read from the pre-determined snapshot location, read from the distribution database, write to the subscriber database

Some of the permissions required are access to SQL server and others are Windows folder level permissions to the snapshot location.

If you can include the full error message I can confirm and will edit my answer accordingly.

Related Topic