Unable to start SQLServer agent resource on cluster after upgrading to 9.00.3186 or Higher
Error
===
2008-03-19 23:06:23 - ? [100] Microsoft SQLServerAgent version 9.00.3186.00 (x86
unicode retail build) : Process ID 2428
2008-03-19 23:06:23 - ? [101] SQL Server SNETNAME version 9.00.3186 (0 connection
limit)
2008-03-19 23:06:23 - ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-19 23:06:23 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is
2008-03-19 23:06:23 - ? [310] 4 processor(s) and 2560 MB RAM detected
2008-03-19 23:06:23 - ? [339] Local computer is SNETNAME running Windows NT 5.2
(3790) Service Pack 2
2008-03-19 23:06:23 - ? [432] There are 11 subsystems in the subsystems cache
2008-03-19 23:06:38 - ! [364] The Messenger service has not been started - NetSend
notifications will not be sent
2008-03-19 23:06:38 - ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-19 23:06:38 - + [260] Unable to start mail session (reason: No mail profile
defined)
2008-03-19 23:06:38 - + [396] An idle CPU condition has not been defined - OnIdle
job schedules will have no effect
2008-03-19 23:06:38 - + [408] SQL Server MSSQLSERVER is clustered - AutoRestart has
been disabled
2008-03-19 23:06:39 - ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146893813, 'Key not valid for use in specified state.' [SQLSTATE 42000]
2008-03-19 23:06:39 - ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-19 23:06:40 - ? [098] SQLServerAgent terminated (normally)
Error2
=====
2008-03-18 12:18:30 - ? [100] Microsoft SQLServerAgent version 9.00.3200.00
((Unknown) unicode retail build) : Process ID 6512
2008-03-18 12:18:30 - ? [101] SQL Server PISTONDIST version 9.00.3200 (0 connection
limit)
2008-03-18 12:18:30 - ? [102] SQL Server ODBC driver version 9.00.3042
2008-03-18 12:18:30 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local
host server is np:pistondist
2008-03-18 12:18:30 - ? [310] 16 processor(s) and 32765 MB RAM detected
2008-03-18 12:18:30 - ? [339] Local computer is PISTONDIST running Windows NT 5.2
(3790) Service Pack 2
2008-03-18 12:18:31 - ? [432] There are 11 subsystems in the subsystems cache
2008-03-18 12:18:31 - ! [364] The Messenger service has not been started - NetSend
notifications will not be sent
2008-03-18 12:18:31 - ? [129] SQLSERVERAGENT starting under Windows NT service
control
2008-03-18 12:18:32 - + [396] An idle CPU condition has not been defined - OnIdle
job schedules will have no effect
2008-03-18 12:18:32 - + [408] SQL Server MSSQLSERVER is clustered - AutoRestart has
been disabled
2008-03-18 12:18:32 - + [162] Internal request (from SetJobNextRunDate [reason:
schedule will not run again]) to deactivate schedule 66
2008-03-18 12:18:32 - ! [298] SQLServer Error: 22022, CryptUnprotectData() returned
error -2146892987, 'The requested operation cannot be completed. The computer must
be trusted for delegation and the current user account must be configured to allow
delegation.' [SQLSTATE 42000]
2008-03-18 12:18:32 - ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2008-03-18 12:18:33 - ? [098] SQLServerAgent terminated (normally)
Resolution
========
Modify the the following Key.
=======================
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent
Modify the value data of the serverhost key to np:Virtualservername
Ie:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.X\SQLServerAgent
ServerHost
Value: np:Virtualservername.
This will force the SQLServer agent to connect with SQLserver using Named Pipes so
delegation is not used.
-----------------------------------------------------------------------------------------------------------------
We have a HOTFIX available for this issue and it is included in the cumulative update pack9 for SQLServer service pack2. https://support.microsoft.com/?id=956378
Note: Before applying the Hotfix. you have to follow the steps mentioned in Resolution else hotfix would fail.
Regards
Karthick PK