November 26, 2012 at 1:29 am
Hi,
I've recently changed our SQL services to run under domain account instead of the local account they used to use. Since doing this i'm unable to start the agent. I got the follwing error in event viewer
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).
The account we are trying to start the agent with is both a local admin and has sysadmin role.
One thing i find strange is that when we connect to the instance using the service account and sql managent i can't see the properties of the agent as it's greyed out when i right click on it.
Any ideas on this - i've tried going back and running the agent as local but thet now returns the same error !
Thanks in advance,
Mike.
November 26, 2012 at 2:26 am
mike.whorley (11/26/2012)
Hi,I've recently changed our SQL services to run under domain account instead of the local account they used to use. Since doing this i'm unable to start the agent. I got the follwing error in event viewer
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).
The account we are trying to start the agent with is both a local admin and has sysadmin role.
One thing i find strange is that when we connect to the instance using the service account and sql managent i can't see the properties of the agent as it's greyed out when i right click on it.
Any ideas on this - i've tried going back and running the agent as local but thet now returns the same error !
Thanks in advance,
Mike.
Does the domain account have "permission to logon as a service" rights on the server?
November 26, 2012 at 2:40 am
Mike
It sounds as if you didn't use SQL Server Configuration Manager to make the change. Maybe you used the Services applet instead? Try using SQL Server Configuration Manager to change to Local System or something like that, then change back to your domain account.
John
November 26, 2012 at 6:51 am
learning_sql (11/26/2012)
Does the domain account have "permission to logon as a service" rights on the server?
Yes it does - just double checked it in user rights assignment in local policies on the server.
November 26, 2012 at 6:58 am
John Mitchell-245523 (11/26/2012)
MikeIt sounds as if you didn't use SQL Server Configuration Manager to make the change. Maybe you used the Services applet instead? Try using SQL Server Configuration Manager to change to Local System or something like that, then change back to your domain account.
John
Thanks John - i didn't use the config manager, however I've tried your suggestions but sadly just the same, local user or domain user return exactly the same error as previous.
November 26, 2012 at 7:01 am
Sounds like you might have to do it manually, then. Try creating a login for your domain account and adding it to sysadmin. Then use SQL Server Configuration Manager to change the service to start under that account.
John
November 26, 2012 at 7:14 am
Sorry John i'm not quite sure what you are saying there ?
The service account does have a login to the instance already with sysadmin role.
November 26, 2012 at 7:18 am
Ah yes, so it does. I'm running out of ideas now. How about creating a named local Windows account and seeing if you can start SQL Server Agent as that?
John
November 26, 2012 at 7:21 am
Is the domain service account a member of the local group "SQLserveragentusers$SERVERNAME$INSTANCE" or similar
EDIT - this maybe useful but we may have covered it:http://www.mssqltips.com/sqlservertip/2317/running-sql-server-agent-with-a-least-privilege-service-account/
November 26, 2012 at 8:12 am
John Mitchell-245523 (11/26/2012)
Ah yes, so it does. I'm running out of ideas now. How about creating a named local Windows account and seeing if you can start SQL Server Agent as that?John
Created a new account and just made it a member for the SQLAGENT group as detailed above but still no joy.
My hunch is something in group policy is revoking the privs we are giving the domain account on this server.
November 27, 2012 at 2:31 am
We have a test system that's pretty much a clone of this live system.
On this node i did nothing more than add the service account to the sqlagent group and then restart the agent with the same domain service account credentials and it worked fine.
I've stripped everything back on the live server and just left the service account in the sqlagent group and still the same error - head and brick wall are going to come together soon.....
November 27, 2012 at 3:25 am
Hi,
Here are the informational messages arriving before the error - not sure if these are red herrings or not.
SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' 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.
Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.
Regards,
Mike.
November 27, 2012 at 3:32 am
Mike
Where are those messages appearing? Are you getting anything in SQLAGENT.OUT?
John
November 27, 2012 at 3:45 am
John Mitchell-245523 (11/27/2012)
MikeWhere are those messages appearing? Are you getting anything in SQLAGENT.OUT?
John
Cheers John - here it is
2012-11-27 10:19:02 - ! [298] SQLServer Error: 5845, Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process. [SQLSTATE 42000] (DisableAgentXPs)
2012-11-27 10:19:02 - ! [298] SQLServer Error: 15281, SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' 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. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2012-11-27 10:19:02 - ! [298] SQLServer Error: 15281, 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. [SQLSTATE 42000]
2012-11-27 10:19:02 - ? [100] Microsoft SQLServerAgent version 9.00.5000.00 (x86 unicode retail build) : Process ID 5288
2012-11-27 10:19:02 - ? [101] SQL Server version 9.00.5057 (0 connection limit)
2012-11-27 10:19:02 - ? [102] SQL Server ODBC driver version 9.00.5000
2012-11-27 10:19:02 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
2012-11-27 10:19:02 - ? [310] 2 processor(s) and 4096 MB RAM detected
2012-11-27 10:19:02 - ? [339] Local computer is SRV-HAR-APPLIX7 running Windows NT 5.2 (3790) Service Pack 2
2012-11-27 10:19:02 - ! [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role
2012-11-27 10:19:02 - ? [098] SQLServerAgent terminated (normally)
November 27, 2012 at 5:49 am
Sounds like you need to grant the service account the lock pages in memory right.
John
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply