Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server is 3179 or a later build

You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server is 3179 or a later build

Important This article contains information about how to modify the registry. Make sure that you back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 How to back up and restore the registry in Windows

↑ Back to the top

Symptoms

Consider the following scenario:
  • You install and then configure a failover cluster of Microsoft SQL Server 2005.
  • The build of SQL Server 2005 is 3179 or a later build.
  • The SQL Server Agent service startup account is a domain administrator user account.
In this situation, you cannot start the SQL Server Agent service. Additionally, the following error message is logged in the SQL Server Agent log (Sqlagent.out):
2007-10-10 10:46:24 - ! [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]
2007-10-10 10:46:24 - ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
Note The Sqlagent.out file is in following folder:
%ProgramFiles%\Microsoft SQL Server\MSSQL.Instance_Number\MSSQL\LOG

↑ Back to the top

Cause

This problem occurs because the SQL Server Agent service cannot connect to the SQL Server service.

When the SQL Server Agent service starts, it connects to the SQL Server service. The SQL Server Agent service runs the xp_sqlagent_notify stored procedure to notify the SQL Server Agent service of changes. While the xp_sqlagent_notify stored procedure is running, a call to a cryptographic API fails. Therefore, the SQL Server Agent service cannot connect to the SQL Server service.

↑ Back to the top

Resolution


The fix for this issue was first released in Cumulative Update 9. For more information about how to obtain this cumulative update package for SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
953752
Cumulative update package 9 for SQL Server 2005 Service Pack 2
Note
Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
937137
The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released
Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

↑ Back to the top

Workaround

To work around this problem, consider each of the following methods and use the one that is a best fit to your environment:

Method 1

To work around this problem, log on to the computer by using a user account that has sufficient credentials to connect to SQL Server 2005 and to run all the jobs that are created. For example, log on as a user who is a member of the local administrators group. Then, change the SQL Server Agent startup account to a non-domain administrator user account. The user account must have the appropriate rights. For example, the user should be a member of the local administrators group.

To do this, follow these steps:
  1. Click Start, click Programs, click Microsoft SQL Server 2005, click Configuration Tools, and then click SQL Server Configuration Manager.
  2. Click SQL Server 2005 Services.
  3. Right-click SQL Server Agent (Instance_Name), and then click Properties.
  4. Click the Log On tab, enter the user logon information, and then click OK.
  5. Right-click SQL Server Agent (Instance_Name), and then click Restart. If the SQL Server Agent service is not already running, right-click SQL Server Agent (Instance_Name), and then click Start.

Method 2

In the Active Directory Users and Computers snap-in, enable the SQL Server service account to be trusted for delegation.

Note You do not have to restart any resource after you enable the SQL Server service account to be trusted for delegation. For more information about how to enable a service account to be trusted for delegation, visit the following Microsoft Web site:
http://technet2.microsoft.com/WindowsServer/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx?mfr=true

Method 3

Configure SQL Server Agent to connect the SQL Server service by using the named pipes protocol. To do this, follow these steps on each cluster node:
  1. Open SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, click SQL Native Client Configuration, right-click Aliases, and then click New Alias.
  3. In the Alias - New dialog box, select Named Pipes in the Protocol list.
  4. In the Alias Name box, specify the name of the alias.
  5. In the Server box, specify the instance of SQL Server 2005, and then click OK.
  6. Open SQL Server Management Studio, and then connect the instance of SQL Server 2005.
  7. Right-click SQL Server Agent, and then click Properties.
  8. In the SQL Server Agent Properties dialog box, click Connection.
  9. In the Alias local host server box, type the name of the alias that you specified in step 4, and then click OK.
  10. In SQL Server Management Studio, right-click SQL Server Agent, and then click Restart.

If the named pipe protocol is not enabled, you must enable the named pipe protocol. For more information about how to enable the named pipe protocol, visit the following Microsoft Web site:
http://msdn.microsoft.com/en-us/library/ms191294(SQL.90).aspx
If the instance of SQL Server 2005 is not configured to listen on a named pipe, you must configure the instance to listen on the named pipe. For more information, visit the following Microsoft Web site:
http://msdn.microsoft.com/en-us/library/ms189321(SQL.90).aspx
Note After you add the alias, all client applications that locally connects to the instance use the named pipes protocol for connection.

↑ Back to the top

References

For more information about the list of builds that are available after SQL Server Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
937137
The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released
For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
935897
An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
For more information about how to obtain SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
913089
How to obtain the latest service pack for SQL Server 2005
For more information about the new features and the improvements in SQL Server 2005 Service Pack 2, visit the following Microsoft Web site:
http://go.microsoft.com/fwlink/?LinkId=71711
For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
822499
New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684
Description of the standard terminology that is used to describe Microsoft software updates

↑ Back to the top

Keywords: kbsql2005error, kbexpertiseadvanced, kbtshoot, kbprb, kb, misc_migrate_32718

↑ Back to the top

Article Info
Article ID : 943525
Revision : 2
Created on : 4/13/2018
Published on : 4/13/2018
Exists online : False
Views : 519