SQL 2005 Peer-To-Peer Replication Cross Domain Impersonation Fails

Question

Hi,  I have 2 nodes running SQL 2005 SP2 participating in a peer-to-peer replication topology.  Each node runs on a different domain but has the same SQL Server Service and Agent account names and passwords, both of which have been granted logon rights and are sysadmin accounts in SQL.  They are also both domain admin accounts on Windows Server 2003.  Only the domain name is different.

We are using VPN to set-up the connection between the two non-trusted domains, and I have already ensured name resolution, and ping tests work.  I can also view both nodes on Enterprise Manager.

I tried in setting up my topology to use "Impersonate SQL Agent Account" in all snapshot, logreader, and distributor agents, but once I started up Replication Monitor, I kept getting the error "The process could not connect to Subscriber 'XXX'".  The only way I seem to be able to fix this is to edit the Subscriber connection properties for each subscription to use a SQL account login instead.  I would like to avoid SQL authentication if possible.  What am I doing wrong?

Answer 1

did ur servers have network teaming.

Also can u please check if u are able to connect Server A and Server B (vise versa) using SQLServer agent service account.

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Answer 2

What kind of account is the SQL Agent Service running under?  If it's a "domain" account, it will not work, it has to be a "windows" account that exists on both domains.  See topic Replication Security Best Practices for more info.

Answer 3

Thanks Greg,

I was using domain accounts.  If I create windows accounts instead, do I have to change SQL Agent Service to run as that account too, or will the agents have appropriate access to run the jobs, assuming that I've granted them appropriate rights as defined in the link above.  Also, when configuring topology, do I have to add the account via "machine_name\user_name" format, or do I just add "username", or does it matter?  Do I still leave the other connections in as the default "Impersonate SQL Process Account" settings, or do I have to redefine those as well?  Finally, should I still choose "Use the first peer’s security settings for all other peers", or should I specify each node separately?

Answer 4

Thanks Mohammad for your input.  I've already checked that both nodes can see each other at the Windows Level and at the SQL Server Level.  Greg was right about the domain account issue.  I will follow that advice.

Answer 5

Thanks Greg,

I was using domain accounts.  If I create windows accounts instead, do I have to change SQL Agent Service to run as that account too, or will the agents have appropriate access to run the jobs, assuming that I've granted them appropriate rights as defined in the link above.  Also, when configuring topology, do I have to add the account via "machine_name\user_name" format, or do I just add "username", or does it matter?  Do I still leave the other connections in as the default "Impersonate SQL Process Account" settings, or do I have to redefine those as well?  Finally, should I still choose "Use the first peer’s security settings for all other peers", or should I specify each node separately?

Hi Diane, I have the same problem as yours. Can you explain how you solve this? Thank you.