Securing xp_cmdshell in SQL Server

In this post I cover two ways of properly securing xp_cmdshell in SQL Server to avoid unrestricted access to the underlying Windows OS.

What’s xp_cmdshell?

xp_cmdshell is an extended system stored procedure that is used to interact with the instance’s underlying Windows operating system.

When executed, it spawns a Windows command shell session and executes the provided input string as a command, returning the output, if any, as rows of text.

Use cases

From a DBA point of view, xp_cmdshell is a fairly handy option when wanting to interact with an instance’s host OS without having to remote desktop into the VM, even more so when you don’t have RDP access to the VM.

I’ve used it to check for storage space, running processes and their resource usage, I’ve also used it to delete leftover backup and database files that were no longer needed and were just taking up space.

From a developer point of view, I’ve seen applications relying on xp_cmdshell to write files to disk that where afterwards sent as email attachments using sp_send_dbmail.

The risks that come with xp_cmdshell

By default xp_cmdshell is disabled, another safeguard is the fact that, once enabled, it can only be used by members of the sysadmin fixed server-level role and logins who have been explicitly granted the CONTROL SERVER server permission.

The risk with xp_cmdshell is that, if it’s not properly secured, once someone gets access to it due to lax security configurations, that someone can directly interact with the host OS as SQL Server’s service account.

This is compounded if the service account is a member of the local Administrators group or, even worse, SQL Server runs as LocalSystem. This means that said person, even though they only have access to SQL Server, ends up having full access to the host server via xp_cmdshell.

Side-note, there are a few forums out there where people suggest setting SQL Server to run as LocalSystem as a fix for various problems – do not follow that advice, it’s just a lazy “fix” for permission-related issues, and it exposes your server to unnecessary risk.

If you want to read more about securing SQL Server’s service account, check out this blog post.

For details on how to grant SQL Server’s service account permissions on a folder or file, check out this blog post.

Enabling and executing xp_cmdshell in SQL Server

Now, that I’ve explained the risks, it’s time to start playing with xp_cmdshell and setting things up.

First, xp_cmdshell can be enabled either via P olicy Based Management or, the more commonly used way, via sp_configure.

/*Enable advanced options in sp_configure*/
EXECUTE sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

/*Enable xp_cmdshell*/
EXECUTE sp_configure 'xp_cmdshell', 1;
GO

/*Disable advanced options*/
EXECUTE sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

/*Enable advanced options in sp_configure*/

EXECUTE sp_configure 'show advanced options', 1;

RECONFIGURE;

/*Enable xp_cmdshell*/

EXECUTE sp_configure 'xp_cmdshell', 1;

/*Disable advanced options*/

EXECUTE sp_configure 'show advanced options', 0;

RECONFIGURE;

At this point, I’m logged into the instance as SA, so I can execute xp_cmdshell without any extra steps.
Any command you can run in Command Prompt can be an input for xp_cmdshell, here are a few examples:

Getting the contents of the current directory (notice how the landing directory is C:\Windows\system32)

EXEC xp_cmdshell 'dir';

1	EXEC xp_cmdshell 'dir';

Finding as which user I’m currently interacting with the OS as, group membership and privileges (In Command Prompt, && means “run the following command if the previous command executed successfully”, and it’s a pretty nifty way of running more commands in one single line)

EXEC xp_cmdshell 'whoami && whoami /groups';

1	EXEC xp_cmdshell 'whoami && whoami /groups';

You can also use xp_cmdshell to run PowerShell commands, here’s a quick example of calling PowerShell and having it run Get-Process to get information about the SQL Server process.

EXEC xp_cmdshell 'powershell "get-process -name "sqlservr*" | Format-List *"';

1	EXEC xp_cmdshell 'powershell "get-process -name "sqlservr" \| Format-List "';

Note that you’re not limited to just retrieving information, you can use xp_cmdshell to manipulate directories and files, and, even start/stop other processes.
If it’s improperly secured and the SQL Server service runs as a high privilege user, you can even leverage it create and modify Windows users.

Securing xp_cmdshell in SQL Server

First, I set up a database and a SQL Login to act as my application database and user.

