XP_CMDSHELL fails execution

  • John Parker-202766

    Old Hand

    Points: 375

    Hi All,

    Got a SQL Server 2005 problem whereby we get the following error:

    ExecuteDTS (X import):[Microsoft] [ODBC SQL Server Driver] [SQL Server] An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code:'1326'

    This used to work but the password was changed last week for the SQL Server service account. We've checked everything and even rekeyed the password. I've google supported it but couldn't find a satisfactory answer.

    Any ideas ?

    many thanks,

    John P.

  • Toby White

    SSCertifiable

    Points: 6127

    I assume everything is in the same domain since it worked for you before you changed the password. Is it possible that when the password was changed the Active Directory account was inadvertantly recreated? If this did occur then the SID would have been recreated and would not match the SID that is stored in SQL Server which would cause this error. You can find out if the SID matches by selecting the SID from

    select * from sys.server_principals and then create the

    account on a test/dev machine or your local box if you have a dev instance there and check the sid that was created. If these two SIDs are not the same then this is the problem.

  • John Parker-202766

    Old Hand

    Points: 375

    Toby,

    thank you for your response. After restarting the service, all was well.

    John P.

  • ap-401939

    Valued Member

    Points: 59

    USE master;

    GRANT CONTROL SERVER TO LoginName;

    GO

    GRANT EXECUTE on xp_cmdshell TO LoginName

  • Brandie Tarvin

    SSC Guru

    Points: 172536

    So apparently this thread comes up in a search of xp_cmdshell errors. For those who stumble across this thread in the future, the above post is BAD. Do not grant control server to a login just to try and fix a xp_cmdshell problem. BAD BAD BAD BAD BAD.

    ap-401939 (5/20/2012)


    USE master;

    GRANT CONTROL SERVER TO LoginName; -- SUPER BAD, Don't do this!

    GO

    GRANT EXECUTE on xp_cmdshell TO LoginName

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Ed Wagner

    SSC Guru

    Points: 286958

    Brandie Tarvin (11/4/2015)


    So apparently this thread comes up in a search of xp_cmdshell errors. For those who stumble across this thread in the future, the above post is BAD. Do not grant control server to a login just to try and fix a xp_cmdshell problem. BAD BAD BAD BAD BAD.

    ap-401939 (5/20/2012)


    USE master;

    GRANT CONTROL SERVER TO LoginName; -- SUPER BAD, Don't do this!

    GO

    GRANT EXECUTE on xp_cmdshell TO LoginName

    Agreed. I think the WITH EXECUTE AS OWNER would come into play in a real solution, but GRANT CONTROL SERVER should not be included in the answer.

  • Jeff Moden

    SSC Guru

    Points: 994667

    I'll also add that no one except DBAs that have "SA" privs should ever be allowed to execute xp_CmdShell directly. It's one of those rare instances where I'll actually use the word "NEVER". Being able to use it in a well formed and protected stored procedure? Not a problem. Grant them privs to execute the proc.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994667

    John Parker-202766 (7/4/2008)


    Toby,

    thank you for your response. After restarting the service, all was well.

    John P.

    What you don't know is that whatever xp_CmdShell was running, is still running! Restarting the service clears the link to SQL Server but the process in Windows is still running. Depending on what it's doing, it could still be consuming a shedload of CPU, as well.

    There are only two valid ways to kill a stuck xp_CmdShell call. One is to bounce the whole server, not just the SQL Service. Obviously, that's even worse than just bouncing the service.

    The other way (which is the best way) is to kill the PROCESS that's running including any and all subprocesses that may be attached to the main process. While not horribly difficult to do manually if you know how, the DBA might not have OS level access to the SQL Server and, if there are a whole lot of Cmd Shell processes running, it can be a bit daunting to find just the one you need to kill. Obviously, you'll need to know what the issued command was to make a really good decision there.

    With all of that in mind, the following stored procedure will help you find all of this and successfully kill the offending PID and the related subtree. Rather than regurgitate how to use it, all of that is in the documentation in the code. In fact, if you run the stored procedure without any parameters or with 'Jelp', it will display the help as a return from the stored procedure. It is setup to ONLY find Cmd.EXE with the "/C" switch but that doesn't mean it will only find PIDs started by xp_CmdShell. ANY Cmd.EXE instance with the /C switch will appear. Since most people don't run a DOS Window session using the /C switch, it will likely not find those (you don't need to kill those, usually). Still, you're killing a PID and you need to pay attention to what you're killing.

    And, yes... I install this on the Master database of all my servers so I don't have to look for it if there is such an emergency. READ AND UNDERSTAND ALL THE COMMENTS BEFORE YOU USE IT OR DON'T USE IT! It's not dangerous code but how you use it might be. 😉

    The best part about this code is that you're no longer required to bounce the server or the service to kill a stuck xp_CmdShell SPID!

    Here's the code.

    CREATE PROCEDURE [dbo].[sp_FindCmdProcess]

    /**********************************************************************************************************************

    Purpose:

    Finds and kills processes started by xp_CmdShell so that "frozen" SPIDs can be killed.

    All related child processes will also be killed.

    Please see the "Help" section of the code or simply run this proc with no parameters for syntax and other information.

    If you want to make this sproc available from any database, create this stored procedure in the Master database and

    then run the following code.

    --===== Make the listed sproc a "system object".

    USE MASTER;

    EXEC sp_ms_marksystemobject 'sp_FindCmdProcess'

    ;

    --===== Verify that that mark "took".

    SELECT name, is_ms_shipped

    FROM sys.objects

    WHERE name = 'sp_FindCmdProcess'

    ;

    Revision History:

    Rev 00 - 23 Feb 2014 - Jeff Moden - Initial release and unit testing.

    **********************************************************************************************************************/

    --===== Declare the I/O parameters for this sproc.

    @pLookFor VARCHAR(8000) = 'Help'

    ,@pAction CHAR(4) = 'List'

    AS

    --=====================================================================================================================

    -- Presets

    --=====================================================================================================================

    --===== Environmental Settings

    SET NOCOUNT ON;

    --===== Temp Table(s)

    IF OBJECT_ID('tempdb..#CmdResponse','U') IS NOT NULL

    DROP TABLE #CmdResponse

    ;

    CREATE TABLE #CmdResponse

    (

    Line# INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

    ,CmdOutput VARCHAR(8000)

    )

    ;

    --===== Local variables

    DECLARE @Cmd VARCHAR(8000)

    ,@CmdKill VARCHAR(MAX)

    ,@ProcessIdStart INT

    ,@LookFor VARCHAR(8000)

    ,@Action VARCHAR(10)

    ;

    --=====================================================================================================================

    -- Validate and Delouse Inputs

    --=====================================================================================================================

    SELECT @LookFor = @pLookFor

    ,@Action = @pAction

    WHERE @pLookFor LIKE '%[^&|#]%' --Reject DOS injection by disallowing embedded/multiple commands and comments

    AND @pLookFor NOT LIKE 'REM %'--Reject DOS injection by disallowing REM statments

    AND @pAction IN ('Help','List','Kill')

    ;

    IF @LookFor IS NULL

    OR @Action IS NULL

    BEGIN

    RAISERROR('No Action Taken.',16,1);

    RETURN 1;

    END

    ;

    --=====================================================================================================================

    -- First, check to see if someone is simply looking for help with syntax

    --=====================================================================================================================

    IF @LookFor = 'Help'

    BEGIN

    PRINT '

    ***Help on sp_FindCmdProcess***

    Rev 00 - 23 Feb 2014 - Jeff Moden - Initial release and unit testing.

    Purpose:

    Finds and kills select processes started by xp_CmdShell so that "frozen" SPIDs can be killed.

    More specifically, it only finds instances of cmd.exe /c and will not find instance of just

    cmd.exe for safety sake.

    All related child processes will also be killed.

    Syntax:

    EXEC sp_FindCmdProcess @pLookFor, @pAction;

    @pLookFor can be...

    If not present, will default to ''HELP''.

    If blank or NULL, will error out.

    ''HELP'' -- Overrides everything and returns this help listing.

    ''somestring'' -- Find all cmd.exe /c processes that contain this ''somestring''.

    ''All'' -- Find all cmd.exe /c processes.

    somenumber -- Find the process that has a ProcessID = somenumber.

    ''somenumber'' -- Find the process that has a ProcessID = somenumber.

    May not contain the special symbols of &, |, or #.

    May not contain values that start with REM

    May contain the % wild card that is used by WMIC.

    @pAction can be...

    If not present, will default to ''List''.

    If blank or NULL, will error out.

    ''List'' -- Will list all found cmd.exe /c processes with no action taken.

    ''Kill'' -- Will KILL all found cmd.exe /c processes after a 20 second

    "stopable" delay period. Click the STOP button during the

    delay period to abort the run with no action taken. Once the

    kills begin, the code will run to completion killing all

    found processes according to the @pLookFor parameter.

    This stored procedure will NOT list or kill cmd.exe processes that are not using

    the /c sub-processor. This means that it will not list or kill "Command" windows

    that have been opened by users unless they manually invoked the /c sub-processor

    at the time they invoked cmd.exe.

    '

    RETURN 0 ;

    END

    ;

    --=====================================================================================================================

    -- Find the cmd.exe tasks that we're looking for.

    --=====================================================================================================================

    --===== Create the necessary dynamic DOS command to find the processes that we're looking for.

    SELECT @Cmd = CASE --Determine what to find

    WHEN @LookFor = 'ALL'

    THEN 'WMIC PROCESS WHERE (Name="cmd.exe" AND CommandLine LIKE "%/c %") GET CommandLine,ProcessId'

    WHEN @LookFor NOT LIKE '%[^0-9]%' --@LookFor is all digits for a ProcessID

    THEN REPLACE(

    'WMIC PROCESS WHERE (Name="cmd.exe" AND ProcessID="<<@LookFor>>") GET CommandLine,ProcessId'

    ,'<<@LookFor>>',@LookFor

    )

    ELSE REPLACE(

    'WMIC PROCESS WHERE (Name="cmd.exe" AND CommandLine LIKE "%/c <<@LookFor>>") '

    +'GET CommandLine,ProcessId'

    ,'<<@LookFor>>',@LookFor

    )

    END

    ;

    --===== Store the processes we're looking for in a working table.

    INSERT INTO #CmdResponse

    (CmdOutput)

    EXEC xp_CmdShell @Cmd

    ;

    --=====================================================================================================================

    -- Desired @Action = 'List'

    -- Just list everything we found and exit gracefully.

    --=====================================================================================================================

    --===== List all of the processes we found no matter what the desired action is.

    SELECT *

    FROM #CmdResponse

    WHERE CmdOutput > ''

    AND CmdOutput NOT LIKE '%/c WMIC PROCESS WHERE (Name="cmd.exe"%GET CommandLine,ProcessId%'

    ORDER BY Line#

    ;

    --===== If the desired action is simply to list the processes we found, exit now.

    IF @Action = 'List' RETURN 0

    ;

    --=====================================================================================================================

    -- Desired @Action = 'Kill' (assumed after fallthrough if 'List' wasn't the action from above.

    -- Kill all processes that were found.

    --=====================================================================================================================

    --===== Find where the floating ProcessId column starts so that we can isolate the ProcessIds to kill.

    SELECT @ProcessIdStart = CHARINDEX('ProcessId',CmdOutput)

    FROM #CmdResponse

    WHERE Line# = 1

    ;

    --===== Build the dynamic DOS statements we need to kill the found processes.

    -- /f = Force termination

    -- /t = Terminate child processes, as well.

    SELECT @CmdKill = ISNULL(@CmdKill,'')

    + REPLACE('EXEC xp_CmdShell ''taskkill /f /t /fi "pid eq <<ProcessID>>"'''+CHAR(10)

    , '<<ProcessID>>'

    , RTRIM(REPLACE(REPLACE(SUBSTRING(CmdOutput,@ProcessIdStart,8000),CHAR(10),''),CHAR(13),''))

    )

    FROM #CmdResponse

    WHERE CmdOutput NOT LIKE '%/c WMIC PROCESS WHERE (Name="cmd.exe"%GET CommandLine,ProcessId%'

    AND CmdOutput > ''

    AND Line# > 1

    ;

    --===== Warn the operator of what is about to happen.

    SELECT [READ ME NOW!!! ] =

    '*** WARNING *** WARNING *** WARNING *** WARNING *** WARNING *** WARNING ***' UNION ALL

    SELECT ' YOU ARE ABOUT TO KILL ALL OF THE PROCESSES LISTED ABOVE.' UNION ALL

    SELECT ' YOU HAVE 20 SECONDS TO ABORT BY CLICKING THE STOP BUTTON.' UNION ALL

    SELECT '*** WARNING *** WARNING *** WARNING *** WARNING *** WARNING *** WARNING ***'

    ;

    RAISERROR('READ THE GRID NOW IF IT''S ACTIVE!!!',0,1) WITH NOWAIT;

    --===== Now, give the operator some time to read and react to the warning

    WAITFOR DELAY '00:00:20'

    --===== TIME'S UP! Waste 'em!

    EXEC (@CmdKill)

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Hany Helmy

    SSChampion

    Points: 13321

    Hi,
    Please note that when xp_cmdshell is called by a user that is not a member of the sysadmin fixed server role, it connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

    So the proper solution will be as follows:
    Update the credential ##xp_cmdshell_proxy_account## with Windows account that has admin privileges on the database (ideally it should be the SQL service account).

    Best regards,
    Hany

  • Jeff Moden

    SSC Guru

    Points: 994667

    Hany Helmy - Wednesday, September 27, 2017 2:31 AM

    Hi,
    Please note that when xp_cmdshell is called by a user that is not a member of the sysadmin fixed server role, it connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

    So the proper solution will be as follows:
    Update the credential ##xp_cmdshell_proxy_account## with Windows account that has admin privileges on the database (ideally it should be the SQL service account).

    Best regards,
    Hany

    NEVER!!!!  DON"T EVER DO THIS!!!  YOU'RE GIVING A USER THE KEYS TO THE CITY!

    Learn to write a protected stored procedure that does the job correctly and in a highly protected fashion and then give the user the privs to execute that proc.  NEVER GIVE USERS privs to run XP_CMDSHELL directly.  NEVER give the proxy admin privs.  And watch for "DOS INJECTION" as well!

    Just so you know where I'm coming from, I'm a very strong advocate of using xp_CmdShell but, like anything else, IT MUST BE DONE PROPERLY.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply