SQL Database, Server Isolated Flat Web Mobile Icon with SQL word. Vector Illustration isolated on modern background
SQL Database, Server Isolated Flat Web Mobile Icon with SQL word. Vector Illustration isolated on modern background

Recently, i was invited by a senior developer to investigate an issue with the aforementioned result.

User was receiving the below error when tried to execute a specific Stored Procedure by using xp_cmdshell:

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1 [Batch Start Line 338]
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'.
 
Below is the portion of the code executed:

DECLARE @Command0   NVARCHAR (max) ;
DECLARE @Command    NVARCHAR (max) ;
DECLARE @sql        VARCHAR (8000) ;
DECLARE @DateSuffix VARCHAR(16) = REPLACE( REPLACE ( (CONVERT(VARCHAR(16),GETDATE(),121)),':','-'),' ','_') ;
DECLARE @filename   VARCHAR(200);

SET @Command0 ='Select' + CHAR(39)+ 'ENTCDE' + CHAR(39) + ',' + CHAR(39)+ 'Indication' + CHAR(39) + ',' + CHAR(39)+ 'TRN_Status' + CHAR(39)  ;
--Select @Command0
SET @Command  ='UNION ALL SELECT CAST (ENTCDE AS Char (20)) ,CAST (Indication AS Char (20)),CAST (TRN_Status AS Char (20)) FROM [Database].[dbo].[tbl_TEP_Email_Notification]' ;
--select CHAR(39)
--- Production
SET @filename ='\\Server\Data\Transfer_Reports\Transfer_Report_'+ CAST (@DateSuffix as CHAR (16)) + '.csv' ; 

SET @sql        ='bcp "' + @Command0 + @Command +'" queryout ' + @filename + '  -c -T'  ; 
--- SET @sql =  'bcp "' + @Command0 + @Command +'" queryout ' + @filename + ' -t , -c -T' --- with commas

EXECUTE master.dbo.xp_cmdshell @sql ;

By searching the error online, it was obvious that the user had wrong credentials.

User informed me that he was using an AD account and he had changed his credentials few days before. This was not adding much to the investigation, so i began checking all SQL Server’s related values, in an attempt to identify what was causing the inconvenience.

Suddenly, boom! This 2008R2 SQL server had Server Proxy account enabled!

After replacing the user’s password with the valid one, everything worked like a charm!

Last modified: December 16, 2020

Author

Comments

Write a Reply or Comment