In SQL Server, xp_cmdshell is an extended stored procedure that is stored in the master database that would allow us to execute Windows command shell. By default, the ability to execute xp_cmdshell is disabled. We could enable the ability to run xp_cmdshell by executing the following query against the SQL Server instance:
USE master;
GO
EXEC sp_configure 'show advanced options', 1;
reconfigure;
GO
EXEC sp_configure 'xp_cmdshell', 1;
reconfigure;
GO
Once we run the above T-SQL query, any account that is part of the sysadmin role in the SQL Server instance has the ability to run the xp_cmdshell extended stored procedure. On the background, when the user with sysadmin privileges runs the xp_cmdshell, it will execute the Windows command shell using the SQL Server Service Account (So if you are executing xp_cmdshell to access certain resource on the network, for example, and you are having permission issue, you might want to make sure that the SQL Server Service Account has permission to that resource).
Now, what if you have a non-sysadmin account that needs to run xp_cmdshell? In order to do that, we would need to do some additional configuration. For this example, let say that we want to grant SQL Server login 'johndoe' the ability to execute xp_cmdshell extended stored procedure (Notes: It doesn’t have to be SQL Server login, it can also be assigned to Windows authentication). And we want to execute the command passed to xp_cmdshell using account Domain\RunUser Windows account.
First, we would need to create a cmd shell proxy account on the SQL Server Instance. This will essentially tell SQL Server to use the proxy account when executing the Windows command shell. To do that we can run the following T-SQL statement:
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.credentials WHERE [name] = '##xp_cmdshell_proxy_account##')
BEGIN
CREATE CREDENTIAL ##xp_cmdshell_proxy_account## WITH IDENTITY = 'Domain\RunUser', SECRET = 'PasswordForTheRunUser'
END
GO
Next, we would need to grant the non-sysadmin account (in this case the SQL Server login 'johndoe'), access to master database and the execute permission for the xp_cmdshell extended stored procedure. To do this, we need to execute the following T-SQL Statement:
USE [master];
GO
EXEC sp_grantdbaccess 'johndoe';
GO
GRANT EXEC ON xp_cmdshell TO johndoe;
GO
Now, if the proxy account that we use (in this case, the domain account 'Domain\RunUser') doesn’t have local administration to the Windows Server where the SQL Server Instance is installed, we would need to grant it "Logon As Batch Job" privilege on the "Local Security Policy". To do that, we can go to "Local Security Policy" and then go to "User Rights Assignment" and look for "Logon As Batch Job". Add 'Domain\RunUser' to the "Logon As Batch Job" policy. If we don’t do this, we would most likely get the following error when 'johndoe' SQL Server login account tries to execute xp_cmdshell:
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.
A quick way to test, you can run the following T-SQL statement:
EXEC AS LOGIN = 'johndoe';
EXEC xp_cmdshell 'whoami';
REVERT;
The above T-SQL statement would run the xp_cmdshell 'whoami' by impersonating 'johndoe' SQL Server login. If things are set up properly, you should see 'Domain\RunUser' as the result.
To Clean Up or Revert Back:
First, we would remove 'Domain\RunUser' from the "Local Security Policy" - "User Rights Assignment" - "Logon As Batch Job".
Then, we would run the following T-SQL statement to revoke access for 'johndoe' user:
USE [master];
GO
REVOKE EXEC ON xp_cmdshell TO johndoe;
GO
EXEC sp_revokedbaccess 'johndoe';
GO
Lastly, we would run the following T-SQL statement to remove the proxy account:
USE [master];
GO
EXEC sp_xp_cmdshell_proxy_account NULL;
GO
No comments:
Post a Comment