Problem connecting to SQL Server 2000

[John Yung]

Hi,

I installed SQL Server 2005 Express on one of my development machine, I had
since then having problem connecting to a SQL Server 2000 (On a cluster)
database. The problem persist even after I remove SQL Sever 2005 Express.
The following is the error message:

SAC.EDS.CorpAction.WithholdingTax.UnitTesting.WithholdingTaxOverrideRecordCr
eate :
System.Data.SqlClient.SqlException :
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: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)

I do not experience the same problem when connecting from a machine without
SQL Sever 2005 Express installed. I don't know why ADO.NET 2.0 thinks I am
trying to connect to a SQL Server 2005 database.

Please advice!

Thanks,

John Yung

 

[Rick Byham [MS]]

Some questions: Is the "developement machine" the same computer as the "SQL
Server 2000 (On a cluster)"? I know that's not likely but if so, your
problem may be related to SQL Server Browser.
Also regarding SQL Server Browser service, is the cluster a named instance
and what service pack is the SQL Server 2000 running?
--
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.

 

[John Yung]

Rick,

The SQL Server 2000 is not on the development machine. According to our DBA,
SQL Server 2000 is running SP4. Further more, it is not a cluster (my
mistake), but an instance of SQL Server running on a single database
machine.

Thanks,

John Yung

 

[Rick Byham [MS]]

I suspect your problem is related to either the configuration of the client
software on the formerly SQL Express computer, or the SQL Server Browser
Service on the server computer.
Installing SQL Server 2005 Express edition installed the SQL Native Client.
I don't know if your uninstall of Express also uninstalled the Microsoft SQL
Server Native Client. You can check that from Control Panel/Add and Remove
Programs, as it is listed separately. If the SQL Server Native Client was
removed, then you should go back to connecting from the SQL Server 2000
client software, but perhaps the install of Express somehow disabled that.
If SQL Server Native Client is not present, try reinstalling the client
software from the SQL Server 2000 CD.
If the SQL Server Native Client is still installed, which seems likely from
your error message, and if the SQL Server Browser service is not running on
the Server, the client may be having trouble resolving the instance name. Is
the SQL Server a named instance or the default instance? If it is the
default instance, then the Native Client will attempt to connect to the
machine name and port 1433. If the server is a named instance, and if the
SQL Server Browser Service is not running (as I suspect), then the SQL
Server Native Client has no way of figuring out what port to connect to. SQL
Server 2000 made different assumptions and so may be coordinating better
with SQL Server 2000.
So my advice, is first, make sure you have a client. Either SQL Server
Native Client, or reinstall the SQL Server 2000 software.
Second, see if the SQL Server Browser Service (SQLBROWSER) is present on the
server. If so, try starting the service, and then connecting.
If the SQL Server Browser Service is not present, and if the SQL Server 2000
is a named instance, you will need to connect using the port number, as in
computername,1500
--
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.

 

[John Yung]

Rick,

I tried all your suggestions, but they don't work; for example: I use this
as connection string:
Data Source=QA1SQL02, 1500;Initial Catalog=saccorpaction;User
Id=sacweb;Password=websac1
note: QA1SQL02 is the database server name,

I got this error message:
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: TCP Provider, error: 0 - No connection could be made because the
target machine actively refused it.)

I am able to confirm that the problem is cause by ADO.NET 2.0, and not SQL
Server 2005 Express. I tried the software on a fresh machine with .NET 2.0
installed, i received the same error messages.

I don't understand why ADO.NET thinks I am trying to connect to SQL Server
2005 (unless the error message is hard-coded to 2005).

Please advice, thanks,

John Yung

 

[John Yung]

Rick,

I compile (VS.NET 2003) the same code in .NET 1.1 and it works:

This is the connection string I used:
Data Source=QA1SQL02\DEVSQL03;Initial Catalog=saccorpaction;User
Id=abc;Password=abc

I am very sure that the problem is from ADO.NET 2.0.

Thanks,

John Yung

Rick,

I tried all your suggestions, but they don't work; for example: I use this
as connection string:
Data Source=QA1SQL02, 1500;Initial Catalog=saccorpaction;User
Id=sacweb;Password=websac1
note: QA1SQL02 is the database server name,

I got this error message:
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: TCP Provider, error: 0 - No connection could be made because the
target machine actively refused it.)

I am able to confirm that the problem is cause by ADO.NET 2.0, and not SQL
Server 2005 Express. I tried the software on a fresh machine with .NET 2.0
installed, i received the same error messages.

I don't understand why ADO.NET thinks I am trying to connect to SQL Server
2005 (unless the error message is hard-coded to 2005).

Please advice, thanks,

John Yung

I suspect your problem is related to either the configuration of the client
software on the formerly SQL Express computer, or the SQL Server Browser
Service on the server computer.
Installing SQL Server 2005 Express edition installed the SQL Native Client.
I don't know if your uninstall of Express also uninstalled the Microsoft SQL
Server Native Client. You can check that from Control Panel/Add and Remove
Programs, as it is listed separately. If the SQL Server Native Client was
removed, then you should go back to connecting from the SQL Server 2000
client software, but perhaps the install of Express somehow disabled that.
If SQL Server Native Client is not present, try reinstalling the client
software from the SQL Server 2000 CD.
If the SQL Server Native Client is still installed, which seems likely from
your error message, and if the SQL Server Browser service is not running on
the Server, the client may be having trouble resolving the instance

name.

Is
the SQL Server a named instance or the default instance? If it is the
default instance, then the Native Client will attempt to connect to the
machine name and port 1433. If the server is a named instance, and if the
SQL Server Browser Service is not running (as I suspect), then the SQL
Server Native Client has no way of figuring out what port to connect to. SQL
Server 2000 made different assumptions and so may be coordinating better
with SQL Server 2000.
So my advice, is first, make sure you have a client. Either SQL Server
Native Client, or reinstall the SQL Server 2000 software.
Second, see if the SQL Server Browser Service (SQLBROWSER) is present on the
server. If so, try starting the service, and then connecting.
If the SQL Server Browser Service is not present, and if the SQL Server 2000
is a named instance, you will need to connect using the port number, as in
computername,1500
--
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.

machine,

I

SAC.EDS.CorpAction.WithholdingTax.UnitTesting.WithholdingTaxOverrideRecordCr thinks

I

 

[Rick Byham [MS]]

Sorry, I'm a DBA, not a programmer. I'm pretty useless when it comes to
..NET.
--
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.

 

[John Yung]

Rick,

Do you know anyone from the ADO.NET group that could help me with this?

Thanks,

John Yung

 

[Guest]

I am having this exact same problem. Have you found a solution?

Thanks,
Randy

 

[Pablo Castro [MS]]

There is a slight difference in the way ADO.NET 1.1 and 2.0 handle protocol
order that might be the issue here.

It's likely that the computer where SQL Server is running has Windows
Firewall enabled, and that the SQL Server TCP port (1433 by default,
variable if using a named instance such as SQLExpress) is blocked. It's also
likely that 1.1 is working because it's using named pipes instead of TCP,
which gets through the same channel as file-sharing which is typically open.

If this is the case in your scenario, you can do one of these:

a) force named pipes. you can do this by adding "np:" before the server
name. In your connection string it would be "Data Source=np:QA1SQL02"

b) open the SQL Server port in the firewall of the computer where SQL Server
is running


Regarding your question about SQL 2000 vs 2005, note that ADO.NET is not
confused, it's the error message that isn't clear; we meant that if this
*were* SQL Server 2005, then you may want to check out the connection
setting.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

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


"(e-mail address removed)"