July 2, 2008 at 4:53 am
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.
July 2, 2008 at 12:41 pm
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.
July 4, 2008 at 4:27 am
Toby,
thank you for your response. After restarting the service, all was well.
John P.
May 20, 2012 at 1:04 am
USE master;
GRANT CONTROL SERVER TO LoginName;
GO
GRANT EXECUTE on xp_cmdshell TO LoginName
November 4, 2015 at 11:19 am
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
November 4, 2015 at 12:17 pm
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.
November 4, 2015 at 3:58 pm
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
Change is inevitable... Change for the better is not.
November 5, 2015 at 7:33 am
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
Change is inevitable... Change for the better is not.
September 27, 2017 at 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
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 27, 2017 at 8:47 am
Hany Helmy - Wednesday, September 27, 2017 2:31 AMHi,
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply