Create a signed AWS API request to a Lambda Function URL from Excel (VBA)

I have an AWS Lambda function with a function URL. Its auth type is AWS_IAM and I have credentials. Currently, this Lambda takes two numbers as input and returns their sum (placeholder for future complexity once I solve this first issue). I want to access the AWS Lambda from Excel, using VBA.

If I turn the auth off, I can successfully send and receive a response. Further, I was able to send a signed request using Python. However, I need the code in VBA (so I can distribute to my users without requiring them to install Python).

I am getting a return message of "The request signature we calculated does not match the signature you provided. Check your AWS Secret Access Key and signing method. Consult the service documentation." I consulted. This led me to the Python solution, but I haven't been able to get the VBA to work. Note: If I set the timestamp to a fixed time, I am getting identical signatures in Python and VBA.

VBA:

Sub CallLambdaFunctionWithIAM()
    Dim req As New WinHttpRequest
    Dim url As String
    Dim trim_url As String
    Dim input_data, input_data_json As String
    Dim access_key As String
    Dim secret_key As String
    Dim a, b As Variant
    
    url = "https://myurlhere.lambda-url.eu-central-1.on.aws/"
    trim_url = "myurlhere.lambda-url.eu-central-1.on.aws"
    
    a = ThisWorkbook.Names("a").RefersToRange.Value
    b = ThisWorkbook.Names("b").RefersToRange.Value
    
    input_data = "{'a': '" & a & "', 'b': '" & b & "'}"
    input_data_json = Replace(input_data, "'", Chr(34))

    access_key = "accesskey"
    secret_key = "secretkey"
    
    ' Generate a signature for the request
    Dim timestamp As String
    Dim signature As String
    timestamp = Format(DateAdd("h", -1, Now()), "yyyyMMddTHHmmssZ")
    Debug.Print timestamp
    Debug.Print datestamp(timestamp)
    signature = GenerateSignature(trim_url, "POST", input_data_json, access_key, secret_key, timestamp)
    
    Debug.Print "signature:  " & signature
       
    With req
        .Open "POST", url, False
        .SetRequestHeader "content-type", "application/json"
        .SetRequestHeader "host", trim_url
        .SetRequestHeader "x-amz-date", timestamp
        .SetRequestHeader "authorization", "AWS4-HMAC-SHA256 Credential=" & access_key & "/" & GetScope(timestamp) & ", SignedHeaders=content-type;host;x-amz-date, Signature=" & signature
        .Send input_data
    End With
    
    ' Parse the response and extract the output data
    Dim output_data As String

    output_data = req.ResponseText
    Debug.Print "Return message:  " & output_data
    Debug.Print "ResponseHeaders:  " & req.GetAllResponseHeaders

End Sub

Function GenerateSignature(url As String, method As String, data As String, access_key As String, secret_key As String, timestamp As String) As String
    ' Generate a signature for the given request
    
    ' Compute the required hash values
    Dim service As String
    Dim region As String
    Dim canonical_uri As String
    Dim canonical_querystring As String
    Dim canonical_headers As String
    Dim signed_headers As String
    Dim payload_hash As String
    
    service = "lambda"
    region = "eu-central-1"
    canonical_uri = "/"
    canonical_querystring = ""
    canonical_headers = "content-type:application/json" & Chr(10) & "host:" & url & Chr(10) & "x-amz-date:" & timestamp & Chr(10)
    signed_headers = "content-type;host;x-amz-date"
    payload_hash = HexString(HashBytes(data))
    Debug.Print "payload_hash:  " & payload_hash
    
    
    ' Compute the string-to-sign and the signing key
    Dim scope As String
    Dim string_to_sign As String
    Dim signing_key() As Byte
    Dim kDate() As Byte
    Dim kRegion() As Byte
    Dim kService() As Byte
    Dim kSigning() As Byte
    Dim asc As Object
    Set asc = CreateObject("System.Text.UTF8Encoding")
    
    
    scope = GetScope(timestamp)
    string_to_sign = "AWS4-HMAC-SHA256" & Chr(10) & timestamp & Chr(10) & scope & Chr(10) & canonical_request(url, method, data, canonical_uri, canonical_querystring, canonical_headers, signed_headers, payload_hash)

    signing_key = asc.getbytes_4("AWS4" & secret_key)
    kDate = HmacSHA256(signing_key, datestamp(timestamp))
    kRegion = HmacSHA256(kDate, region)
    kService = HmacSHA256(kRegion, service)
    kSigning = HmacSHA256(kService, "aws4_request")
    
    ' Compute the signature
    GenerateSignature = HexString(HmacSHA256(kSigning, string_to_sign))
    
End Function

Function canonical_request(url As String, method As String, data As String, canonical_uri As String, canonical_querystring As String, canonical_headers As String, signed_headers As String, payload_hash As String) As String
    ' Generate the canonical request for the given inputs

    canonical_request = method & Chr(10) & canonical_uri & Chr(10) & canonical_querystring & Chr(10) & canonical_headers & Chr(10) & signed_headers & Chr(10) & payload_hash
    canonical_request = HexString(HashBytes(canonical_request))
    Debug.Print "canonical_request (Hash & Hex):  " & canonical_request


