PRB: Utilizing xp_cmdshell with Non-Sysadmin Accounts in a Clustered Environment (248407)

SYMPTOMS

When running xp_cmdshell, with a sysadmin account, the process runs under the context of the SQL Server service account. If you run xp_cmdshell with a non-sysadmin account the process then uses the SQLAgentCmdExec Windows NT account created during setup.

In a clustered environment xp_cmdshell tasks/jobs fail with an error if they are run by a member not associated with the sysadmin server role on the node to which that instance of SQL was not installed. The error is similar to this:

Msg 50001, Level 1, State 50001

xpsql.c: Error 5 from LogonUser on line 476

CAUSE

During SQL setup a local Windows NT account, SQLAgentCmdExec, is created with a randomized password.

In a clustered environment, again, in the SQL setup, the SQLAgentCmdExec account is created on the local computer, hence the computer onto which SQL is installed.

For a cluster composed of member servers the task fails if run by a non-sysadmin as the SQLAgentCmdExec is not set up as an account on the passive node.

Even if you were to set up an Active/Active SQL cluster, two different SQLAgentCmdExec accounts are created. In this case, each account is associated with a different SID at the Windows NT level and each account has a separate password.

For a cluster composed of domain controllers (primary domain controller [PDC] or a backup domain controller [BDC] or two BDCs) the Windows NT servers share the same SAM database and therefore local accounts are seen as "shared" accounts between both nodes and hence one SQLAgentCmdExec account is created for both nodes.

This is an issue with the way we store information for the SQLAgentCmdExec within SQL and with the Windows NT 4.0 domains structure.

WORKAROUND

To work around this problem use one of the following workarounds:

In a clustered environment, set up the nodes as domain controllers, which allows the SQLAgentCmdExec account and password to be shared between both nodes of the cluster.

-or-
Only allow tasks/jobs that are utilizing xp_cmdshell to be executed by members of the sysadmin server role.