USE [master]
GO
CREATE DATABASE [AppDB]
GO
CREATE LOGIN [AppUser] WITH PASSWORD='P@ssW0rd!', 
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF;
GO
USE [AppDB]
GO
CREATE USER [AppUser] FOR LOGIN [AppUser];
GO

USE [master]

CREATE DATABASE [AppDB]

CREATE LOGIN [AppUser] WITH PASSWORD='P@ssW0rd!',

DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF;

USE [AppDB]

CREATE USER [AppUser] FOR LOGIN [AppUser];

Now, the first step in securing xp_cmdshell is to create a dedicated Windows user for it, this can be an AD user, but since I’m running on a standalone Windows Server 2022 VM, it will be a local user.

Using PowerShell’s New-LocalUser cmdlet to create the user:

#This is part 1 - you'll be prompted to input the password
$Password = Read-Host -AsSecureString

#Part 2 - creating the user with the password provided above
New-LocalUser -Name 'SQLXPCMDShell' `
-Description 'Runs commands from xp_cmdshell' -Password $Password `
-AccountNeverExpires -PasswordNeverExpires

#This is part 1 - you'll be prompted to input the password

$Password = Read-Host -AsSecureString

#Part 2 - creating the user with the password provided above

New-LocalUser -Name 'SQLXPCMDShell' `

-Description 'Runs commands from xp_cmdshell' -Password $Password `

-AccountNeverExpires -PasswordNeverExpires

Now, in SQL Server logged in as SA (or any member of the SysAdmin role), I use sp_xp_cmdshell_proxy_account to create the proxy account.
The provided password is the same as the one used when the account was created via PowerShell.
Note that this command has to be executed from the instance’s host from either SSMS or Command Prompt opened as administrator, otherwise it will result in a Windows access denied error.

Specifically:

Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1 [Batch Start Line 22]
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the ‘##xp_cmdshell_proxy_account##’ credential could not be created. Error code: 5 (Access is denied.), Error Status: 0.

/*
Create proxy account - note that for this to work you'll 
have to be logged in on the instance's host
and execute it from SSMS opened as admin
*/
EXEC sp_xp_cmdshell_proxy_account 'WINSRV2K22\SQLXPCMDShell','$ecur3Pa22';
GO

Create proxy account - note that for this to work you'll

have to be logged in on the instance's host

and execute it from SSMS opened as admin

EXEC sp_xp_cmdshell_proxy_account 'WINSRV2K22\SQLXPCMDShell','$ecur3Pa22';

At this point, one could think that any call made through xp_cmdshell would now be executed at the OS level by the Windows user mapped to the proxy account, but that doesn’t apply to members of the SysAdmin fixed server-level role.

/*check if proxy account exists*/
SELECT [name], [credential_identity]
FROM   sys.credentials;
/*see if it's being used by SA*/
EXEC xp_cmdshell 'whoami';

/*check if proxy account exists*/

SELECT [name], [credential_identity]

FROM sys.credentials;

/*see if it's being used by SA*/

EXEC xp_cmdshell 'whoami';

The “lazy” way.

Side-note: the actual lazy way is to not make a proxy account and just add AppUser to the SysAdmin server role, which I really hope nobody in their right mind would do in a production environment since it’s the opposite of secure.

The “lazy” and risky option at this point is to create a database user for AppUser in the master database and then grant the user execute permissions on xp_cmdshell, like in the below example.

USE [master]
GO
CREATE USER [AppUser] FOR LOGIN [AppUser];
GO
GRANT EXECUTE ON xp_cmdshell TO [AppUser];
GO

USE [master]

CREATE USER [AppUser] FOR LOGIN [AppUser];

GRANT EXECUTE ON xp_cmdshell TO [AppUser];

Connecting as AppUser and running xp_cmdshell shows that this works without any issues.

SELECT SUSER_NAME() AS [LoginName];
EXEC xp_cmdshell 'whoami';

1 2	SELECT SUSER_NAME() AS [LoginName]; EXEC xp_cmdshell 'whoami';

AppUser can now execute xp_cmdshell and is interacting with the OS using the proxy account…

Needles to say, this isn’t really a great way to try and secure xp_cmdhsell since you’d also want to limit what commands AppUser is able to pass to xp_cmdhsell.