End Function

Function GetScope(timestamp As String) As String
    ' Generate the scope for the given timestamp   
    GetScope = datestamp(timestamp) & "/eu-central-1/lambda/aws4_request"
End Function

Function datestamp(timestamp As String) As String
    ' Generate the datestamp for the given timestamp
    datestamp = Left(timestamp, 8)

End Function

Function HmacSHA256(key() As Byte, message As String) As Byte()
    ' Compute the HMAC-SHA256 digest for the given key and message
    Dim sha As Object
    Set sha = CreateObject("System.Security.Cryptography.HMACSHA256")
    sha.key = key
    
    Dim message_bytes() As Byte
    message_bytes = StrConv(message, vbFromUnicode)
    
    HmacSHA256 = sha.ComputeHash_2(message_bytes)
End Function


Function HashBytes(data As String) As Byte()
    ' Compute the SHA256 hash for the given data
    
    Dim sha As Object
    Set sha = CreateObject("System.Security.Cryptography.SHA256Managed")
    
    Dim data_bytes() As Byte
    data_bytes = StrConv(data, vbFromUnicode)
    
    HashBytes = sha.ComputeHash_2(data_bytes)
End Function

Function HexString(bytes() As Byte) As String
    ' Convert a byte array to a hex string
    Dim i As Long
    Dim temp As String
    For i = LBound(bytes) To UBound(bytes)
        temp = Hex(bytes(i))
        If Len(temp) = 1 Then temp = "0" & temp
        HexString = HexString & temp
    Next i
    HexString = LCase(HexString)
End Function

Here's the Python code which is working.

import requests
import datetime
import hashlib
import hmac
import json

# AWS IAM credentials
access_key = "accesskey"
secret_key = "secretkey"
region = "eu-central-1"
service = "lambda"

# Request URL
url = "https://myurlhere.lambda-url.eu-central-1.on.aws/"

# Request headers
headers = {
    "Content-Type": "application/json",
}

# Request body
body = {
    "a": "1",
    "b": "3"
}

# Create a datetime object for the request timestamp
timestamp = datetime.datetime.utcnow().strftime("%Y%m%dT%H%M%SZ")

# Create a date object for the request date
datestamp = datetime.datetime.utcnow().strftime("%Y%m%d")

# Construct the canonical request string
http_method = "POST"
canonical_uri = "/"
canonical_querystring = ""
canonical_headers = "content-type:" + headers["Content-Type"] + "\n" + "host:" + url.split("/")[2] + "\n" + "x-amz-date:" + timestamp + "\n"
signed_headers = "content-type;host;x-amz-date"
payload_hash = hashlib.sha256(json.dumps(body).encode("UTF-8")).hexdigest()
print("payload_hash:  " + str(payload_hash))
canonical_request = http_method + "\n" + canonical_uri + "\n" + canonical_querystring + "\n" + canonical_headers + "\n" + signed_headers + "\n" + payload_hash
print("canonical_request:  "+str(hashlib.sha256(canonical_request.encode("UTF-8")).hexdigest())) 

# Construct the string to sign
algorithm = "AWS4-HMAC-SHA256"
credential_scope = datestamp + "/" + region + "/" + service + "/" + "aws4_request"
string_to_sign = algorithm + "\n" +  timestamp + "\n" +  credential_scope + "\n" +  hashlib.sha256(canonical_request.encode("UTF-8")).hexdigest()

# Derive the signing key
def sign(key, msg):
    return hmac.new(key, msg.encode('utf-8'), hashlib.sha256).digest()

kDate = sign(("AWS4" + secret_key).encode('utf-8'), datestamp)
kRegion = sign(kDate, region)
kService = sign(kRegion, service)
kSigning = sign(kService, "aws4_request")

# Calculate the signature
signature = hmac.new(kSigning, string_to_sign.encode('utf-8'), hashlib.sha256).hexdigest()
print("signature: "+"\n"+str(signature))

# Add the required authorization headers to the request
headers["Authorization"] = algorithm + " " + "Credential=" + access_key + "/" + credential_scope + ", " +  "SignedHeaders=" + signed_headers + ", " + "Signature=" + signature
headers["X-Amz-Date"] = timestamp

print("headers: "+"\n"+str(headers))

# Send the signed request to the Lambda function URL
response = requests.post(url, headers=headers, json=body)

# Print the response
print(response.text)
print(response.headers)

I suspect it's something to do with my request headers, but I'm out of ideas.

I've "solved" this, although I'm not happy with it. When I replace the WinHttpRequest with a MSXML2.serverXMLHTTP request, the code works. I don't know why or how.

Prior to that, I had multiple calls with aws support. They were only a little helpful, but my biggest take-away is that they're unable to trace an x-amzn-RequestId.

Other ideas were that the issue was caused by a proxy making an unwanted change in the request, but I was unable to trace the request because I don't have the admin rights to do so.

Long story short, I can now send data to my Lambda and get a calculated response back. I'm not happy that I spent weeks on this and don't really have an answer as to why this works now.

Edit: If I understand correctly, MSXML2 provides a wrapper over WinHttp which has some sort of proxy management. That could be why the XMLHTTP request is working while the WinHttp request is not.