Can't connect to SQL Server from local Enterprise Manager; can connect from remote Enterprise Manager?
Posted on 2007-11-29
Our MS SQL server (2003) started acting shady yesterday/today, don't know why exactly.
Anyway, there are some strange symptoms:
- The database is up and running; client applications function normally
- The local Enterprise Manager cannot connect to the database; error message below :
"A connection could not be established to SRV12\XXXXX_DB.
Reason: SQL Server does not exist or access denied.
Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the SRV12\XXXXX_DB node) and try again."
However, the Manager can connect to an other database on the same machine.
- Enterprise Manager on a remote computer *can* connect to the database (at least there's something working).
- SQL Server Agent won't start:
When trying to start from the remote Enterprise Manager these are in the error log:
" Unable to connect to server 'SRV12\XXXXX_DB'; SQLServerAgent cannot start"
" SQLServer Error: 10049, ConnectionOpen (Connect()). [SQLSTATE 01000]"
" SQLServer Error: 17, SQL Server does not exist or access denied. [SQLSTATE 08001]"
When trying to start the service from the services manager:
"The SQLAgent$XXXXX_DB 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."
When trying to start from the command line using 'd:\PROGRA~1\MICROS~1\MSSQL$~1\binn\sqlagent.exe -i XXXXX_DB' it's this error: "StartServiceCtrlDispatcher failed (error 6)."
Things I've done so far:
- Restart SQL services
- Reboot server
- Delete and recreate the server registration in local Enterprise Manager
- Create a server registration on a remote server (via which I can manage the server now)
- Set server authentication to both 'SQL Server and Windows'
- Used 'cliconfg' to enable named pipes and tcp/ip protocols on both servers.
- Pulled my hair out ;-)
So, does anyone have a clue why I cannot connect to the server from the local Enterprise Manager, and why SQL Server Agent won't start?
Which steps do I take to repair these problems?
Thanks in advance for any efforts to help!