So, what’s the issue with this approach?

The problem is that, even though AppUser can now execute xp_cmdshell while using a low-privileged Windows account to carry out the commands at the OS level, there is no way of locking down what AppUser can (try to) do through xp_cmdshell.

This isn’t really securing xp_cmdshell as much as it is relying on the proxy account to have permissions restrictive enough so that nobody can do any significant damage through it.

Here’s where the next part of this post comes in.

Secure xp_cmdshell through EXECUTE AS OWNER

First, I do a quick cleanup from the previous example.

USE [master]
GO
REVOKE EXECUTE ON xp_cmdshell TO [AppUser];
GO
DROP USER [AppUser];
GO

USE [master]

REVOKE EXECUTE ON xp_cmdshell TO [AppUser];

DROP USER [AppUser];

Now, there are two steps that need to be done as sa (or any member of the sysadmin role that’s also the database owner).
First step is to encapsulate the code that AppUser uses to execute xp_cmdshell in a stored procedure and specify the EXECUTE AS OWNER option so that:

It ensures that a database user with minimal permissions (in this case just explicit execute on the stored procedure) can leverage xp_cmdshell by executing the code inside it as the database owner, which, in this case, is sa.
It blocks changes to the stored procedure made by any user in the AppDB database, even if they’re a member of the db_ddladmin or db_owner fixed database-level roles.

USE [AppDB]
GO
IF OBJECT_ID('dbo.GetProcessInfo') IS NOT NULL
BEGIN
    DROP PROCEDURE GetProcessInfo;
END;
GO
CREATE PROCEDURE GetProcessInfo
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @WriteCMD       NVARCHAR(500),
            @ReadCMD        NVARCHAR(500),
            @DateTimeString VARCHAR(19);

    SELECT @DateTimeString = REPLACE(REPLACE(REPLACE(
					CONVERT(VARCHAR(19), GETDATE(), 120), 
					'-', ''), ':', ''), ' ', '_')
	/*
	 Get info about current execution's security context - just to show which login and user is 
	executing the xp_cmdshell calls
	*/
	SELECT ORIGINAL_LOGIN() AS [OriginalLogin_InSP],
       SUSER_NAME()     AS [LoginName_InSP],
       SUSER_SNAME()    AS [CurrentSecurityContextLogin_InSP],
       USER_NAME()      AS [DatabaseUser_InSP],
       SYSTEM_USER      AS [CurrentSystemUser_InSP],
       SESSION_USER     AS [CurrentSessionDBUser_InSP];
	
	/*Check who is interacting with the OS*/
	EXEC xp_cmdshell 'echo Windows User && whoami';
	/*Write the file*/
    SET @WriteCMD = N'powershell "get-process -name "sqlservr*" | Format-List * | Out-File C:\temp\sqlservr_'
                    + @DateTimeString + N'.txt"';

    EXEC xp_cmdshell
      @WriteCMD;
    /*Read the file*/
    SET @ReadCMD = N'echo reading file C:\temp\sqlservr_'
                   + @DateTimeString
                   + N'.txt && more C:\temp\sqlservr_'
                   + @DateTimeString + N'.txt';

    EXEC xp_cmdshell
      @ReadCMD;
END;
GO

USE [AppDB]

IF OBJECT_ID('dbo.GetProcessInfo') IS NOT NULL

BEGIN

DROP PROCEDURE GetProcessInfo;

END;

CREATE PROCEDURE GetProcessInfo

WITH EXECUTE AS OWNER

BEGIN

DECLARE @WriteCMD NVARCHAR(500),

@ReadCMD NVARCHAR(500),

@DateTimeString VARCHAR(19);

SELECT @DateTimeString = REPLACE(REPLACE(REPLACE(

CONVERT(VARCHAR(19), GETDATE(), 120),

'-', ''), ':', ''), ' ', '_')

Get info about current execution's security context - just to show which login and user is

executing the xp_cmdshell calls

SELECT ORIGINAL_LOGIN() AS [OriginalLogin_InSP],

SUSER_NAME() AS [LoginName_InSP],

SUSER_SNAME() AS [CurrentSecurityContextLogin_InSP],

USER_NAME() AS [DatabaseUser_InSP],

