I'm unable to connect to my local server after I accidentally ended some SQL Server related tasks in Task Manager.
I found out that my my SQL Server Agent is not running, and unable to make it to run, below is the message shown.
Any idea about resolving the problem?
2016-02-25 22:35:09 - ? [100] Microsoft SQLServerAgent version 12.0.2000.8 (X64 unicode retail build) : Process ID 8736
2016-02-25 22:35:09 - ? [495] The SQL Server Agent startup service account is NT AUTHORITY\LOCAL SERVICE.
2016-02-25 22:35:09 - ? [393] Waiting for SQL Server to recover database 'msdb'...
2016-02-25 22:35:09 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2016-02-25 22:35:09 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2016-02-25 22:35:09 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
2016-02-25 22:35:09 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2016-02-25 22:35:09 - ? [098] SQLServerAgent terminated (normally)
5条答案
按热度按时间h4cxqtbf1#
Thanks everyone for answering my question!
I eventually solve the problem by uninstalling and reinstalling it again.
For this time, I go on Control Panel > Programs and Features > and choose on Microsoft SQL Server 2014 to be uninstalled, not sure why it didnt work this way for my previous reinstall, but it works for now!
bqucvtff2#
Based on the error messages in your Sql Agent's log file, it appears that the SQL Engine is starting, at least. Otherwise, you wouldn't be seeing messages about execute permissions being denied (instead you'd probably be seeing some messages about not being able to contact the server).
What you need to do now is either: Grant the user account that is being used to run the SQL Agent the rights it needs, or switch to an account that already has those rights.
You can see from your log file that the account current being used is NT AUTHORITY/LOCAL SERVICE. To change the user account to something else: Open Control Panel -> Administration -> Services, right- click on the SQL Server Agent service, select Properties, click the Log On tab, and change to a different user account.
To grant permissions to the existing account: Open SQL Server Management Studio, and log in to your database with a working account. Navigate to Security -> Logins, right click on the name of the user that currently runs SQL Server Agent, and select Properties.... for absolute minimum permissions to run the Agent, head to User Mapping, select the MSDB database, and pick one of the SQL Agent fixed accounts ( as described here ). (Alternatively, you can pick Server Roles, set it as a sysadmin and call it a day--but only if you don't care too much about security on this server...)
km0tfn4u3#
Please always use the SQL Server 2008 Configuration Manager to open SQL service. Never use windows service to open SQL services.
wvt8vs2t4#
Just to add to the list of lessons learned...
Sql Server Configuration Manager -> then aliases
During some documentation, trying to create a ODBC file connection (just for screenshots), had actually also created a lot of aliases that redirected all connections to odd places.
Deleting all of the aliases (don't do this unless you know what you are doing, or are on e dev/test system), actually helped.
9lowa7mx5#
YOU CAN RESTART SQLSERVER if you get errors from shutting down using the task manager.
Snapshot of SQLSERVER Configuration Manager