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!
Comments