SYSTEM_USER AS [CurrentSystemUser_InSP],

SESSION_USER AS [CurrentSessionDBUser_InSP];

/*Check who is interacting with the OS*/

EXEC xp_cmdshell 'echo Windows User && whoami';

/*Write the file*/

SET @WriteCMD = N'powershell "get-process -name "sqlservr*" | Format-List * | Out-File C:\temp\sqlservr_'

+ @DateTimeString + N'.txt"';

EXEC xp_cmdshell

@WriteCMD;

/*Read the file*/

SET @ReadCMD = N'echo reading file C:\temp\sqlservr_'

+ @DateTimeString

+ N'.txt && more C:\temp\sqlservr_'

+ @DateTimeString + N'.txt';

EXEC xp_cmdshell

@ReadCMD;

END;

And the last thing is to grant EXECUTE permission on the newly created stored procedure to the AppUser database user.

/*Grant execute to AppUser*/
GRANT EXECUTE ON [dbo].[GetProcessInfo] TO [AppUser];

1 2	/Grant execute to AppUser/ GRANT EXECUTE ON [dbo].[GetProcessInfo] TO [AppUser];

Test time

In order to properly test this, I’ll connect to the instance as the AppUser and run the following:

USE [AppDB]
GO
/*Verify identity before execution*/
SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
       SUSER_NAME()     AS [LoginName],
       SUSER_SNAME()    AS [CurrentSecurityContextLogin],
       USER_NAME()      AS [DatabaseUser],
       SYSTEM_USER      AS [CurrentSystemUser],
       SESSION_USER     AS [CurrentSessionDBUser];

/*Execute the stored procedure*/
EXEC [dbo].[GetProcessInfo];

USE [AppDB]

/*Verify identity before execution*/

SELECT ORIGINAL_LOGIN() AS [OriginalLogin],

SUSER_NAME() AS [LoginName],

SUSER_SNAME() AS [CurrentSecurityContextLogin],

USER_NAME() AS [DatabaseUser],

SYSTEM_USER AS [CurrentSystemUser],

SESSION_USER AS [CurrentSessionDBUser];

/*Execute the stored procedure*/

EXEC [dbo].[GetProcessInfo];

The first result set is showing the information about the login’s/user’s security context before I execute the stored procedure, everything is showing up as AppUser, which is as expected.

The second result set is showing how the security context changes for the code within the stored procedure, with everything in the stored procedure being ran by sa due the EXECUTE AS OWNER option and the database ownership.

The third result set shows the Windows account being used to interact with the OS from within the stored procedure, so this time calls through xp_cmdshell get executed at the OS level by the WINSRV2K22\SQLXPCMDShell Windows user.

The fourth result set is null, because the output is being written to file and nothing is returned.

And the fifth result set is the contents of the previously written file.

The resulting file is also visible on disk on the instance’s host VM.

Can AppUser change the code in the stored procedure if given db_ddladmin or db_owner membership?

I temporarily switch to SA to add AppUser as a member in the db_ddladmin role.

ALTER ROLE [db_ddladmin] ADD MEMBER [AppUser];

1	ALTER ROLE [db_ddladmin] ADD MEMBER [AppUser];

And then I switch back to AppUser and try to alter the procedure.

Error message: Msg 15151, Level 16, State 1, Procedure GetProcessInfo, Line 1 [Batch Start Line 2] Cannot execute as the user 'dbo', because it does not exist or you do not have permission.

As the error messages states, the AppUser login does not have permission to alter the stored procedure while it contains the EXECUTE AS OWNER option, the same thing happens if the user is a member of the db_owner role.

What if AppUser removes EXECUTE AS OWNER?

Looks like AppOwner is able to alter the stored procedure after commenting out the EXECUTE AS OWNER option.

Now let’s see if AppUser can still execute xp_cmdshell via the stored procedure.

USE [AppDB]
GO
/*Verify identity before execution*/
SELECT ORIGINAL_LOGIN() AS [OriginalLogin],
       SUSER_NAME()     AS [LoginName],
       SUSER_SNAME()    AS [CurrentSecurityContextLogin],
       USER_NAME()      AS [DatabaseUser],
       SYSTEM_USER      AS [CurrentSystemUser],
       SESSION_USER     AS [CurrentSessionDBUser];

