Implications of removing the xp_cmdshell stored procedure from SQL Server 2000

Many Microsoft SQL Server 2000 administrators consider removing the xp_cmdshell stored procedure from SQL Server as part of their security plan. Although the extended stored procedure can be removed, removal has significant support and functionality implications. Examine other security settings before you make this system change.

Removing stored procedures during SQL Server 2000 hardening

Many third-party SQL Server 2000 hardening guides and security templates are publicly available. No third-party hardening guide has been tested with the original release version of SQL Server 2000 or with any later service packs or hotfixes. Therefore, you should perform extensive testing in your environment before you implement any new settings in a production environment.

Additionally, if the newer settings cause application outages or failure, you must be able to roll back settings or revert to default settings.

Note If you remove system-installed extended stored procedures, the system configuration will be an unsupported configuration. Microsoft will not fix bugs that occur because you removed extended stored procedures.

Administrators who harden SQL Server by making these changes will experience less functionality than the functionality of the original product.

For more information about SQL Server 2000 hardening, view the "SQL Server 2000 SP3 Security Features and Best Practices" guide. For more information, visit the following Microsoft Web site:

The xp_cmdshell stored procedure and accounts

By default, only system administrators can use the xp_cmdshell stored procedure. However, the following authentication settings allow for different levels of access to the xp_cmdshell stored procedure:

• Mixed Authentication
  When the server is set to Mixed Authentication, the following users can use the xp_cmdshell stored procedure:
  • Members of the Builtin\Administrators group
  • Users who know the sa login password
  Windows Authentication only
  Users who use the sa login password cannot use the xp_cmdshell stored procedure.
• Mixed security
  Mixed security authentication allows for both Windows Authentication and SQL Authentication. In this configuration, you should help protect the sa login password and any other SQL account that you create. Help protect these login passwords just as you help protect Windows passwords. Standard logins are encrypted if the server has a server authentication certificate installed.
  
  For more information about strong passwords, visit the following Microsoft Web site:
  http://www.microsoft.com/smallbusiness/support/articles/enforce_strong_passwords.mspx (http://www.microsoft.com/smallbusiness/support/articles/enforce_strong_passwords.mspx)

Also, the user rights of the account that is used to start the SQL Server service directly affects the permissions and the user rights that an attacker assumes if the attacker gains access to the system. The xp_cmdshell stored procedure runs under the context of the SQL Server service account.

We do not recommend that you let users use the xp_cmdshell stored procedure because this lets the user run any command that is under the context of the proxy account. To determine whether a server lets users access the xp_cmdshell stored procedure, examine the proxy account settings by using one of the following tools:

• SQL Server Enterprise Manager
• The Microsoft Baseline Security Analyzer (MBSA) tool. To download the MBSA tool, visit the following Microsoft Web site:
  http://technet.microsoft.com/en-us/security/cc184924.aspx (http://technet.microsoft.com/en-us/security/cc184924.aspx)

The xp_cmdshell stored procedure and other stored procedures

Many stored procedures call the xp_cmdshell stored procedure. The following table lists the components that will break if you remove the xp_cmdshell stored procedure.

How to drop the xp_cmdshell stored procedure

You must have System Administrator user rights to drop the xp_cmdshell stored procedure. To drop the stored procedure, type the following command at a command prompt:To re-add the xp_cmdshell stored procedure, type the following command at a command prompt:

The Xplog70.dll file and the xp_cmdshell stored procedure

If you only drop the xp_cmdshell stored procedure, this action may not deny access to the system. Another administrator can just re-add the xp_cmdshell stored procedure by using the sp_addextendedproc stored procedure.

If you want to remove the xp_cmdshell stored procedure from the system, you must also remove the Xplog70.dll file from the computer.

The Xplog70.dll file is also used for other extended stored procedures. If you remove the Xplog70.dll file, the server cannot call the following extended stored procedures:

• Xp_sscanf
• Xp_sprintf
• Xp_msver
• Xp_msver
• Xp_enumgroups
• Xp_logevent
• Xp_loginconfig

Note If you install a SQL Server 2000 service pack, the Xplog70.dll file may be replaced on the computer. Therefore, if you remove the Xplog70.dll file, the file may be reinstalled later.

You may receive error messages after you remove the Xplog70.dll file

After you remove the Xplog70.dll file, you may experience one of the following symptoms:

• When you call one of the extended stored procedures that are listed in the "The Xplog70.dll file and the xp_cmdshell stored procedure" section from an ODBC tool, you receive the following error message:
  ODBC: Msg 0, Level 16, State 1
  Cannot load the DLL xplog70.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
• You notice that Replication Wizard items other than Snapshot or Merge are unavailable. For example, Transactional Replication is unavailable. If you choose Merge or Snapshot replication in the wizard, you receive the following error message:
  Trying to configure Distributor for "server"
  Fails with Error 2812: Could not find stored procedure 'master..xp_cmdshell'
• The Replication Log Reader Agent utility fails when you use Transactional Replication, and you receive the following error message:
  Status: 0, code: 1007, text: 'Cannot load the DLL xplog70.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).'.
  Cannot load the DLL xplog70.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
  Repl Agent Status: 6
• The Index Tuning Wizard fails, and you receive the following error message:
  An error occurred trying to execute a SQL Statement.
• In the Make MSX Wizard, you receive the following error message on the Select Servers to Enlist page:
  "Cannot enlist server 'ServerName' . Please upgrade server 'ServerName' to SQL Server 2000 sp3 or later.
  A network trace reveals that the following error message is the real error message:
  “Cannot load the DLL xplog70.dll or one of the DLLs it references. Reason: 126(The specified module could not be found)
  ServerName xp_msver

For more information, visit the following Microsoft Web site: