Replication trouble across domains (non-trusted)

I have struggled for weeks now with replication.

This is my setup:
Machine 1 (publisher+distributor):
Dell Server, brand new (single purpose DB server)
Windows 2000 Server (Stand alone)
SQL Server 2000 Std SP3a (running under special SQL Server account, not LocalSystem)

Machine 2 and 3 (subscribers):
Windows NT4 WS SP5 (not member of domains)
IE 5.5 SP2 (required by SQL Server)
MSDE 2000 SP3a (running under special SQL Server account, not LocalSystem)

I have attempted setting up merge and transactional replication without success. Every attempt seems to boil down to the snapshot not being applied. The clients inform that 'the initial snapshot could not be applied because it is not available yet.' If I run Snapshot Agent, I cannot browse the snapshot because it says it is 'not created or has been cleaned up.'

If I type SELECT working_directory FROM msdb.dbo.MSdistpublishers I get E:\SQLRepl\ and attempts to browse that directory using NET USE X: \\(Machine1)\SQLRepl - It says 'password incorrect, enter username:'. If I enter an existing username and password on Machine1 it logs on allright.

These are my requirements to replication (all three types will need to be set up):
2 tables (logging)
changes happen at the subscribers
one table is being written to twice per second (production parameter logging)
no special latency requirement (can sync every 5 mins to reduce network load)
changes normally happen on Machine2. Machine3 is an online backup of Machine2.

1 table (positioning system)
changes happen at the subscribers and publisher
long periods without activity, but must be replicated immediately when changes occur!
minimal latency (changes should be replicated <1 second)

1 table (system variables)
changes can happen at subscriber and publisher
changes rarely happen (maybe twice per year)
must be applied at Machine2 and Machine3 exactly at the same time - db consistent across replicas. Not necessary at Machine1 (publisher).

The machines are placed in a network without a DNS-server or a DC. Machine2 and 3 are exact copies of each other. The table in type3 holds the name of the machine that is the "active" machine - the one writing to the database. Machine1 is placed about 1 km away from the two others, but acceptable response times through wired ethernet. If network connection to Machine1 is lost, everything should carry on as normal, queueing transactions, and resuming (catching up) when network connection is back up.

I hope you have a good view of my mission critical system, with good suggestions on how to solve my snapshot distribution problems and how I should set up my replication properly.

PS! I am not currently by the machines, so error messages and directories are taken from memory and thus may be slightly misspelled. My appologies.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hows this one going ?

The clients inform that 'the initial snapshot could not be applied because it is not available yet.' If I run Snapshot Agent, I cannot browse the snapshot because it says it is 'not created or has been cleaned up.'
--> Have you created the initial snapshot ?
Or specified no snapshot needs to be created ?

Has the snapshot agent completed, have you tried running it manually ?
(there is a job for this, with a line containing  snapshot -<multiple params> depending on how you setup your system...refer to BOL on starting the snapshot agent.)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

risoyAuthor Commented:
The other thread is regarding the first test setup I did, which was on a trusted domain - the snapshot creation worked. So it is a different problem. What is not working with the other thread is the fact that if the system goes down for longer than the 10 times trial cycle, it won't restart.

The problem described above is on a non-trusted connection - across domains, or actually no domains. Running the snapshot agent manually returns success, but I am not able to 'browse the snapshot' using the appropriate button. Browsing the directory locall works though. But it does not work from the subscribers OS. Plus I am afraid the 'not yet created' message will appear when I get that bit working...

The publication wizard has ben set to create the snapshot.
But it does not work from the subscribers OS -- Due to no credentials being supplied/authorisation.
You have to allow the snapshot folder to be seen from the subscribers --
(Have you seen the article KB Number: 321822
Replicating across non trusted domains or no domains:;en-us;321822)

On the other thread -- I mentioned changing the job type to always run -- so you will not suffer from the problem of 10 times trial /resets as it will always be running as you are manually overloading the process...