/*Execute the stored procedure*/
EXEC [dbo].[GetProcessInfo];

USE [AppDB]

/*Verify identity before execution*/

SELECT ORIGINAL_LOGIN() AS [OriginalLogin],

SUSER_NAME() AS [LoginName],

SUSER_SNAME() AS [CurrentSecurityContextLogin],

USER_NAME() AS [DatabaseUser],

SYSTEM_USER AS [CurrentSystemUser],

SESSION_USER AS [CurrentSessionDBUser];

/*Execute the stored procedure*/

EXEC [dbo].[GetProcessInfo];

This results in every call to xp_cmdshell from within the stored procedure to error out with the following error message:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 [Batch Start Line 2]
The EXECUTE permission was denied on the object ‘ xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.

Checking the Results tab explains why.

Since WITH EXECUTE AS OWNER was commented out the stored procedure no longer changes the security context to that of SA for the calls to xp_cmdshell, and, since AppUser is not a sysadmin and does not have permissions to run xp_cmdshell directly, we get an error every time xp_cmdshell is called in GetProcessInfo.

Secure xp_cmdshell by signing the stored procedure

This method requires creating a certificate and a login-user pair based on said certificate.

The first step is to create the certificate in the master database.

/*Create the certificate*/
USE [master]
GO
CREATE CERTIFICATE [xp_cmdshell_certificate]
   ENCRYPTION BY PASSWORD = 'S3cr3tC3rtP@ss!'
   WITH SUBJECT = 'Used for granting xp_cmdshell exec permission',
   /*EXPIRY_DATE needs to be in the future, otherwise error 3701 will be thrown*/
   EXPIRY_DATE = '20280701'; 
GO

/*Create the certificate*/

USE [master]

CREATE CERTIFICATE [xp_cmdshell_certificate]

ENCRYPTION BY PASSWORD = 'S3cr3tC3rtP@ss!'

WITH SUBJECT = 'Used for granting xp_cmdshell exec permission',

/*EXPIRY_DATE needs to be in the future, otherwise error 3701 will be thrown*/

EXPIRY_DATE = '20280701';

Verify the certificate, just to make sure.

SELECT [name],
       [certificate_id],
       USER_NAME([principal_id]) AS [cert_owner],
       [expiry_date],
       [subject],
       [pvt_key_encryption_type_desc],
       [is_active_for_begin_dialog]
FROM   sys.certificates
WHERE  [name] = N'xp_cmdshell_certificate';

SELECT [name],

[certificate_id],

USER_NAME([principal_id]) AS [cert_owner],

[expiry_date],

[subject],

[pvt_key_encryption_type_desc],

[is_active_for_begin_dialog]

FROM sys.certificates

WHERE [name] = N'xp_cmdshell_certificate';

SSMS result set for the above query Securing xp_cmdshell in SQL Server 2022

The next step is to create the login and database user pair from the certificate and grant the user execute permission on xp_cmdshell.

USE [master]
GO
/*Create a login from the certificate*/
CREATE LOGIN [xp_cmdshell_cert_login] FROM CERTIFICATE [xp_cmdshell_certificate];
GO
/*Create a database user for the login*/
CREATE USER [xp_cmdshell_cert_login] FOR LOGIN [xp_cmdshell_cert_login];
GO
GRANT EXECUTE ON xp_cmdshell TO [xp_cmdshell_cert_login];
GO

USE [master]

/*Create a login from the certificate*/

CREATE LOGIN [xp_cmdshell_cert_login] FROM CERTIFICATE [xp_cmdshell_certificate];

/*Create a database user for the login*/

CREATE USER [xp_cmdshell_cert_login] FOR LOGIN [xp_cmdshell_cert_login];

GRANT EXECUTE ON xp_cmdshell TO [xp_cmdshell_cert_login];

I now copy the certificate from the master database to the AppDB database.
Note that the following block of code needs to be executed all in one go.

/*Copy the certificate to the AppDB database*/
USE [master]
GO
DECLARE @CertID  INT,
        @PubKey  VARBINARY(MAX),
        @PrivKey VARBINARY(MAX),
        @Pass    NVARCHAR(20);

