sp_invoke_external_rest_endpoint to Azure Queue results in poisonous message

Question

From Azure SQL using sp_invoke_external_rest_endpoint a message is successfully queued in Azure Queue. The payload is <QueueMessage><MessageText>HelloWorld</MessageText></QueueMessage>.

When the Queue Triggered Azure function dequeues the message it ultimately lands in the poison queue.

If I take the same message, in this case HelloWorld, and add it via the portal (only works if the "Encode the message body in Base64" is checked), then the message successfully dequeues.

I've tried a variety of things to gain understanding of what is happening in the background, but I've run out of ideas.

This feels like an encoding problem. Any ideas on how best to solve this?

Below is some code if there is a desire to recreate the experience. SQL Code (Azure SQL Database)

DECLARE @QueueMessage_XML nvarchar(max) = N'<QueueMessage><MessageText>HelloWorld</MessageText></QueueMessage>';
DECLARE @QueueMessage nvarchar(max) = @QueueMessage_XML;

DECLARE @Time datetime = SYSUTCDATETIME();
DECLARE @FormattedMoment nvarchar(50) = FORMAT(@Time, 'ddd, dd MMM yyyy HH:mm:ss' ) + ' GMT';

DECLARE @ContentType nvarchar(256) = N'"content-type":"application/xml"';
DECLARE @Accept nvarchar(256) = N'"accept":"application/xml"';
DECLARE @StorageApiMsVersion nvarchar(256) = N'"x-ms-version":"2023-11-03"';
DECLARE @MsDate nvarchar(256) = N'"x-ms-date":"' + @FormattedMoment + N'"';

DECLARE @HeadersArray table([HeaderItem] nvarchar(256) NOT NULL);
INSERT INTO @HeadersArray ([HeaderItem]) VALUES 
    (@ContentType), 
    (@Accept), 
    (@StorageApiMsVersion), 
    (@MsDate);
DECLARE @Headers nvarchar(4000) = N'{' + (SELECT STRING_AGG([HeaderItem], ',') FROM @HeadersArray) + N'}';

DECLARE @Method nvarchar(6) = N'POST';
DECLARE @TimeoutSeconds int = 30;
DECLARE @Response nvarchar(max) = N'';
DECLARE @VisibilityTimeoutSeconds int = 20;

DECLARE @AccountName nvarchar(256)  = N'stacctname';
DECLARE @QueueName nvarchar(256)  = N'request';
DECLARE @QueueEndpoint nvarchar(256)  = N'https://' + @AccountName + N'.queue.core.windows.net';

DECLARE @Url nvarchar(4000) = @QueueEndpoint + N'/' + @QueueName + N'/messages';

DECLARE @ReturnValue int = 0;
EXEC @ReturnValue = sp_invoke_external_rest_endpoint
  @url = @Url,
  @payload = @QueueMessage,
  @headers = @Headers,
  @method = @Method,
  @credential = [https://stacctname.queue.core.windows.net],
  @timeout = @TimeoutSeconds,
  @response = @Response OUTPUT;

SELECT @ReturnValue AS [ReturnValue], @Response AS [Response];

Azure Function App Code

• Isolated
• C# .NET 8
• Visual Studio 2022 Version 17.9.0 Preview 1.0

using Azure.Storage.Queues.Models;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.Logging;

namespace FunctionApp;

public class DequeueRequest(ILogger<DequeueRequest> logger)
{
    private readonly ILogger<DequeueRequest> _logger = logger;

    [Function(nameof(DequeueRequest))]
    public void Run([QueueTrigger("request", Connection = "AzureWebJobsStorage")] QueueMessage message)
    {
        //intentionally muted
        _ = message.Body;
    }
}

UPDATE I've mostly narrowed this down to making sure the value is base64 encoded from within SQL BASE64_ENCODE(CAST(@Payload_String AS varbinary(max))). It would almost make this problem solved however when attempting to JSON serialize the string pulled off the queue it throws an error I can't trace. The raw string is valid and confirmed to pass serialization, so this is about a translation that occurs from being pulled off the queue and sent to the json serializer.

IOW, below is confirmed as successful deserialization

var request = JsonSerializer.Deserialize<Request>(
    @"{""RequestId"":80,""StartDate"":""1753-01-01T00:00:00"",""EndDate"":""9999-12-29T00:00:00""}")

below is failing & turns message into a poisonous one (where the message body is supposedly base64 string)

var request = JsonSerializer.Deserialize<Request>(message.Body.ToString());

Answer 1

SOLVED (at least a working solution. There may be more efficiencies or less "must-do" as alternative working solutions)

TLD;DR ...

• BASE64_ENCODE the non-unicode string that will serve as value of <MessageText>
• Place this encoded message in the xml format (as below) <QueueMessage><MessageText>base64_encoded_value</MessageText></QueueMessage>
• If the string value is from a FOR JSON statement, make sure the encoding is on a non-unicode representation of that value (This is key and not obvious as a reason for encoding problems).

My rudimentary empirical testing reveals that the BASE64_ENCODE MUST be a varbinary of a non-unicode value (at least for the characters needed for my problem).