Cannot establish a connection to SQL Server 2K sp3a remotely

Cannot establish a connection to SQL Server 2K sp3a remotely

Ok, here's the scenario.

1. Windows Server 2003 Standard running SQL Server 2000 sp3a
2. Windows Server 2003 Standard (where the website sits)
3. This is not a network, the connections are over the Net
4. I have set up SQL Server 2k to allow for TCP/Named Pipes listening.

checking the SQL Logs I see the following:
(note: I replaced private information with "x's")
------------------------

2004-03-09 10:39:51.39 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: )

2004-03-09 10:39:51.39 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-03-09 10:39:51.39 server All rights reserved.
2004-03-09 10:39:51.39 server Server Process ID is 3252.
2004-03-09 10:39:51.39 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-03-09 10:39:51.39 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-03-09 10:39:51.62 server SQL Server configured for thread mode processing.
2004-03-09 10:39:51.62 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-03-09 10:39:51.64 server Attempting to initialize Distributed Transaction Coordinator.
2004-03-09 10:39:53.65 spid3 Starting up database 'master'.
2004-03-09 10:39:53.95 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-03-09 10:39:54.21 server SQL server listening on x.x.x.x: 1433.
2004-03-09 10:39:54.21 server SQL server listening on 127.0.0.1: 1433.
2004-03-09 10:39:54.23 spid5 Starting up database 'model'.
2004-03-09 10:39:54.26 spid3 Server name is 'xxxxxxx'.
2004-03-09 10:39:54.26 spid8 Starting up database 'msdb'.
2004-03-09 10:39:54.26 spid9 Starting up database 'pubs'.
2004-03-09 10:39:54.26 spid10 Starting up database 'Northwind'.
2004-03-09 10:39:54.26 spid11 Starting up database 'xxxxxxxx'.
2004-03-09 10:39:55.12 spid5 Clearing tempdb database.
2004-03-09 10:39:55.23 spid9 Starting up database 'xxxxxxxx'.
2004-03-09 10:39:55.60 spid10 Starting up database 'xxxxxxxx'.
2004-03-09 10:39:56.48 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-03-09 10:39:56.48 server SQL Server is ready for client connections
2004-03-09 10:39:56.53 spid5 Starting up database 'tempdb'.
2004-03-09 10:39:56.64 spid3 Recovery complete.
2004-03-09 10:39:56.64 spid3 SQL global counter collection task is created.

------------------------

Ok, so I try to connect via ASP, ASP.NET, ODBC, Enterprise Manager all from the Web server.
In every attempt, I get access denied messages.

On the SQL Server box, I went into a command prompt and ran the following command:
ISQL -Stcp:SQLServerNameHere -E -Q"select @@version"

