MSSQLWIKI

SQLServer agent resource fails to come online on cluster after upgrading to build 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. http://support.microsoft.com/?id=956378
Note: Before applying the Hotfix. you have to follow the  steps mentioned in Resolution else hotfix would fail. Revert the steps after applying the fix.

Regards

Karthick PK