Sunday, February 12, 2012

Connection Problems SQL SERVER 2000 and SQL SERVER 2005

I have two machines one has SQL SERVER 2000 and other one has SQL SERVER 2005. I want to connect to SS2000 (SQL SERVER 2000) using the machine SS2005. This will allow me to run queries on both and transfer tables using SQL Server Management Studio. I don’t want to use text files to import and export.

At the moment i am unable to connect using any of the network protocol (default, Named pipes and TCP/IP)

Some of the errors i get are as follows

Cannot connect to VALTHORENS.

ADDITIONAL INFORMATION:

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) (Microsoft SQL Server, Error: 1326)

Cannot connect to VALTHORENS.

ADDITIONAL INFORMATION:

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) (Microsoft SQL Server, Error: 1326)

<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->

I have also checked that Enable remote connection option is checked.


i will be really grateful for any help.

Just to clarify bit further

I am trying to connect to SQL SERVER 2000 using SQL SERVER Management Studio of SQL SERVER 2005 (enterprise edition)


|||I am wondering where you checked for the remote connections property in SQL Server 2000, as it did not exists in this version.

OK, seems that you are trying to connect to a default instance using the default port of SQL Server which is 1433, therefore:

-Make sure that the instance you are connecting to is REALLY a default not a named instance. (named instances look like ServerName\InstanceNamehere
-Make sure that the instance of SQL Server 2000 is using the default port, otherwise, you will have to tell the port within the connection string (e.g. Servername,portnumber)
-Make sure that you have a physical connection (ping) to the server including allowed ports between them.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I'm having the same problem, same error, several days from now with no luck

SQL 2000 SP4

Windows 2003 R2

IIS 6.0

.NF 2.0

ASPX

Everything in the same box.

I'm sure there is not a named instance, it is the default.

I'm sure the port es 1433 (network server configuration and client server)

I'm sure there is a physical connection, I can ping, it is the same box.

The problem stills there, we try all we know, changed connstrings, changed user and passwords, switch between SQL and Windows authentification back and forth, tried the telnet, tried the osql online command, reinstalled SQL 2000, everything but still the same error.

Any help would be tremendously appreciated.

|||Is the service started ? Can you use sqlcmd, I mean is it working or do you get an exception ?

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hello Jens, thanks for your response

Yes, the services are started.

I can access the SQL Server from other machines inside the network.

Thanks

|||To make sure that you are really connecting to the local instance using the default port, use the syntax .,1433 as the servername.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi Zaki,

Error 40 could mean a variety of things but in your case, it appears likely that your problem stems from a file sharing security issue. These links should help you solve your problem:

http://blogs.msdn.com/sql_protocols/archive/2007/05/16/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error-xxx.aspx

http://blogs.msdn.com/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx

Il-Sung.

No comments:

Post a Comment