I have a main SQL Server, running SQLServer 2000, with two (in theory) subscribing servers, each running SQL Server 2005.
One of these is subscribing fine, but the other always seems to fail subscribing, both when attempting to set up the subscription from the publisher (SQL2000) to the subscriber(SQL2005), and when trying to set it up from the subscriber to the publisher, both via Server Management Studio 2008 and via SQL Enterprise Manager
In both cases, the publication is created on the publisher, but a corresponding subscription is not created on the subscriber.
I then get an error message saying "The process could not connect to Subscriber [ServerName]", and no more sign of activity. There's no problem with logins, permissions, etc. The password for sa is the same on both machines, and is different on the 2005 machine that works.
Is this a problem anyone else has encountered?
EDIT: I've now tried adding both a dbSubscriber and a dbPublisher access account on each server so that they're not logging into each other using "sa", but it doesn't seem to have made any difference.
EDIT2: Adding a push subscription does not create a Local Subscription on the subscribing server. Is this normal, or is this the point at which everything is falling to pieces?
We don't really understand what was going wrong, but we think that the 2005 server was unable to accept the 2000 server as a Push Publisher.
We created four different Pull subscriptions on the 2005 server and the first three failed, while the fourth magically worked.
We are accepting this as a blessing from the God of Computers and will not question His benevolence.