SET @Pass = N'S3cr3tC3rtP@ss!'

SELECT @CertID = CERT_ID('xp_cmdshell_certificate');

SELECT @PubKey = CERTENCODED(@CertID),
       @PrivKey = CERTPRIVATEKEY(@CertID, @Pass, @Pass);


DECLARE @SQL NVARCHAR(MAX) = N'CREATE CERTIFICATE [xp_cmdshell_certificate]
       FROM  BINARY = '
  + CONVERT(VARCHAR(MAX), @PubKey, 1)
  + N'
       WITH PRIVATE KEY (BINARY = '
  + CONVERT(VARCHAR(MAX), @PrivKey, 1)
  + N', DECRYPTION BY PASSWORD = N''' + @Pass
  + ''', ENCRYPTION BY PASSWORD = N''' + @Pass
  + ''')'; 

/*
 Switch to AppDB, but don't use GO after the switch 
since it will flush the variables
*/
USE [AppDB];

/*
 Execute the dynamic SQL that will create the certificate 
based on the one in master
*/
EXEC(@SQL);

/*Copy the certificate to the AppDB database*/

USE [master]

DECLARE @CertID INT,

@PubKey VARBINARY(MAX),

@PrivKey VARBINARY(MAX),

@Pass NVARCHAR(20);

SET @Pass = N'S3cr3tC3rtP@ss!'

SELECT @CertID = CERT_ID('xp_cmdshell_certificate');

SELECT @PubKey = CERTENCODED(@CertID),

@PrivKey = CERTPRIVATEKEY(@CertID, @Pass, @Pass);

DECLARE @SQL NVARCHAR(MAX) = N'CREATE CERTIFICATE [xp_cmdshell_certificate]

FROM BINARY = '

+ CONVERT(VARCHAR(MAX), @PubKey, 1)

+ N'

WITH PRIVATE KEY (BINARY = '

+ CONVERT(VARCHAR(MAX), @PrivKey, 1)

+ N', DECRYPTION BY PASSWORD = N''' + @Pass

+ ''', ENCRYPTION BY PASSWORD = N''' + @Pass

+ ''')';

Switch to AppDB, but don't use GO after the switch

since it will flush the variables

USE [AppDB];

Execute the dynamic SQL that will create the certificate

based on the one in master

EXEC(@SQL);

At this point I can create the stored procedure in AppDB. Note that as opposed to the previous example, this one no longer contains the EXECUTE AS OWNER option.

USE [AppDB]
GO
IF OBJECT_ID('dbo.GetProcessInfo') IS NOT NULL
BEGIN
    DROP PROCEDURE GetProcessInfo;
END;
GO
CREATE PROCEDURE GetProcessInfo
AS
BEGIN
    DECLARE @WriteCMD       NVARCHAR(500),
            @ReadCMD        NVARCHAR(500),
            @DateTimeString VARCHAR(19);

    SELECT @DateTimeString = REPLACE(REPLACE(REPLACE(
					CONVERT(VARCHAR(19), GETDATE(), 120), 
					'-', ''), ':', ''), ' ', '_')
	/*
	 Get info about current execution's security context - just to show which login and user is 
	executing the xp_cmdshell calls
	*/
	SELECT ORIGINAL_LOGIN() AS [OriginalLogin_InSP],
       SUSER_NAME()     AS [LoginName_InSP],
       SUSER_SNAME()    AS [CurrentSecurityContextLogin_InSP],
       USER_NAME()      AS [DatabaseUser_InSP],
       SYSTEM_USER      AS [CurrentSystemUser_InSP],
       SESSION_USER     AS [CurrentSessionDBUser_InSP];
	
	/*Check who is interacting with the OS*/
	EXEC xp_cmdshell 'echo Windows User && whoami';
	/*Write the file*/
    SET @WriteCMD = N'powershell "get-process -name "sqlservr*" | Format-List * | Out-File C:\temp\sqlservr_'
                    + @DateTimeString + N'.txt"';

    EXEC xp_cmdshell
      @WriteCMD;
    /*Read the file*/
    SET @ReadCMD = N'echo reading file C:\temp\sqlservr_'
                   + @DateTimeString
                   + N'.txt && more C:\temp\sqlservr_'
                   + @DateTimeString + N'.txt';

    EXEC xp_cmdshell
      @ReadCMD;
END;
GO

USE [AppDB]

IF OBJECT_ID('dbo.GetProcessInfo') IS NOT NULL

BEGIN

DROP PROCEDURE GetProcessInfo;

END;

CREATE PROCEDURE GetProcessInfo

BEGIN

DECLARE @WriteCMD NVARCHAR(500),

@ReadCMD NVARCHAR(500),

@DateTimeString VARCHAR(19);

SELECT @DateTimeString = REPLACE(REPLACE(REPLACE(

CONVERT(VARCHAR(19), GETDATE(), 120),

'-', ''), ':', ''), ' ', '_')

Get info about current execution's security context - just to show which login and user is

executing the xp_cmdshell calls

SELECT ORIGINAL_LOGIN() AS [OriginalLogin_InSP],

SUSER_NAME() AS [LoginName_InSP],

SUSER_SNAME() AS [CurrentSecurityContextLogin_InSP],

USER_NAME() AS [DatabaseUser_InSP],

SYSTEM_USER AS [CurrentSystemUser_InSP],

SESSION_USER AS [CurrentSessionDBUser_InSP];

/*Check who is interacting with the OS*/

EXEC xp_cmdshell 'echo Windows User && whoami';

/*Write the file*/

SET @WriteCMD = N'powershell "get-process -name "sqlservr*" | Format-List * | Out-File C:\temp\sqlservr_'

+ @DateTimeString + N'.txt"';

EXEC xp_cmdshell

@WriteCMD;

/*Read the file*/

SET @ReadCMD = N'echo reading file C:\temp\sqlservr_'

+ @DateTimeString

+ N'.txt && more C:\temp\sqlservr_'

+ @DateTimeString + N'.txt';

EXEC xp_cmdshell

@ReadCMD;

END;

I now sign the procedure using the previously created certificate, and grant execute permission on the stored procedure to the AppUser database user.

/*Sign stored procedure*/
ADD SIGNATURE TO [dbo].[GetProcessInfo] BY CERTIFICATE [xp_cmdshell_certificate]
   WITH PASSWORD = 'S3cr3tC3rtP@ss!';
/*Grant execute to AppUser*/
GRANT EXECUTE ON [dbo].[GetProcessInfo] TO [AppUser];
GO

/*Sign stored procedure*/

ADD SIGNATURE TO [dbo].[GetProcessInfo] BY CERTIFICATE [xp_cmdshell_certificate]

WITH PASSWORD = 'S3cr3tC3rtP@ss!';

/*Grant execute to AppUser*/

GRANT EXECUTE ON [dbo].[GetProcessInfo] TO [AppUser];

Check if the procedure has been successfully signed.

SELECT OBJECT_NAME([crp].[major_id]) AS [ObjectName],
       [obj].[type_desc]             AS [ObjectType],
       [crt].[name]                  AS [Cert],
       [crp].[crypt_type_desc]
FROM   sys.crypt_properties [crp]
       INNER JOIN sys.objects AS [obj]
               ON [obj].[object_id] = [crp].[major_id]
       LEFT JOIN sys.certificates AS [crt]
              ON [crt].[thumbprint] = [crp].[thumbprint];

SELECT OBJECT_NAME([crp].[major_id]) AS [ObjectName],

[obj].[type_desc] AS [ObjectType],

[crt].[name] AS [Cert],

[crp].[crypt_type_desc]

FROM sys.crypt_properties [crp]

INNER JOIN sys.objects AS [obj]

ON [obj].[object_id] = [crp].[major_id]

LEFT JOIN sys.certificates AS [crt]

ON [crt].[thumbprint] = [crp].[thumbprint];

All that’s left to do now is to remove the private key from the certificate in both databases, since only the public key is needed to validate the signature.
And if I need to sign again this procedure (following a code change) or sign other procedures for the same purpose, I can just re-create the certificate.
This also helps prevent someone, who might have seen the password, from potentially exploiting the existing private key.

USE [AppDB]
GO
ALTER CERTIFICATE [xp_cmdshell_certificate] REMOVE PRIVATE KEY;
GO
USE [master]
GO
ALTER CERTIFICATE [xp_cmdshell_certificate] REMOVE PRIVATE KEY;
GO

USE [AppDB]

ALTER CERTIFICATE [xp_cmdshell_certificate] REMOVE PRIVATE KEY;

USE [master]

ALTER CERTIFICATE [xp_cmdshell_certificate] REMOVE PRIVATE KEY;

Test time part deux

Now, to test if the signed procedure works as intended, I just connect to the instance as AppUser and execute the stored procedure.
Note that I’m no longer adding the query to list the current security context since with this method it no longer has to change during the execution of the procedure for xp_cmdshell to work.

EXEC [GetProcessInfo];

1	EXEC [GetProcessInfo];

In this case, the first result set is the check from within the stored procedure, and, unlike the previous example, the security context doesn’t change to that of sa during the execution.

Everything else behaves as expected. xp_cmdshell interacts with the OS via the proxy account, the process information is successfully written to disk and the resulting file is retrieved and displayed in the last result set.

Can AppUser change the code within the signed stored procedure?

AppUser is still a member of the db_ddladmin fixed database role from the previous test, and can alter the stored procedure just fine.

But, after altering it, the stored procedure no longer runs and it errors out in the same way it did when AppUser removed the WITH EXECUTE AS OWNER option from the previous test.

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 [Batch Start Line 2]
The EXECUTE permission was denied on the object ‘ xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.

Checking the signature status of the stored procedure reveals why.

/*Run as SA*/
USE [AppDB]
GO
SELECT OBJECT_NAME([crp].[major_id]) AS [ObjectName],
       [obj].[type_desc]             AS [ObjectType],
       [crt].[name]                  AS [Cert],
       [crp].[crypt_type_desc]
FROM   sys.crypt_properties [crp]
       INNER JOIN sys.objects AS [obj]
               ON [obj].[object_id] = [crp].[major_id]
       LEFT JOIN sys.certificates AS [crt]
              ON [crt].[thumbprint] = [crp].[thumbprint];

/*Run as SA*/

USE [AppDB]

SELECT OBJECT_NAME([crp].[major_id]) AS [ObjectName],

[obj].[type_desc] AS [ObjectType],

[crt].[name] AS [Cert],

[crp].[crypt_type_desc]

FROM sys.crypt_properties [crp]

INNER JOIN sys.objects AS [obj]

ON [obj].[object_id] = [crp].[major_id]

LEFT JOIN sys.certificates AS [crt]

ON [crt].[thumbprint] = [crp].[thumbprint];

The stored procedure is no longer singed as a result of AppUser altering it, which makes it now impossible for AppUser to successfully use the stored procedure to execute xp_cmdshell.

Conclusion

Both of these methods are great for the purpose of securing xp_cmdhsell because they both require someone with sysadmin membership to approve and make any subsequent code changes to them.
Meaning that AppUser can’t get xp_cmdshell to run whatever they feel like with oversight.

I hope this post helps shed some light on the fact that there are feasible ways of securing xp_cmshell in SQL Server while still allowing users access to it.

While it might seem like a hassle at first, you should never skimp on security, especially when Microsoft provides solutions already built into SQL Server and Windows to handle this specific scenario.

If you’ want to read another SQL Server security related post, feel free to check out my post on securing SQL Server linked servers.

Cookie	Duration	Description
cookielawinfo-checbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Securing xp_cmdshell in SQL Server

What’s xp_cmdshell?

Use cases

The risks that come with xp_cmdshell

Enabling and executing xp_cmdshell in SQL Server

Securing xp_cmdshell in SQL Server

The “lazy” way.

So, what’s the issue with this approach?

Secure xp_cmdshell through EXECUTE AS OWNER

Test time

Can AppUser change the code in the stored procedure if given db_ddladmin or db_owner membership?

What if AppUser removes EXECUTE AS OWNER?

Secure xp_cmdshell by signing the stored procedure

Test time part deux

Can AppUser change the code within the signed stored procedure?

Conclusion

Testing large insert speeds in SQL Server

Securing SQL Server linked servers

You may also like

Leave a Comment Cancel Reply