Error Registering SQL 2000 in Management Studio | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Error Registering SQL 2000 in Management Studio

Hello to all I just recently installed SQL Server 2005 on windows 2003 and I am trying to register an instance of SQL server 2000 on windows 2000 in the management studio. Every time I try to register the server I get the following error: 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 I have a previously installed SQL Server 2005 on windows 2003 and that server can see and have no problem registering the SQL server 2000 on windows 2000 in the management Studio.
Can any one help me with this? ….Please
Andre Update to above:
Here are my servers:
LES – New install on SQL 2005 and I want to use management studio to register all
TEST – Previous install of SQL 2005
VLS – Old install of SQL 2000 and the server giving the problem
EMS – Old install of SQL 2000 LES can connect/Register TEST, EMS but not to VLS.
TEST can connect/Register LES, EMS and VLS.
Make sure named-pipes protocol has been enabled on the SQL Server 2000 instance. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Also make sure that there is no server alias created in Client Network Utility using TCP/IP.
Mohammed U.
On my SQL Server 2000 instance, Client Network Utility: TCP/IP (1433) and Named Pipes (sqlquery) are enabled The Server Alias Configurations list is empty
I am still not able to Register the 2000 server on the LES -SQL Server 2005 Thank you all for responding
Andre
I am making some progress now… I stopped and restarted the SQL Browser…on LES (new SQL2005)
and found and deleted some aliases in the SQL Server Configuration manager on LES (new SQL2005)
I can now make a DSN(ODBC) connection to troubled server VLS SQL2000 from LES (new SQL2005)
I am able to Register the VLS SQL2000 on LES (new SQL2005) using SQL management studio…. great Thank you all for pointing me in the right direction. OK:
I am now trying to migrate the databases from VLS SQL2000 to LES (new SQL2005) using the copy wizard and I am having trouble with that, it fails with: StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
InnerException–>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)
So on one hand I can connect to the server, select/update and delete data but not able to Run the Copy Database Wizard as it fail to open a connection to the server. I really need your help.
Andre
Check the remote connection properties in SQL 2005 by using SQL SERVER 2005 SURFACE AREA CONFIGURATION.
If the remote connection is enabled then check what protocol it is using in SQL SERVER 2005 SURFACE AREA CONFIGURATION… I think by default it TCP/IP only protocol so you may need to change it TCP/IP and NAMED PIPES protocol. Instead of using copy database wizard for upgrade you can use backup/restore OR attach/detach method where you don’t get these issues. Mohammed. Mohammed U.
Thank you, I tried the backup and Restore method and had some trouble with user ids when running the application against the database. I can connect to VLS from the TEST 2005 SQL server with ease and copy the databases and logins from VLS and my application runs as normal. if I can solve the user IDS issue then the backup/restore would be fine for me. I restored the databases to LES and create the logins on Les after which i was not able to login to the database. if I do not map a VLS drive on lES then I cannot register the SQL Server in Management Studio and I can only map as domainnameusername and not username only. Once I map the drive registration is OK, I am not sure what this is telling me. Andre
Right that might be a problem with orphaned users, refer tohttp://vyaskn.tripod.com/troubleshooting_orphan_users.htm &http://msdn2.microsoft.com/en-us/ms175475.aspx in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Just wanted to say thanks to Satya and Mohammed for all your help I went back to the backup/restore method and used the links you provided to solve my orphan issues. I was able run my application and perform the task that failed the first time round. It looks great so far. Thank you
Andre
Glad it worked and appreciate your feedback. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>