Register SQL 2000 Server in SQL 2005 Server Management Studio

Question

Register SQL 2000 Server in SQL 2005 Server Management Studio

Scout7

Old Hand

Points: 331
More actions
January 25, 2006 at 5:27 am

#113170

I have been banging my head against a wall over this for a while now and I cannot figure what I am doping wrong. I have SQL 2005 Standard installed on WInXP inc SP2 and I need to be able to register and connect to a SQL 2000 server on another machine which is W2K3 inc SP1. I am getting the following error when I try to register the server;
'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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)'
Which server is being complained about here? I have allowed TCP/IP, Named Pipes & shared memory. Both servers have the default port changed from 1433 but are still both the same? What am I missing?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Jansen-278311 SSC Enthusiast Points: 121 More actions · Answer 1

You need to allow remote connections in the SQL Server Surface Area Configuration utility found in your Configuration Tools folder.

Scout7 Old Hand Points: 331 More actions · Answer 2

Thanks for your reply David but I have already done that. When I try to register and browse for the SQL2000 server it appears OK under the list of network servers, I am using the correct login information but still no joy. Which, if any, are the logs that I can look at, and on which server, to see if there are any other messages related to this problem? How can I tell if a connection is actually being attempted? The BOL are not very helpful at all for this! Can anyone give me any more pointers?

David Jansen-277351 SSC Enthusiast Points: 146 More actions · Answer 3

Make sure you have disabled the WinXP firewall or allowed traffic on port 1433. Shutdown the firewall service to make sure it is off if you decide to disable it.

Gabriel Petrovay Mr or Mrs. 500 Points: 546 More actions · Answer 4

I have the same problem and trying to register the SQL2k instance in the Management Studio of two network computers. On one of them work and on the other it doesn't.

I don't remember doing any configuration setup on the one working.

Any other ideeas?

Thanks!

Scout7 Old Hand Points: 331 More actions · Answer 5

Tried this Dave but still no joy. Any other ideas? I need to make a connection to SQL2K so that I can convert the database to SQL2K5.

Gabriel Petrovay Mr or Mrs. 500 Points: 546 More actions · Answer 6

I posted above my problem.

If the firewall wand down, the Management Studio could connect to the remote SQL server. Since that was quite an important computer we had, i simply couldn't let the firewall down. The problem on the computer that could not register the remote SQL 2k server was that i was trying to register the SQL server with its netbios name (computer name). Trying to register it by its IP it worked. So it was a local problem: added the name and IP of the comp hosting the SQL 2k server in the hosts file (under .../System32/driver/).

Now it works!