Solved

Cannot start SQLServerAgent 2005 service

Posted on 2011-02-23
9
788 Views
Last Modified: 2012-05-11
I have a member server in a Win2003 domain. The OS is Win2008 R2 Fdn with MSSQL 2005 Std installed. I use the domain administrator account to logon to the computer.
I have been unsuccessful at starting the SQLServerAgent service on the computer.
All other SQLServer services run without problem. Logon to all these services is via Windows authentication, using the domain administrator account.

When starting the SQLServerAgent from Management Studio, the error message is
"The SQLServerAgent service on ... started and then stopped.."
I also noticed the remark in parenthesis, "Agent XPs disabled" - what does that mean?

The event log shows reason as : "SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role" Error ID 103.

I checked and can confirm that the domain administrator account has been included in the sysadmin role. So why Unknown?

I tried to start the service from the services MMC, and also changed the logon to Local System account. I also tried to start from the command prompt with -c parameter. But all without success.

Would be grateful if an expert can help me out on this one. Thanks
0
Comment
Question by:garychu
  • 6
  • 3
9 Comments
 
LVL 2

Accepted Solution

by:
Jeadien earned 500 total points
ID: 34967199
Two things are coming to mind.

#1  Add the account you're using to the two SQL Server groups in your computer management.

#2  Run the SQL Server Surface Area tool to ensure that your account is setup as a SysAdmin role in SQL server.

MS SQL uses different access control lists than those built-in to windows/AD.
0
 

Author Comment

by:garychu
ID: 34968563
Thanks, Jeadien.
#1 Not sure what is meant by adding to the two SQL Server groups.
All I seem to be able to do is add the Windows logon account to sysadmin role under security in Management studio.
#2 I ran the Surface Area tool to grant sysadmin privileges to the Windows account.
It allows me to do so. But on exiting the tool and returning to it again, found it was not saved!

Some guidance on how to achieve the above would be very appreciated.
My knowledge of MSSQL is limited to MSSQL 2000 only.

Thanks
0
 
LVL 2

Expert Comment

by:Jeadien
ID: 34968657
On the surface area tool --  did you right-click, run as administrator?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:garychu
ID: 34968694
Hi again, Jeadien.
Not sure if this is what is meant.
#1 In Server Manager > Local Users and Groups
I added the logon account into the  SQLServer2005SQLAgentUser.... group.
Then
#2 In Surface Area tool, granting sysadmin privileges to the logon account still does not seem to take effect!
Consequently, I still could not start SQLServerAgent for lack of privileges...
0
 

Author Comment

by:garychu
ID: 34974654
Hi, Jeadien.
My last post crossed with yours.
When running Surface Area tool, I did not right click to run as administrator.
Run as local administrator? This is quite new for me because having joined this particular SQL server to the domain, I had not thought it necessary to do so beyond logon as domain administrator.
I am beginning to see why this is so, given that MSSQL uses a different ACL.

I will give it a go at the first opportunity and revert.
0
 
LVL 2

Expert Comment

by:Jeadien
ID: 34975847
garychu,

even if you're logged in as the domain administrator, Vista/7/2k8 will require elevation of privileges for certain tasks.  If that doesn't work, then i would recommend uninstalling/reinstalling SQL 2005, and make sure that you configure accounts at the end of setup using the surface area configuration tool.
0
 

Author Comment

by:garychu
ID: 34975976
Jeadien,

I have now tried starting the Surface Area tool by right clicking and run as administrator.
Adding sysadmin privileges to the logon account still does not seem to take effect.
Without running in an elevated administrator command prompt, how could I tell if in fact it was so?
Is there a way to run the Surface Area tool via the command prompt - just to be sure?
I really hate to have to uninstall/re-install MSSQL!
0
 

Assisted Solution

by:garychu
garychu earned 0 total points
ID: 34989415
Hi Jeadien,

I think I have finally resolved this issue.
I concluded that the problem was not the windows logon account not having sufficient privileges.
After runnning sqlprov.exe I checked the sqlprov.log and found that among other things that  "..User is already a member of the Administrators role".
I took as a clue that in Management Studio, the SQLAgent service was stopped because Agent XPs has been disabled. ( I still don't know what Agent XPs does ).
Then;
1. I temporarily disabled AWE
2. Ran the following script;
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Agent XPs',1
go
reconfigure with override
go
sp_configure 'show advanced options',0
go
reconfigure with override
go
3. Re-enable AWE

Following that I was able to start SQLAgent service from Configuration Manager.

I would appreciate any further suggestions and advice from you before closing this question.
Thanks for your help.
0
 

Author Closing Comment

by:garychu
ID: 35171017
Set on the right diagnostic path by Expert, even though I finally found my own solution.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question