Tuesday, January 28, 2014

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2) in SQL Server

Installed SQLServer 2012 Express Edition with Named Instance. When trying to startup the SQL Server Agent to setup the backups, the agent failed with below error

The request failed or the service did not respond in a timely fashion.
Consult the event log or other applicable error logs for details 


When I looked into Event log I found below error message, its not much useful.

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2). 

I looked into the SQLAGENT log and found below error messages

2014-01-28 09:33:07 - ? [100] Microsoft SQLServerAgent version 11.0.2100.60 (X64 unicode retail build) : Process ID 1848
2014-01-28 09:33:07 - ? [495] The SQL Server Agent startup service account is SQL-Domain1\SQL-SER1$.
2014-01-28 09:33: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)
2014-01-28 09:33:09 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
2014-01-28 09:33:09 - ? [098] SQLServerAgent terminated (normally)

I have changed the SQL Server Agent logon account to administrator and ran below command to enable AgentXPs option and restarted the SQL Server Agent, but still no luck

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0;
GO

When I looked into SQLAGENT log and found out that SQLServer Express edition does not support SQL Server Agent

2014-01-28 10:00:31 - ? [100] Microsoft SQLServerAgent version 11.0.2100.60 (X64 unicode retail build) : Process ID 1684
2014-01-28 10:00:31 - ? [495] The SQL Server Agent startup service account is SQL-Domain1\Administrators.
2014-01-28 10:00:31 - ? [000]
2014-01-28 10:00:31 - ? [101] SQL Server SQL-SER1\INSTANCE1 version 11.00.2100 (0 connection limit)
2014-01-28 10:00:31 - ? [102] SQL Server ODBC driver version 11.00.2100
2014-01-28 10:00:31 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is SQL-SER1\INSTANCE1
2014-01-28 10:00:31 - ? [310] 4 processor(s) and 8192 MB RAM detected
2014-01-28 10:00:31 - ? [339] Local computer is SQL-SER1running Windows NT 6.2 (9200)
2014-01-28 10:00:31 - ! [000] This installation of SQL Server Agent is disabled. The edition of SQL Server that installed this service does not support SQL Server Agent.
2014-01-28 10:00:31 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2014-01-28 10:00:31 - ? [098] SQLServerAgent terminated (normally)


I forgot that I was working on SQLServer Express Edition...:) I hope this post is useful for users, who face  similar situation.

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com

No comments:

Post a Comment