SQL 2005 Standard won't accept connections - exhausted common/FAQ remedies

[plastic_wrapped]

Hi,

SQL Server 2005 isn't accepting any connection other than SQL Management Studio from the local machine. However, even that's exhibiting strange behavior (please see below for more details).

I've read through all the common errors regarding enabling remote connections via the SAC tool, making sure protocols are enabled, etc. This case seems to be completely outside. Any advice appreciated.

Thanks,

Elias



Other behavior:

A separate instance of SQL Server 2000 on a different machine on the same domain is functioning without problems.

Access through SQL Server Management Studio works, from local machine only.

Able to ping machine by name and IP.

Telnet to either SQL Server (functional 2000 or problem 2005) on port 1433 is not possible - even when attempted from the local machine using 127.0.0.1! Telnet to both machines works using other ports.

When creating a DSN from a client machine, problem 2005 instance is in list of available SQL Servers, however connection fails ("SQL Server denied access or does not exist").

MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL Server (MSSQLSERVER) service on Local Computer Started and then Stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."

SQL Server Surface Area Configuration for Services and Connections tool does not display Database Engine option as indicated by this MS guide to enabling remote connections: http://support.microsoft.com/default.aspx?scid=kb;en-us;914277

SQL Server communication manager shows TCP, Shared memory, named pipes enabled. However event log seems to show instance not listening via any protocol. Message displayed as: "SQL Server listening on ." (should read: "Server is listening on ['any' <ipaddress> <ipv4> <Port Number>])

Several answers indicated uninstall and reinstall could solve these problems. However, wizard refused to uninstall SQL Server.

Attempted disabling Shared Memory as documentation indicates this protocol gets used first. No luck.


MS Forums questionairre:

[1] Client side:

1. What is the connection string in you app or DSN? N/A. Can't connect via any remote method, including client Control Panel > ODBC > create DSN, SQL Server Enterprise Manager, third party application.
2. If client fails to connect, what is the client error messages? Several, depending on method: "server does not exist or access denied", "error has occurred... under default settings, SQL Server does not accept remote connections", etc.
3. Is the client remote or local to the SQL server machine? Remote
4. Can you ping your server? Yes

5. Can you telnet to your SQL Server? No. However, there's a functional instance of SQL Server 2000 on the same domain which has no problems- telnet also fails to that instance, even locally from either machine using the 127.0.0.1 address. Telnet functions for other ports.

6. What is your client database provider? N/A Or/And, what is your client application? SQL Management Studio and others
7. Is your client computer in the same domain as the Server computer? Same domain
8. What protocol the client enabled? TCPIP when I attempted to create the DSN. Not sure what protocol SQL Management Studio uses.
9. Do you have aliases configured that match the server name portion of your connection string? No
10. Do you select force encryption on server and/or client? No



[2] Server side:



1. What is the MS SQL version? SQL Server 2005
2. What is the SKU of MS SQL? Standard
3. What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ] Have tried all three individually and together. Configuration Manager exhibits strange behavior (see below)
4. Does the server start successfully? Server starts successfully when the machine boots. However once it's stopped, it can't be restarted (see below)
5. If SQL Server is a named instance, is the SQL browser enabled? Default instance
6. What is the account that the SQL Server is running under? Local System
7. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? Not sure, but SQL Server 200 instance is working OK
8. Do you make firewall exception for SQL Browser UDP port 1434? Unknown



[3] Platform:

1. What is the OS version? Client: Windows XPSP2 Server: Windows Server 2003
2. Do you have third party antivirus, anti-spareware software installed? Client: Trend Micro Server: Unknown
Edit/Delete Message

[russell]

the service stopping and not restarting sounds like u need a re-install.

that said, i don't think you have the browser service running which will definitely prevent remote connections.

why enable named pipes? just enable tcp/ip in SAC

aslo, and this is not the problem, client apps should use oledb provider, not dsn.

as far as services stopping when not in use... this sounds like a very bad idea for sqlserver and sqlagent services. this is configurable.

edit:: oh yeah...have your network admin check 1434 udp on firewall. but shouldn't be necessary if only accessing inside network

[plastic_wrapped]

Thanks for the input, Russell. Will check on UDP 1434 and the Browser service. Some more info:

the service stopping and not restarting sounds like u need a re-install.

I tried to re-install from the discs and the command line, but both only removed components, netiher one would remove MSSQLSERVER. I fould the instructions for manual reinstall on MSDN, but since it involves editing the registry I've been trying to avoid it.

why enable named pipes? just enable tcp/ip in SAC

This is one of the weird behaviors - I can't edit anything in SAC. The "database engine" does not appear as an option, only components.