Implications of removing the xp_cmdshell stored procedure from SQL Server 2000

Article translations Article translations
Article ID: 891984 - View products that this article applies to.
Expand all | Collapse all

On This Page

SUMMARY

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.

MORE INFORMATION

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:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx

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
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:

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.
Collapse this tableExpand this table
Stored procedurePurpose
sp_ActiveDirectory_SCPAdd, change, or delete Active Directory directory service objects
sp_adddistpublisherReplication
sp_adddistributiondbReplication
sp_attachsubscriptionReplication
sp_changedistpublisherReplication
sp_copysubscriptionReplication
sp_MScopysnapshotReplication
sp_MScopyscriptfileReplication install
sp_MSget_file_existenceReplication install
sp_MSremove_userscriptReplication install
sp_replicationoptionReplication
sp_vupgrade_replicationReplication install
sp_MSreplremoveuncdirReplication called from distribution database
sp_MSdeletefoldercontentsReplication called from distribution database
sp_resolve_loginsLog shipping
Sp_set_local_timeMSDB
sp_msx_defectMultiServer administration
sp_msx_enlistMultiServer administration

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:
exec sp_dropextendedproc 'xp_cmdshell'
To re-add the xp_cmdshell stored procedure, type the following command at a command prompt:
exec sp_addextendedproc 'xp_cmdshell', 'xplog70.dll'

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

REFERENCES

For more information, visit the following Microsoft Web site:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx

Properties

Article ID: 891984 - Last Review: November 2, 2007 - Revision: 1.4
APPLIES TO
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
Keywords: 
kbinfo KB891984

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