Error trying to connect to local database - What is going on?

[Alan Silver]

Hello,

Server configuration:
Windows 2003 Server SP2
SQL Server 2000 SP4
..NET v2.0.50727

just built up a new server using the same configuration as my current
one. I even used the same CDs and registration keys, so I'm certain that
the two machines are as similar as they can be.

I moved the web sites over to the new server, but left the SQL Server
databases on the old machine. I am now trying to move the databases over
as well, but have struck a weird problem that has me stumped.

On the old server, the web sites were using a connection string like...

Server=daisy;uid=User1;pwd=Password1;Network Library=dbmssocn;

....and this has been fine. The new server can happily connect to the old
one and get data.

I copied a database over to the new server, then tried changing the
connection string to use the name of the new server, and got the
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: TCP Provider, error: 0 - No connection could be
made because the target machine actively refused it.)"

I used Query Analyser to connect to the new server, using the user name
and password in the connection string, and it worked fine, so I know the
details are correct.

Now I don't understand why the connection was refused, given that the
two servers have the same OS, same SQL Server, etc. The connection
worked fine when it was the new server connecting to the old, so it
can't be anything to do with remote connections, as the new server has
IIS and SQL Server on the same machine.

I also don't understand what SQL Server 2005 has to do with it, as both
machines have SQL Server 2000 installed.

I tried using other connection strings as well, but nothing helped.
Amongst others, I tried...

Data Source=(local); User Id=User1; Password=Password1;

....but that gave me an error saying a Provider was needed. If I added a
Provider to it, it gave me an error saying that Provider wasn't a
recognised keyword!

I have spent pretty much the whole afternoon trying to fix this and am
completely stuck. I would really appreciate any help.

TIA

 

[Guest]

SQL 2005 has a lot of surface area and other configurations (and protocols)
that need to be enabled, it comes pretty locked down by default.
-- Peter
Recursion: see Recursion
site: http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
bogMetaFinder: http://www.blogmetafinder.com

 

[Alan Silver]

SQL 2005 has a lot of surface area and other configurations (and protocols)
that need to be enabled, it comes pretty locked down by default.

Thanks, but that's not the problem. As I said below, I'm not using SQL
Server 2005. That's one of the things that puzzles me. I'm using SQL
Server 2000, and both the server and web site are on the same machine,
so there's nothing remote happening anyway. It's all local.

Thanks for the reply. Any other ideas?

 

[George Ter-Saakov]

Dude you have a messed up network.
If you have SQL 2000 but getting errors about SQL 2005 then most likely you
are connection to some other machine.

Try to specify IP address rather than a name.

George.

 

[Alan Silver]

Dude you have a messed up network.

Don't think so. The network seems fine, and everything else works. Read
on...

If you have SQL 2000 but getting errors about SQL 2005 then most likely you
are connection to some other machine.

Try to specify IP address rather than a name.

Nope, didn't make any difference. I can specify the machine name or the
IP address and the same problem occurs.

Since first posting I have had some success, but this has confused me
even more!

I copied the databases over from the old server to the new one, and
changed the connection strings in the web sites on the new server. All
but two of the sites are now working. The weird thing is that one of
these two databases is exactly the same in structure as three others
that work. So, I have four databases on the same server, all with the
exact same structure, with connection strings that are identical, other
than the user name and password details, but only three of them work.

I am completely stumped. Any further suggestions are welcome. TIA

George.

 

[Alan Silver]

OK, to follow up my own post, I have the problem a little more clear
now. I have transferred most of the databases over, and have had
success. I still have two that are causing problems. I am certain that
it is not database connectivity per se that is the problem, as most of
the databases are working fine.

This server runs some e-commerce sites that use common code, so those
databases have a common structure. Three of these databases went over to
the new server without problem. All I did was change the name of the
server in the connection string. The fourth is causing problems.

The original connection string that was used was of the form...

Server=daisy;uid=User1;pwd=Password1;Network Library=dbmssocn;

....which worked fine. When I changed the name of the server to the new
one, I got the 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: TCP Provider, error: 0 - No connection could be
made because the target machine actively refused it.)"

I then tried the following connection string...

Data Source=daisy;Initial Catalog=DB1;User Id=User1;Password=Password1;

....which worked fine connecting to the old server. When I changed the
connection string to point to the new server, I got an error saying that
an OLE DB provider had not been specified. An example of
"Provider=SQLOLEDB" was given in the error message.

As I am using SQL Server, I copied this into the connection string, and
then got the error message...

Keyword not supported: 'provider'.

These last two error messages seem to contradict each other.

So, I'm stuck on this database. As I said, three others with exactly the
same structure worked fine, this one doesn't.

TIA for any help.