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
-
Nigel, this is definitely some network problem. It does appear you are listening on the port, perhaps a router is blocking on one end?
-
ok, thanks Steve, I will look into it and get back to this thread when i know more...
-
Let this be a lesson for those looking for something SERIOUSLY wrong with their connection....
Check the obvious first
My servers were on a network where port 1433 and 1434 are blocked
I simply changed the port on the server connection utility and then created my odbc connections (changed client connectivity to look for new port as well)...
it works just fine!
Thanks Steve for pointing me in the correct direction.
-
but man, i created one hell of a list of repro steps eh?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply