November 25, 2009 at 10:15 am
Just posting some experience, as I hope it will help others. I changed my SQL Server Agent service account to a domain account using SQL Server Configuration Manager (should always use this tool to manipulate service accounts), but then could not start the SQL Server Agent service. This was the fifth box I'd made this change and did not see any problems on the previous four. A couple of errors:
--------------------
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).
--------------------
SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.
--------------------
After some Googling, the top 5-6 posts said to enable the use of 'Agent XPs' using the sp_configure stored procedure. Made sense, as the error told me to do so also. But, when trying to enable Agent XPs, I would get "Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process." Those top 5-6 posts also said to change Windows Group Policy to allow the account running the SQL Server Agent service to 'lock pages in memory'.
Whoa! I went to my other servers where the SQL Server Agent service is running successfully and checked their Group Policy, none of the domain accounts I was using on those machines needed to have 'lock pages in memory' enabled. This raised a flag, as I do not want my servers configured differently.
I found that on my problematic server, the 'awe enabled' option in the sp_configure list had a 'config_value' of 1. The 'run_value' was still 0. Changing the 'config_value' back to 0 solved my problem. I only have 2gbs of memory on the box. SQL Server Books Online discusses the differences between the two values, as well as what the 'awe enabled' option does for the instance
Long winded, but hopefully helps others. Here's a list of various commands I ran during my troubleshooting:
-- by itself, shows options that are set
sp_configure
-- to enable all options to be shown
sp_configure 'show advanced options', 1;
reconfigure
go
-- to change value of Agent XPs
sp_configure 'Agent XPs', 1;
reconfigure
go
-- to change value of 'awe enabled'
sp_configure 'awe enabled', 1;
reconfigure
go
To view/change Windows Group Policy:
1. On the Start menu, click Run; in the Open box, type gpedit.msc.
2. The Group Policy dialog box opens.
3. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
4. Expand Security Settings, and then expand Local Policies.
5. Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 25, 2009 at 10:27 am
Thanks Dan,
This would be helpful sometime or the other.
Pavan.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply