Who does SQL Server run xp_cmdshell command as?

When you execute xp_cmdshell SQL Server runs a command at the operating system level similar to the old DOS prompt, or CMD shell. There are many security concerns and misconceptions about xp_cmdshell as documented in an earlier post.

The purpose of this post is not to debate whether xp_cmdshell is safe or not, but instead to show what user commands sent to xp_cmdshell are being run as.

To start off, we will be using the whoami command, this is a dos/cmdshell command that returns the current logged in user. When run from the command prompt it looks like the following, showing that the server name is steve18 and the currently logged in user is steve.

Easy enough, now we can use a script that I created to test this from SQL Server. This originally came from a project where a procedure was failing in one scenario and not failing in another scenario, and I wanted to prove show the procedure was running as to show with the network admin.

Before running the script we will need to enable xp_cmdshell. If this is not something that you are familiar with, please take a look at my earlier post.

EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', '1';
GO
RECONFIGURE

The script above will enable xp_cmdshell. If you don’t run that you will probably see a message like this:

Msg 15281, Level 16, State 1, Procedure master..xp_cmdshell, Line 1 [Batch Start Line 34]
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online.

Here is the procedure to wrap xp_cmdshell and return the user that xp_cmdshell is running as.

------------------------------------------------------------------
-- created by Steve Stedman
------------------------------------------------------------------
-- procedure whoami runs the dos command whoami and 
-- returns the user that the process is running as.
------------------------------------------------------------------
CREATE PROCEDURE [dbo].[whoami]
(
    @username VARCHAR(1000) OUTPUT
)
AS
BEGIN
    CREATE TABLE #cmdlog 
    	   (
    		  output VARCHAR(255) NULL
    	   );
    DECLARE @dosCmd as NVARCHAR(3000);
    SET @dosCmd = 'whoami';
    INSERT INTO #cmdlog EXEC master..xp_cmdshell @dosCmd;
    SELECT TOP 1 @username = [output] 
      FROM #cmdlog
     WHERE [output] IS NOT NULL;
    DROP TABLE #cmdlog;
END
GO

Then here is the TSQL to call the procedure and display the result.

DECLARE @username as VARCHAR(1000);
SET @username = NULL;
EXEC [dbo].[whoami] @username OUTPUT;
PRINT @username;

When run, it shows that the whoami in this case is running as the service account that SQL Server is configured to run as nt service\mssql$sql2019.

This should match the user that your SQL Server process is running as.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!