You need to setup a FTP Site or Establish a VPN/security (such as Trusted remote domain) mechanism between the two sites to allow the remote site to see the snapshot.

Its down to you how you put this in place.
I believe I have given you answers to both of these particular problems that you have encountered.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
risoyAuthor Commented:
So FTP is the only solution when in a non-trusted environment. I hoped I could just share a folder. I will try this next time I am by the machines...

What is the disadvantage of "manually" creating the schema and data on the subscriber and then setting up replication? What happens when it stops working for a while?
I hoped I could just share a folder -- which you can do via a VPN or other security mechansim relationship to the snapshot folder.  Would you honestly want to share a folder publically containing company data ?

What is the disadvantage of "manually" creating the schema and data on the subscriber and then setting up replication? What happens when it stops working for a while?
--> New question.  Ask in a new question please, please refer to EE guidelines.
risoyAuthor Commented:
FTP is set up. But the REPL-Snapshot agent failes with the following error:

"The job failed. The job was invoked by User sa. The last setp to run was step 3 (Detect nonlogged agent shutdowm.)."
Step ID 2: "The process could not create the file 'E:\SQLRepl\ftp\SERVERNAME_DBNAME_DBNAME_REPLNAME\2004050614456'. The step failed.

I have run it a few times manually. Tried changing the user it is run as... Nothing helped.

This is a merge replication.

The FTP connection test passed.
Does the SQL-Server Agent logon have permissions to this folder ?
Is it running as a domain account ?
risoyAuthor Commented:
More details:

"Cannot create a file when that file already exists.
(Source: (OS); Error number: 183)"

- From replication monitor: Snapshot agent
risoyAuthor Commented:
> Does the SQL-Server Agent logon have permissions to this folder ?

Yes. The E:\SQLRepl\unc\... is created successfully.

>  it running as a domain account ?

risoyAuthor Commented:
Now it creates a new empty folder under E:\SQLRepl\ftp\ each time it is run - still giving above error message. The folder under ...\unc\ holds only one direct sub-folder and the snapshot seems to be complete.
Can you turn on verbose logging, and delete that snapshot file that already exists...
by adding : -OutputVerboseLevel 2

Sometimes you will get this error if one snapshot is still running in the background and has not completed running an initial snapshot.

The snapshot job, will contain a line with:
snapshot -<settings> -<settings> -<settings>

Also if you were creating a compressed snapshot -- turn this option off for the mo (seen some problems with big snapshots and compression)

BOL on snapshot agent command line settings:
Here are the full details on how to add logging to the agents:;EN-US;312292
risoyAuthor Commented:
A little breakthrough. The -OutputVerboseLevel 2 switch strangely didn't return any more error messages. But by unchecking the 'Generate snapshots in the normal snapshot folder' it created the ftp one.

Running the snapshot agent now works. New problem: Creating the subscription:

"The schema script '' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
The process could not connect to FTP site 'SERVERNAME' using port PORTNO.
(Source: SUBSCRNAME (Agent); Error number: 0)
550 E:\SQLRepl: The filename, directory name, or volume label syntax is incorrect.
(Source: (OS); Error number 12003)

If a run from the command line 'ftp servername portno' I have no problem browsing the snapshot folders. I get straigh to the folder where the folders ftp and unc are.
Have you added verbose Level output to 2 , and added the output file for the errors to be logged to ?
Examine this for further info.

Check the following:;en-us;318592&Product=sql2k -- That all your subscribers have SP2 or above with SQL Server 2000.;en-us;320600&Product=sql2k;en-us;832902&Product=sql2k

risoyAuthor Commented:
It seems to be working just now.

Firstly I wrongly added the local (server)  E:\SQLRepl on the ftp path by mistake. What it needed to be was 'ftp'. It did not work without it.

Thank you so much for your help! I will keep the thread open for a couple of days so I know that I won't run into any more related problems.

Thanks again!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.