There is a system extended store procedure called “xp_cmdshell” that can be used to issue shell commands using TSQL code. Being able to do this can be quite useful for a number of different situations. But when SQL Server is first installed out of the box this extended stored procedure is not enabled. Additionally once you enable it the default behaviors is that it is only available to the logins that have System Admin permissions. There may be times when you want to allow non-System Admin logins to be able to execute the xp_cmdshell extended stored procedure. In this article I will show you how to setup xp_cmdshell so non-System Admins can use this extended stored procedure.
Enabling xp_cmdshell
Out of the box xp_cmdshell is disabled. If you want to use xp_cmdshell you need to enable it. There are a number of ways to enable xp_cmdshell. One of the ways to enable xp_cmdshell is to use the “sp_configure” extended stored procedure using the following TSQL code:
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO
Another way to enable xp_cmdshell is to use Policy Based Management. For more information on this method review Books Online documentation.
Default Security Rights for xp_cmdshell Operations
When you first enable xp_cmdshell it can only be executed by members of the “sysadmin” server role. Additionally when the xp_cmdshell process executes it spawns a Windows process that runs using the same credentials as the SQL Server Service account. In addition, logins that are not a member of the sysadmin server role can’t run xp_cmdshell. This is normally a good thing, but the purpose of this article is to show you how to setup SQL Server so non-admins can use xp_cmdshell.
In order for non-admin logins to execute xp_cmdshell you need to create an xp_cmdshell proxy account.
Creating Proxy Credentials for xp_cmdshell
By default xp_cmdshell can only be used by members of the server role sysadmin. If you want logins that are not members of the sysadmin group to use xp_cmdshell you can do this by setting up proxy credentials for xp_cmdshell. By default the credentials for xp_cmdshell are turned off. Therefore in order to enable proxy credentials you need to run the system extended stored procedure named “sp_xp_cmdshell_proxy_account”. Here is the syntax for this extended stored procedure:
sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ]
Where “account_name” is a Windows login. Here is an example of how to create a xp_cmdshell proxy account for a Windows Account:
EXEC sp_xp_cmdshell_proxy_account [MyDomainSQLServerProxy], 'usdcu&34&23'
In this example I have create an xp_cmdshell proxy account for the user “SQLServerProxy” in domain “MyDomain” with a password of “uscu&34&23”.
You can also use the extended stored procedure “sp_xp_cmdshell_proxy_account” to disable the proxy account for xp_cmdshell. To do this you would execute the following command:
EXEC sp_xp_cmdshell_proxy_account NULL;
When you don’t pass any parameters to sp_xp_cmdshell_proxy_account, this tells SQL Server to remove the proxy account.
By setting up a proxy account, a credential is created named “##xp_cmdshell_proxy_account##”. SQL Server will connect to Windows with the account stored in this credential when xp_cmdshell is executed by a login that is not a member of the “sysadmin” server role. Additionally when SQL Server spawns a Windows command shell process via xp_cmdshell, that shell process is run using the Windows credentials stored in the “##xp_cmdshell_proxy_account##’. This is a good thing because this allows you to identify different security profiles for sysadmin and non-admin login, because different Windows accounts are used for each.
Problems You Might Run into When Setting up Proxy Credentials
Sometimes when setting up the sp_cmdshell proxy account you can will find that non-SysAdmin users still have problems when they try to exec xp_cmdshell, sometimes they don’t . Some of this is because you might find your local policies on your SQL Server box are locked down. In this section I’ve tried to identify a number of different errors I have run across.
If a non-sysadmin user should try to execute xp_cmdshell prior to creating an xp_cmdshell proxy account you will see this error message:
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential
exists and contains valid information.
After you set up a proxy account your non-sysadmin logins might still not be able to use xp_cmdshell. If you have not granted your non-sysadmin user EXECUTE permissions on xp_cmdshell you will get this error:
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
To overcome this error all you have to do is make sure the non-sysadmin user has a database user in the master database and then GRANT your non-sysadmin user the rights to execute xp_cmdshell. In order to do that all you have to do is run the following TSL code:
USE master; GO CREATE USER [MyDomainDorothy] FOR LOGIN [MyDomainDorothy]; GRANT EXECUTE ON xp_cmdshell TO [MyDomainDorothy];
Once you have given your non-sysadmin users execution rights to to xp_cmdshell you still might get this error when you execute xp_cmdshell:
"Msg 15121, Level 16, State 10, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'CreateProcessAsUser' failed with error code: '1314'.
If you use your favorite search engine for information about this error you will find numerous hits about this error on different SQL Server community websites. The reason you get this error is because the service account that SQL Server is running under doesn’t have enough local security policies rights on the server. I’ve found one knowledgebase article, that is rather old that explains the different local security policies that your proxy account might need, to avoid getting this error. Here a link to this knowledgebase article: http://support.microsoft.com/kb/248391/en-us
Review the local security policies identified in this article. If you find that your proxy account doesn’t have one of these rights then that might be why you are getting the above error.
Appropriate Security Access
Getting xp_cmdshell running for non-System staff is not as straight forward as Books Online makes it sound. Especially when your security staff has a tight control on local policies. Don’t be tempted to drop logins that don’t need System Admin rights into the sysadmin role just so they can execute xp_cmdshell. It is always better to determine the appropriate security access for the given tasks at hand. Therefore if you need non-sysamin logins to execute xp_cmdshell then work through the different issues with setting up an xp_cmdshell proxy account.
