BUG: You may receive an "Access is denied" error message when a query calls an extended stored procedure after you upgrade from SQL Server 2000 to SQL Server 2000 Service Pack 4

Article translations Article translations
Article ID: 887165 - View products that this article applies to.
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:
256986 Description of the Microsoft Windows registry
Expand all | Collapse all

On This Page

SYMPTOMS

After you upgrade a Microsoft SQL Server 2000-based computer to Microsoft SQL Server 2000 Service Pack 4 (SP4), a SQL Server query that calls a registry extended stored procedure may fail, and you receive the following error message:
Access is denied.
You receive the error message when you use any one of the registry extended stored procedures in the following list:
  • xp_regread
  • xp_instance_regread
  • xp_regenumkeys
  • xp_instance_regenumkeys
  • xp_regenumvalues
  • xp_instance_regenumvalues
  • xp_regwrite
  • xp_instance_regwrite
  • xp_regaddmultistring
  • xp_instance_regaddmultistring
  • xp_regremovemultistring
  • xp_instance_regremovemultistring
  • xp_regdeletevalue
  • xp_instance_regdeletevalue
  • xp_regdeletekey
  • xp_instance_regdeletekey

CAUSE

This problem occurs because SQL Server 2000 SP4 implements a restriction on which registry paths the registry extended stored procedures can access. When a query calls a registry extended stored procedure, the registry extended stored procedure examines the requested registry subkey against a control list of registry hives. If the registry subkey that the query requests is not in the control list, you receive the following error message:
'Access is denied.'
This error message has nothing to do with the permissions of the SQL Server user account in the context of which one of these registry extended stored procedures is called. The error message occurs even if the user has Administrator permissions.

Note When you receive the error message, the registry extended stored procedure returns no results.

RESOLUTION

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

Warning The registry extended stored procedures are undocumented and are subject to change. Therefore, we do not recommend that you call these procedures in your client code. Future implementations of these procedures may not be supported. Additionally, there is no guarantee that past implementations will continue to be supported in later versions of SQL Server.

To resolve this problem, modify the registry key that controls access to the registry extended stored procedures. The control list is located in the registry under the MSSQLServer subkey. For a default instance of SQL Server 2000, locate the following subkey in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\ExtendedProcedures
Note This registry key is not created when you apply SQL Server 2000 SP4 to a clustered instance of SQL Server.

For a named instance of SQL Server 2000, locate the following subkey in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance_name>\MSSQLServer\ExtendedProcedure


Note Instance_name is the name of the SQL Server 2000 named instance.
In the ExtendedProcedure subkey, there are two REG_MULTI_SZ registry entries:
  • Xp_regread Allowed Paths
  • Xp_regwrite_Allowed Paths
The Xp_regread Allowed Paths entry contains the following list of allowed registry values for read-only registry extended stored procedures:
  • xp_regread
  • xp_instance_regread
  • xp_regenumkeys
  • xp_instance_regenumkeys
  • xp_regenumvalues
  • xp_instance_regenumvalues
The Xp_regwrite Allowed Paths entry contains the following list of allowed registry values for write registry extended stored procedures:
  • xp_regwrite
  • xp_instance_regwrite
  • xp_regaddmultistring
  • xp_instance_regaddmultistring
  • xp_regremovemultistring
  • xp_instance_regremovemultistring
  • xp_regdeletevalue
  • xp_instance_regdeletevalue
  • xp_regdeletekey
  • xp_instance_regdeletekey
To allow access to the registry subkey that you want, you can either delete the Xp_regread Allowed Paths entry or the Xp_regwrite_Allowed Paths entry, or delete both entries. Additionally, you can modify the values that are in the Xp_regread Allowed Paths entry or the Xp_regwrite_Allowed Paths entry and add the extended stored procedure that you want the query to access. If you delete both the Xp_regread Allowed Paths entry and the Xp_regwrite Allowed Paths entry, you disable the restriction for the registry values. Therefore, the registry extended stored procedure behavior reverts to the pre-SQL Server 2000 SP4 behavior. To achieve the same result, you can remove the ExtendedProcedures registry key. We recommend that you extend each list with valid registry values instead of disabling the restriction that SQL Server 2000 SP4 implements.

Xp_regread allowed paths

The following is a sample for the Xp_regread Allowed Paths entry.

Note INSTANCE is replaced with the actual name of an instance of SQL Server.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<INSTANCE>


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLServer


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Messaging Subsystem


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\MSSQLServer


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\SQLCTRS


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\SQLServer


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$<INSTANCE>


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SNMP\Parameters\ExtensionAgents


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServer


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlset\Services\SQLAgent$<INSTANCE>


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation


HKEY_CURRENT_USER\Software\Microsoft\Mail HKEY_CURRENT_USER\Control Panel\International

Xp_regwrite allowed paths

The following is a sample for the Xp_regwrite Allowed Paths entry.

Note INSTANCE is replaced with the actual name of an instance of SQL Server.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<INSTANCE>


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlset\Services\SQLAgent$<INSTANCE>


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication

MORE INFORMATION

When you install SQL Server 2000 Service Pack 4, the installation program pre-populates both the Xp_regread Allowed Paths registry entry and the Xp_regwrite Allowed Paths registry entry with the extended stored procedures that SQL Server 2000 can access.

For more information about SQL Server 2000 service packs, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/sqlserver/bb331754.aspx

Properties

Article ID: 887165 - Last Review: November 16, 2007 - Revision: 2.5
APPLIES TO
  • Microsoft SQL Server 2000 Service Pack 4
Keywords: 
kbinfo kbsqlserv2000sp4fea kbtshoot kbbug KB887165

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com