How To Use Transactional Replication Across Internet?

Posted on 2014-08-05
Last Modified: 2015-01-28
I am currently experiencing issues with attempting to create a snapshot transaction replication across two servers on different machines that are not on a domain at all.

Server A - Distributor:
Computer Name: WIN-91KB167NSAK
Operating System  - Windows Server 2008
SQL Server - 2008
SSMS - 2012
Firewall off
All SQL server services running.

Server B - Subscriber:
Computer Name: TEST-2012
Operating System  - Windows Server 2012
SQL Server - 2012
SSMS - 2012
Firewall off
All SQL server services running.

I originally began by following this guide by Microsoft for creating transactional replication across two SQL Servers.

Everything was going smoothly until I reached the point of viewing the Synchronization Status, since then it has been a nightmare of attempting to follow breadcrumbs to try and get around the issue.

The latest solution I attempted was to connect through SQL Authentication for each step of the transaction login requirements, this fell upon

Date		05/08/2014 13:05:25
Log		Job History (WIN-91KB167NSAK-Northwind-Northweeend-TEST-2012-43)

Step ID		2
Server		WIN-91KB167NSAK
Job Name		WIN-91KB167NSAK-Northwind-Northweeend-TEST-2012-43
Step Name		Run agent.
Duration		01:21:15
Sql Severity	0
Sql Message ID	0
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

2014-08-05 13:26:32.789 Copyright (c) 2008 Microsoft Corporation
2014-08-05 13:26:32.789 Microsoft SQL Server Replication Agent: distrib
2014-08-05 13:26:32.789 
2014-08-05 13:26:32.789 The timestamps prepended to the output lines are expressed in terms of UTC time.
2014-08-05 13:26:32.789 User-specified agent parameter values:
			-Subscriber TEST-2012
			-SubscriberDB Northwind
			-Publisher WIN-91KB167NSAK
			-Distributor WIN-91KB167NSAK
			-DistributorSecurityMode 1
			-Publication Northweeend
			-PublisherDB Northwind
			-XJOBID 0xC58461878BB77D46AF089EBA55573246
			-XJOBNAME WIN-91KB167NSAK-Northwind-Northweeend-TEST-2012-43
			-XSUBSYSTEM Distribution
			-XCancelEventHandle 00000000000006B8
			-XParentProcessHandle 00000000000006C4
2014-08-05 13:26:32.789 Startup Delay: 7862 (msecs)
2014-08-05 13:26:40.664 Connecting to Distributor 'WIN-91KB167NSAK'
2014-08-05 13:26:40.664 Parameter values obtained from agent profile:
			-bcpbatchsize 2147473647
			-commitbatchsize 100
			-commitbatchthreshold 1000
			-historyverboselevel 1
			-keepalivemessageinterval 300
			-logintimeout 15
			-maxbcpthreads 1
			-maxdeliveredtransactions 0
			-pollinginterval 5000
			-querytimeout 1800
			-transactionsperhistory 100
2014-08-05 13:26:40.664 Connecting to Subscriber 'TEST-2012'
2014-08-05 13:26:40.696 Agent message code 20084. The process could not connect to Subscriber 'TEST-2012'.
2014-08-05 13:26:40.696 Category:NULL
Source:  Microsoft SQL Server Native Client 10.0
Number:  18456
Message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Open in new window

I have followed this to try and get a better understanding of the situation but the section I needed most if less than helpful and quite vague.

What would be the best solution to this problem, or is it likely just something stupid that I've overlooked in the process?

At this point the issue has become a net of complication for me so I may have overlooked important information to report, if any more details are required please ask.

Thanks for your time with helping on this issue.
Question by:Psychotext
    LVL 42

    Accepted Solution

    as per the MS link "4.Request a SQL Server login account to be created on the Subscriber with sufficient permissions in the Subscriber database"

    or just as test (if it is ok for your environment)  you can grant 'NT AUTHORITY\ANONYMOUS LOGON' sql server access on 'TEST-2012'
    LVL 2

    Author Comment

    Apologies for the very late reply, work required me to move on other items on my list and I'm now just re-visiting this.

    The problem with number ""4.Request a SQL Server login account to be created on the Subscriber with sufficient permissions in the Subscriber database"

    Is that it falls under the category
    "Example 1: The Subscriber Has Schema and Data, and the Initial Snapshot Is Not Necessary."

    Where in reality the solution I'm attempting to reach requires the initial snapshot. Which looks quite a bit more convoluted for the solutions applicable.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
    This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
    This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now