Outin
asked on
Can't connect to SQL Server from local Enterprise Manager; can connect from remote Enterprise Manager?
Hey all!
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.
ConnectionOpen (Connect())..
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:
"[000] Unable to connect to server 'SRV12\XXXXX_DB'; SQLServerAgent cannot start"
"[298] SQLServer Error: 10049, ConnectionOpen (Connect()). [SQLSTATE 01000]"
"[298] 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\MSSQ
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!
Best regards,
Outin
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.
ConnectionOpen (Connect())..
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:
"[000] Unable to connect to server 'SRV12\XXXXX_DB'; SQLServerAgent cannot start"
"[298] SQLServer Error: 10049, ConnectionOpen (Connect()). [SQLSTATE 01000]"
"[298] 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\MSSQ
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!
Best regards,
Outin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Outin,
Could you please tell me the output of the \LOG\SQLAGENT.OUT ?
Check the account that is used to run SQL SERVER AGENT.
Could you please tell me the output of the \LOG\SQLAGENT.OUT ?
Check the account that is used to run SQL SERVER AGENT.
ASKER
Contents of SQLAGENT.OUT:
2007-11-30 09:07:48 - ! [298] SQLServer Error: 17, SQL Server does not exist or access denied. [SQLSTATE 08001]
2007-11-30 09:07:48 - ! [298] SQLServer Error: 10049, ConnectionOpen (Connect()). [SQLSTATE 01000]
2007-11-30 09:07:48 - ! [000] Unable to connect to server 'SRV12\XXXXX_DB'; SQLServerAgent cannot start
2007-11-30 09:07:49 - ? [098] SQLServerAgent terminated (normally)
The logon information as retrieved from the MS Management Console
SQLSERVERAGENT service: local system account (this service is up and running)
SQLAgent$XXXXX_DB service: local system account (this service won't start)
2007-11-30 09:07:48 - ! [298] SQLServer Error: 17, SQL Server does not exist or access denied. [SQLSTATE 08001]
2007-11-30 09:07:48 - ! [298] SQLServer Error: 10049, ConnectionOpen (Connect()). [SQLSTATE 01000]
2007-11-30 09:07:48 - ! [000] Unable to connect to server 'SRV12\XXXXX_DB'; SQLServerAgent cannot start
2007-11-30 09:07:49 - ? [098] SQLServerAgent terminated (normally)
The logon information as retrieved from the MS Management Console
SQLSERVERAGENT service: local system account (this service is up and running)
SQLAgent$XXXXX_DB service: local system account (this service won't start)
Outin,
What is the configuration in the connection tab of the sql server agent properties ?
What is the configuration in the connection tab of the sql server agent properties ?
ASKER
MrNetic,
Settings in the connection tab of SQL Server Agent properties:
SQL Server connection: Use Windows Authentication
Local host server: (default)
Settings in the connection tab of SQL Server Agent properties:
SQL Server connection: Use Windows Authentication
Local host server: (default)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That doesn't do any good I'm afraid....entered user sa, but Server Agent still won't start....
Have you read the eventlog (app/sec/sys). ?
ASKER
Jup....
Only thing it says (app log) is
Error: "SQLServerAgent could not be started (reason: Unable to connect to server 'SRV12\XXXXX_DB'; SQLServerAgent cannot start)."
and
Informational: "SQLServerAgent service successfully stopped."
But that doesn't give any more info than we already had....
Only thing it says (app log) is
Error: "SQLServerAgent could not be started (reason: Unable to connect to server 'SRV12\XXXXX_DB'; SQLServerAgent cannot start)."
and
Informational: "SQLServerAgent service successfully stopped."
But that doesn't give any more info than we already had....
Outin,
Have you cheched the named pipes configuration of the server ?
Have you cheched the named pipes configuration of the server ?
Outin,
You named pipe default pipe should be something like :
\\.\pipe\MSSQL$XXXXX_DB\sq
Hope this helps,
Best Regards,
Paulo Condeça
You named pipe default pipe should be something like :
\\.\pipe\MSSQL$XXXXX_DB\sq
Hope this helps,
Best Regards,
Paulo Condeça
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
MrNetic,
I changed the server alias in the client network utility (= cliconfg I assume) to .\XXXXX_DB just to see if that would help.
It didn't, so I changed it back to SRV12\XXXXX_DB
But now I cannot connect to the db anymore using the local Enterprise Manager (still can do it through remote Enterprise Mgr)
Default pipe setting for named pipes = 'sql\query'
I changed the server alias in the client network utility (= cliconfg I assume) to .\XXXXX_DB just to see if that would help.
It didn't, so I changed it back to SRV12\XXXXX_DB
But now I cannot connect to the db anymore using the local Enterprise Manager (still can do it through remote Enterprise Mgr)
Default pipe setting for named pipes = 'sql\query'
Outin,
In the CLICONFIG, remove the alias SRV12\XXXXX_DB.
In the CLICONFIG, remove the alias SRV12\XXXXX_DB.
Sorry i took long time... Lunch :)
ASKER
MrNetic,
Thanks again for your efforts.
Removed the alias, didn't work (it only caused 1 of the 2 duplicate entries in the 'available servers' list to disappear.
Extra info: I called the guys who created the application depending on this server to see if they could help out.
I was told that since there are 2 databases running on the server one has to run on a different port, so I changed the server config so that TCP/IP is now on port 2000 for this troubled db. Not that it made a difference......
PS: It's 5 o'clock here...WEEKEND :-)
I might read this thread before monday, or not; I won't be disappointed if no further comments are made during the weekend....
Thanks again for your efforts.
Removed the alias, didn't work (it only caused 1 of the 2 duplicate entries in the 'available servers' list to disappear.
Extra info: I called the guys who created the application depending on this server to see if they could help out.
I was told that since there are 2 databases running on the server one has to run on a different port, so I changed the server config so that TCP/IP is now on port 2000 for this troubled db. Not that it made a difference......
PS: It's 5 o'clock here...WEEKEND :-)
I might read this thread before monday, or not; I won't be disappointed if no further comments are made during the weekend....
ASKER
We decided to reinstall Enterprise Manager.
After this, and an MS SQL SP4 update, it works again.
Still don't know why it broke down though.....
MrNetic, thanks for your efforts!
Regards,
Outin
After this, and an MS SQL SP4 update, it works again.
Still don't know why it broke down though.....
MrNetic, thanks for your efforts!
Regards,
Outin
ASKER
Solved part of the problem myself...
ASKER
Thanks for your comment; I created a server registration connecting to '.\XXXXX_DB' and now I can indeed connect to the db with local Enterprise Manager.
Problem that remains is the SQL Server Agent that won't start...(for error codes etc see original post)
Info that might help towards finding the problem: when creating a server registration using the 'Register SQL Server Wizard', the wizard shows a list of available servers. 'SRV12\XXXXX_DB' appears twice in this list, while other servers only appear once....
Best regards,
Outin