This ran fine. Here is the result:
-------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copy
right (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows
NT 5.2 (Build 3790: )

(1 row affected)
-------------

I also ran the following command:
netstat -an

This ran fine with the following results:
(note: I replaced private information with "x's")

-----------------
Active Connections

Proto Local Address Foreign Address State
TCP 0.0.0.0:21 0.0.0.0:0 LISTENING
TCP 0.0.0.0:25 0.0.0.0:0 LISTENING
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:99 0.0.0.0:0 LISTENING
TCP 0.0.0.0:110 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:143 0.0.0.0:0 LISTENING
TCP 0.0.0.0:366 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:465 0.0.0.0:0 LISTENING
TCP 0.0.0.0:993 0.0.0.0:0 LISTENING
TCP 0.0.0.0:995 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1101 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING
TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING
TCP 0.0.0.0:5222 0.0.0.0:0 LISTENING
TCP 0.0.0.0:5223 0.0.0.0:0 LISTENING
TCP 0.0.0.0:5269 0.0.0.0:0 LISTENING
TCP 0.0.0.0:32000 0.0.0.0:0 LISTENING
TCP 0.0.0.0:32001 0.0.0.0:0 LISTENING
TCP x.x.x.x:110 66.25.183.245:33080 TIME_WAIT
TCP x.x.x.x:110 66.25.183.245:33401 TIME_WAIT
TCP x.x.x.x:110 66.25.183.245:33448 TIME_WAIT
TCP x.x.x.x:110 66.25.183.245:33555 TIME_WAIT
TCP x.x.x.x:110 66.25.183.245:33564 TIME_WAIT
TCP x.x.x.x:139 0.0.0.0:0 LISTENING
TCP x.x.x.x:1330 x.x.x.x:1433 TIME_WAIT
TCP x.x.x.x:3389 66.25.183.245:33260 ESTABLISHED
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1106 *:*
UDP 0.0.0.0:1107 *:*
UDP 0.0.0.0:1258 *:*
UDP 0.0.0.0:1259 *:*
UDP 0.0.0.0:1434 *:*
UDP 0.0.0.0:4500 *:*
UDP x.x.x.x:123 *:*
UDP x.x.x.x:137 *:*
UDP x.x.x.x:138 *:*
UDP 127.0.0.1:123 *:*
----------------

The Windows 2003 Firewall is DISABLED for both machines.

When I attempt to set up an ODBC connection from the Web Server, I get the following error:

----------------------------
Connection Failed:
SQLState: '01000'
SQL Server Error: 10051
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
----------------------------

Now, I attempted to set up the SAME ODBC connection on the SQL Server box, with the same login credentials (both using SQL Server Authentication), and it worked fine.

So, as far as I can tell, the SQL Server box is listening on port 1433. I have all the usernames/passwords set up for each DB. I can set all this up on the local box.

The SQL Server used to house the web server as well so there doesn't seem to be any firewall or connection issues going INTO the box (again, Internet Firewall is turned OFF on both machines).

I have also run the following command from the command prompt on both machines:

ISQL -Stcp:ServerNameHere -Uxxxxxx -Pxxxxxx -Q"select @@version"

On the SQL Server Box, I get a good return message, on the Web Server I get the following message:

--------------------------
C:\>ISQL -Stcp:66.135.32.79 -Usa -Pxxxxxx -Q"select @@version"
DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 10051: ConnectionOpen (Connect()).
--------------------------

Can anyone help? I appreciate your help!

Thanks in advance!

Nigel Gandy

...did you install MSDE ... and not SQL Server ???

My guess is your sql server is set to accept windows authentication only, so when you try to connect remotely the user account of the remote computer is not available on the sql server computer.

I have the exactly same pb...Since March, any news which can helps me...
Thanks

I have the same problem in two clients.
The las thing that I probe is register the SQL server in the local enterprice manager
with other alias than (local), in fact, the alias of the SQL server now is the ip address and it work
right.
In other hand i dont know yours escenarios but you probably are conected to internet and behind a firewall
be care about the port to use by SQL, it must be open in firewall.
I hope that this info help you all.

First of all, where are you getting this error? From a conection string in an applicaiton or from an attempt to create an ODBC data source in Windows using Control Panel, Administrative Tools, ODBC? Big dfference. IF you cannot do the ODBC data source, are you useing SQL Server authentication or NT (Windows) authentication? IF it's SQL Server auth and you cannot create the windows DSN then you have a network problem. Verify that the webserver is really on the network and can ping another server. Try ping both ways.

If you are using Windows authentication you may have a domain problem, like the new webserver computer is not part of the samd domain or is not set up right.

IF you CAN creaet this type of connection (Windows DSN) yet your applicaiton cannot connect using a connection string, then you have a syntax problem. Perhaps the provider used in the connection string is pointing to a version of a driver that is not the same on the new webserver as it used to be on the old webserver. Versions of drivers matter. Every so often a connection string will change with a new version of a driver which means progammers have to go through and modfy their code for the new connections tring.

Hi, I think I just solved this one, it's been bugging me for two months and the solution for me was a bit too simple.
But, anyway, it could work for you (let me know)
Thats how it goes:

Go to The Microsoft SQL Server program Group
Open Server Network Utility
Change the TCP/IP port from 1433 to anything (pick something safe above 1000)
Restart the SQL Service
Open Server Network Utility
Change the TCP/IP port back to 1433
Restart the SQL Service

Done, for me it did the trick.
Ilan