Posted on Leave a comment

A call to ‘LogonUserW’ failed with error code: ‘1385’

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: ‘1385’.

1385 = Logon failure: the user has not been granted the requested logon type at this computer.

Solution:

  1. Look up the Windows user that is assigned to credential [##xp_cmdshell_proxy_account##]
  2. Run secpol.msc
  3. Navigate to “Local Policies” -> “User Rights Assignment” -> “Log on as a batch job”
  4. Add the user found from step 1 to “Log on as a batch job”
  5. Test by executing sql stored procedure: “EXEC master..xp_cmdshell ‘whoami'”

 

Troubleshooting xp_cmdshell failures

Posted on Leave a comment

Instant PRINTs

SQL “PRINT” statements getting buffered and not displaying until buffer is flushed with batch is done or gets full.

Using a RAISERROR with severity of 0 and “WITH NOWAIT” will not interrupt the batch, but will immediately display the output.

Here’s an example keeping it to one line and including a timestamp…

–for first msg in batch
DECLARE @msg nvarchar(2044) = convert(varchar(20),current_timestamp,120) + ‘ – Your First Message Here’; RAISERROR(@msg, 0, 1) WITH NOWAIT;

–for rest of msgs in batch
SET @msg = convert(varchar(20),current_timestamp,120) + ‘ – Subsequent Messages Here’; RAISERROR(@msg, 0, 1) WITH NOWAIT;

GO