Transaction replication: Distribution agent owner

Hello!

I seem to have a permission issue when i setup a pull transaction replication on SQL Server 2005 which i was hoping someone could explain. The distribution agent gets created but when it tries to run it fails with:

The job failed. The owner (NATION\PK0159_a) of job EPPINF002\YUKON1-Publisher-Person-EPPINF004\YUKON1-Subscription-852824CC-803A-4C6B-B44B-748B6E25BFFC does not have server access

However, when i change the owner of the distrbution agent job on the subscriber server to sa and manually execute the job, the distribution agent executes successfully. NATION\PK0159_a is what i am authenticated when i am setting up replication and this account is a local admin in all the servers. Each of the servers also have the BUILTIN\Administrators account.

The Publisher, Distributor and Subscriber are all on different servers with SQLService and Agent services being run on the same domain account. This domain account is also a memeber of local administrator on each of the 3 servers.

If i have setup the distribution agent with the following details, why am i set as the owner of the job and why does it fail

Distribution Agent Security

1. Sepecify the domain or machine account under which the Distribution agent process will run when synchronising this subscription : Run under the SQL Server Agent Service Account

2. Connection to Distributor : By impersonating the process account

3. Connect to the subscriber : By impersonating the process account

Any explanation is very much appreciated.

Cheers,
Priyanga

Answer this question

Transaction replication: Distribution agent owner

NatalieE
On the distributor Nation\pk0159_a is a local administrator and the builtin administratot account exists. As this is a pull subscription, when the subscriber connects to the distributor using nation\pk0159_a, it should have access to the instance.

However as i mentioned in my first post , this job works when sa is made the owner of the job on the subscriber. I am also curious as to why Nation\pk0159_a is made the owner of the job when i specifically say to use the SQL Server Agent Service account for all agent authentication.

Thanks,
Priyanga
briane
Not sure about why nation\pk0159_a doesnt have access on the distributor.
However sine you specifically said: use the SQL Server Agent Service account when setting up the subscription, the job owner becomes: nation\pk0159_a since you mentioned that the service is running under this context.

On a sidenote, are the distributor, publisher and the subscriber on the same domain and this nation\pk0159_a account is in the same domain
ajb2528

This may be because there is no SQL Authentication login for account NATION\OK0159_a on the server. Can you check that, and add a login if there isn't one. Then let us know whether the agent runs correctly.

Thanks,

Phil Garding
Simon Gorski
The publisher, distributor and subscriber are all running in the same domain and pk0159_a is an account within this domain.

I will keep looking into the issue.

Thanks,
Priyanga
Transaction replication: Distribution agent owner

Answer this question