locked
SQL Server 2008 R2 Replication over untrusted domains RRS feed

  • Question

  •  

    Domain A: srvA.domA.local with Windows 2008 R2 and SQL Server 2008 R2 instance SRVASQLR2 with Windows Authentication

    Domain B: srvB.domB.local with Windows 2008 R2 and SQL Server 2008 R2 instance SRVBSQLR2 with Windows Authentication

    Domains are untrusted.

    Both SQL Servers are running named instances and a VPN connection has been established between Domain A and B. SQL Servers are using 'sqlService' user account name at each domain.

    Both firewalls are configured with the following: TCP Ports 135, 1433, 1434, 4022, 5551 and UDP 1434

    Working on domain A, successfully registered sql server on domain B using SRVB\SRVASQLR2.

    Created testdb on SRVASQLR2 and configured Publishing and Distribution using SRVASQLR2 as its own distributor.

    Created new Merge Publication on SRVASQLR2 called repl_testdb using testdb.

    On SRVBSQLR2, created new subscription using publisher on SRVA\SRVASQLR2 and using repl_testdb replication. Agent runs at its subscriber and using a subscription database repl_testdb. In the Merge Agent Security window, chose Run under SQL Server Agent service account and connecting to the publisher and distributor by impersonating the process account. Connecting to the subscriber defaults to impersonating the process account.

    So agent is running on subscriber and on-demand only schedule, and selecting immediate initialisation, with subscription type being set to client, both 'subscription creation' and 'starting the synchronization agent' steps were successful.

    However, right-clicking on the subscription on SRVBSQLR2, View Job History, there is only 1 entry in the log and expanding the entry shows the following:

    For step 1: The replication agent has been successfully started. See the Replication Monitor for more information.

    For step 2:

    • 2010-06-10 10:47:27.858 Connecting to Subscriber 'SRVB\SRVBSQLR2'
      2010-06-10 10:47:28.014 Connecting to Distributor 'SRVA\SRVASQLR2'
      2010-06-10 10:47:45.877 The process could not connect to Distributor 'SRVA\SRVASQLR2'.
      2010-06-10 10:47:45.893 Category:AGENT
      Source:  Merge Process
      Number:  20084
      Message: The process could not connect to Distributor 'SRVA\SRVASQLR2'.
      2010-06-10 10:47:45.893 Category:SQLSERVER
      Source:  SRVA\SRVASQLR2
      Number:  -1
      Message: SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
      2010-06-10 10:47:45.893 Category:SQLSERVER
      Source:  SRVA\SRVASQLR2
      Number:  -1
      Message: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
      2010-06-10 10:47:45.893 Category:SQLSERVER
      Source:  SRVA\SRVASQLR2
      Number:  0
      Message: Login timeout expired
      2010-06-10 10:47:45.893 Category:SQLSERVER
      Source:  SRVA\SRVASQLR2
      Number:  0
      Message: The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
      2010-06-10 10:47:45.893 The merge process will restart after waiting 30 second(s)...
      Connecting to Subscriber 'SRVB\SRVBSQLR2'
      2010-06-10 10:48:15.910 Connecting to Distributor 'SRVA\SRVASQLR2'
      2010-06-10 10:48:33.726 The process could not connect to Distributor 'SRVA\SRVASQLR2'.
      2010-06-10 10:48:33.757 Category:AGENT
      Source:  Merge Process
      Number:  20084
      Message: The process could not connect to Distributor 'SRVA\SRVASQLR2'.
      2010-06-10 10:48:33.773 Category:SQLSERVER
      Source:  SRVA\SRVASQLR2
      Number:  -1
      Message: SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
      2010-06-10 10:48:33.789 Category:SQLSERVER
      Source:  SRVA\SRVBSQLR2
      Number:  -1
      Message: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
      2010-06-10 10:48:33.804 Category:SQLSERVER
      Source:  SRVA\SRVASQLR2
      Number:  0
      Message: Login timeout expired
      2010-06-10 10:48:33.820 Category:SQLSERVER
      Source:  SRVA\SRVASQLR2
      Number:  0
      Message: The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

    No replication has occured as the newly created database repl_testdb on SRVBSQLR2 does not contain any tables...

    Does anyone have any ideas? Should I be using SQL Authentication? Help!!!

    Thursday, June 10, 2010 11:00 AM

Answers

  • Well, tried it with SQL Authentication and it worked beautifully... Thanks anyways. Seems like I should have persevered more instead of seeking help at the first hurdle... :)
    • Marked as answer by Jedimaster100 Saturday, June 12, 2010 3:06 AM
    Saturday, June 12, 2010 3:05 AM

All replies

  • Well, tried it with SQL Authentication and it worked beautifully... Thanks anyways. Seems like I should have persevered more instead of seeking help at the first hurdle... :)
    • Marked as answer by Jedimaster100 Saturday, June 12, 2010 3:06 AM
    Saturday, June 12, 2010 3:05 AM
  • Do you think it was because MS Windows doesn't use Kerberos pass through authentication?

    Support my SQL MCM & Internal Training blog on AliRazeghi.Com.  Have a tough I.T. job and you need to talk to someone with no obligation?  Contact my certified associates and I at Rosonco.com!

    If you find a question answered please click 'mark as answer' or 'vote as helpful'.  This will help other users find answers quickly.  Thanks for visiting!

    Thursday, June 2, 2011 8:09 PM
  • Hi, never posted to any forum before (never needed to always found my answer) but had to post this as it took me three days to figure this out, it may not help you but is one possible solution for the:

    'The process could not connect to Distributor' and/or 'The process could not connect to Subscriber' problem when trying SQL server Replication between untrusted domains.

    I am using SQL Server 2008 R2 on a 64bit machine.

    I have followed everything to the letter, same name and passwords for all accounts etc. the sql Agent account. I set all account as Administrator and using SQL Auth to connect for the subscription, but still no luck.

    Anyway to cut a long story short it was done to the alias' on the Distributor(Push), Subscriber(Pull) I had the defined under the 'SQL Native Client 10.0 Configuration (32bit)/Aliases' section in the Server Configuration Manager, these work when using the Server Management Studio (as it is a 32bit program) but the SQL Server looks for the alias in the 'SQL Native Client 10.0 Configuration/Aliases'. As soon as I created the server alias under the other section (aliases under both sections) the connection was made and the Replication sprung into life.

    Publication was created with 'SQL Server Agent service account' for snapshot and impersonating the process account for the Publisher and the Subscription 'SQL Server Agent service account' for the Distribution Agent process, and 'SQL Server Login' for the Connect to Subscriber.

    To get this working my SQL server logins had sysadmin rights, but could be locked down a bit, someting I can do now I have it all working

    Hope this helps...

    nick

    PS I am doing all this through a SSH tunnel and it is working fine only needed port 1433...

    • Edited by nick208 Thursday, January 19, 2012 9:50 PM missing text
    • Proposed as answer by nick208 Thursday, January 19, 2012 9:51 PM
    Thursday, January 19, 2012 8:13 PM