Friday, October 28, 2011

0 Trouble shooting xp_cmdshell

xp_cmdshell is Microsoft extend store procedure. It will spawn the cmd shell under sql server process to let user to run any command or script under windows host. By default it is disable on the sql server.

Enable xp_cmdshell

  1. EXEC master.dbo.sp_configure 'show advanced options', 1
  2. RECONFIGURE
  3. EXEC master.dbo.sp_configure 'xp_cmdshell', 1
  4. RECONFIGURE

Lets see how that works, Below code will ping the loop back interface and sleep for 500 secs

  1. xp_cmdshell "ping -n 500 127.0.0.1 > nul"

From procexp, you can see sql server has spawn the child process cmd and it is running the same credential as SQL server account.

image

By default, only sysadmin can use xp_cmdshell. As alternative, we can create the sp_xp_cmdshell_proxy_account to allow user to run it.

When I tried the sp_xp_cmdshell_proxy_account, I got below error

  1. EXEC sp_xp_cmdshell_proxy_account 'PO7\summer','XXXXXXXXXX'

Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
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: '0'.

So I tried to look around the error message, the dm_os_ring_buffer usually store the os feed back information.

  1.   select r.ring_buffer_address,
  2. r.ring_buffer_type,  
  3.   dateadd (ms, r.[timestamp] - sys.ms_ticks, getdate()) as record_time,
  4. cast(r.record as xml) record  
  5. from sys.dm_os_ring_buffers r  
  6. cross join sys.dm_os_sys_info sys

unfortunately, this time it does not tell us something we do not already know. ( Msg 15137, Level 16, State 1)

image

It is because the UAC policy on my Win7 cause the issue. To resolve the issue. Just run the SSMS under administrator mode.

Test/Verify xp_cmdshell

To test the proxy account, we need to grant the user permission to use xp_cmdshell too, otherwise you will get the error:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Below example use execute as to test the permission

  1. EXECUTE AS LOGIN = 'PO7\summer' ;
  2. GO
  3. use master;
  4. exec xp_cmdshell "whoami"
  5. REVERT ;

Here is the syntax to go grant the regular user to use xp_cmdshell through proxy account.

  1. USE [master]
  2. GO
  3. CREATE LOGIN [PO7\summer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
  4. GO
  5. GRANT EXECUTE ON xp_cmdshell TO [PO7\summer]

 

How to fix the error code 1326 when use xp_cmdshell

Since sp_xp_cmdshell_proxy_account store user’s password inside the sql server, if the windows account password get changed, the proxy account needs to be updated. Otherwise, it will get the error message as:

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: '1326'.

To fix the issue, run the

USE master;
ALTER CREDENTIAL [##xp_cmdshell_proxy_account## ] WITH IDENTITY = 'DOMAIN\USERID', 
    SECRET = 'PASSWORD';
GO

 

Change the credential does not require bounce the SQL server to make it effective.

Another scenario for error message is the account get drop and recreate in the domain, even the name is the same but the SSID has been changed. In this case, we have to drop the credential and recreate it.

select name,sid,type_desc,create_date,modify_date from sys.server_principals 

 

image

To drop and re create the credential:

sp_xp_cmdshell_proxy_account NULL;
sp_xp_cmdshell_proxy_account 'PO7\summer','XXXXXXXXXX';
GRANT EXECUTE ON xp_cmdshell TO [PO7\summer]

 

Trouble shooting XP_cmdshell hang

What happen if windows host hang or the xp_cmdshell just hang without returning the result?

Use sp_who2 to identify the spid

Use sys.dm_exec_requests and sys.dm_exec_sql_text to confirm it is the xp_cmdshell

  1. SELECT
  2.     session_id,
  3.     status,
  4.     command,
  5.     sql_handle,
  6.     database_id
  7. FROM
  8.     sys.dm_exec_requests
  9. WHERE
  10.     session_id = 60
  11. GO

 

  1. SELECT   
  2.     st.text
  3. FROM
  4.     sys.dm_exec_requests r
  5. CROSS APPLY
  6.     sys.dm_exec_sql_text(sql_handle) AS st
  7. WHERE
  8.     r.session_id = 60
  9. GO

Till now, we still have no way to identify what was xp_cmdshell is running

After issuing the kill command. The spid is in the roll back state.

image

You will find below in the sql server error log. The host process ID is process id where you issue the kill command NOT the child process id where the xp_xmdshell is running. image

The easiest way to get the hanging child process id is using procexp but the procexp is the GUI tool

The command line version of it is pslist

pslist \\hostnmae –t

image

taskkill /PID 8036 /F

 

Check proxy account for xp_cmdshell

select * from sys.credentials
  • sys.credentials store the ##xp_cmdshell_proxy_account## information, we can query it to verify whether proxy account is created or not.

: image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates