Distributor cannot connect to subscriber

I am setting up my 2005 Replication system...

publisher = 2005 sp1

Subscriber = 2005 sp1

I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away.

Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections.

I am not sure where to look at next... I never had this issue in 2000.

Did a little more testing. My distributor/publisher also has SQL 2000 on it. I think this might have something to do with it.

I created a linked server on my subscriber to my publisher/dist and it has no problem connecting what so ever.

Could it be that my pub/dist is using the wrong client files?

|||

Hi William,

Are you using merge or transactional replication?

Is the subscription that you set up a push or pull? This will determine where the distribution or merge agent is running.

In you second note, you indicate that the distributor/publisher has SQL 2000. Is this in addition to SQL 2005, per the first note?

Assuming that you have SQL 2000 and SQL 2005 on the boxes, then you are using named instances for the SQL 2005 installations. If you have named instances, not only do you need to enable remote connections, but you need to insure the SQL Browser service is running for connectivity to work properly.

This link has some more information about SQL Browser -- http://msdn2.microsoft.com/en-us/library/ms165724.aspx

Hope this helps,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hey Tom,

I am using transactional replication. The pub/dist has both 2000 (default) and 2005 (named) instances.

The subscription is a push subscription. I will take a look at the link you sent.

=== Edited by William Lowers @. 24 Jan 2007 4:11 PM UTC===
After reading the article I check my pub/dist. It is set to listen on TCP port 1434 which I know works cause I had to have the Firewall openned to get to the server. The SQL Browser server appears to be running.

Is there anything I can do to ensure it is working?

|||

update

Since my subscriber is a dev server and if I mess it up it does not matter I did the following...

I created a publication on this server with itself being the distributer also.. that way it is setup just like the server with 2000 and 2005 on it. I then created a subscription from this server (2005 only) to the 2005 database on the 2000 and 2005 server.

No problem what so ever. So I am leaning towards the fact that it has to do with having both versions on one server...

Any idea? or is this a Bug?

|||

Can you try the following connectivity test from command prompt?

at your dev machine, try to connect to the other box

osql -S<publisher_server> -U<user id> -P<password>

and try the connect from publisher machine to your dev machine as well. If both work, you can rule out the protocal enabling issue. If it works only from your dev box to publisher machine, but not the other way around, it might be as simple as enabling TCP and name/pipe from configuration manager.

Gary

|||

Ok... I did as suggested and the publisher has no problem connecting to the subscriber...

So what does that mean?

I can connect via command line,SMS but not replication.

|||

Ok... So the above statment is only half true. After doing the above I thought about looking at my path cause it executed the osql from the default directory that I was openned to....

So when I execute from 80/tools/binn I can connect without issue.

From 90/tools/binn I get this error

[SQL Native Client]TCP Provider: No connection could be made because the
target machine actively refused it.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections.

|||

Can you check the protocal interface are enabled for TCP and name pipe?

You can find the instruction on the following posting from Mahesh,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1059301&SiteID=1

Gary

|||

I double checked and I already have that set. I cycled the server anyway... Still no luck.

What makes no sense to me is that I can connect with 2000 osql but not 2005 osql...

Is there anyway to check what libraries each client is using?

|||

What was your OS? If windows 2000 server, would install MDAC 2.7/2.8 help?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

OS is Windows 2003 SP1...

I am thinking what happened was the re-install of 2000 client tools that was done when we could not work on DTS...

I

|||I will guess that the re-applying of SQL 2000 bits after SQL 2005 was installed is what's causing the problem. Multiple versions of SQL Server installed on the same box is only supported provide you install the earlier version first, then the later version second. Re-installing the earlier version after the later version is already installed can mess things up. I suggest reinstalling/repairing your SQL 2005 installation.|||

That is what I am thinking but since the server is a major player in our production environment, I don't think I will be allowed to...

So I am asking my boss for other options...

Thanks for all the help.