InvalidSignatureException Sending POST request to AWS Kinesis from SQL

Question

I'm trying to send data to my kinesis stream from SQL but I'm only able to get back the error.

{
    "__type": "InvalidSignatureException",
    "message": "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 for details."
}

My key & secret are correct as I've done a successful test in Postman.

Here is my function for creating the Authentication header...

ALTER FUNCTION [dbo].[CreateAuth]
(
    @awsAccessKey NVARCHAR(MAX),
    @awsSecretKey NVARCHAR(MAX),
    @content NVARCHAR(MAX),
    @dateTime Datetime
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE @awsRegion NVARCHAR(MAX) = 'eu-west-2';
    DECLARE @awsService NVARCHAR(MAX) = 'kinesis';
    DECLARE @timeStamp NVARCHAR(16) = FORMAT(@dateTime, 'yyyyMMddTHHmmssZ');
    DECLARE @scope NVARCHAR(MAX) = FORMAT(@dateTime, 'yyyyMMdd') + '/'+@awsRegion+'/'+@awsService+'/aws4_request'

    DECLARE @x_amz_content_sha256 NVARCHAR(MAX);
    DECLARE @hexbin VARBINARY(max) =  HASHBYTES('SHA2_256 ',@content);   

    SET @x_amz_content_sha256 = LOWER(CONVERT([varchar](512), @hexbin,2))
    
    -- CANONICAL REQUEST
        DECLARE @CanonicalRequest NVARCHAR(MAX) = '';
        -- HTTP verb
        SET @CanonicalRequest += 'POST' + CHAR(13) 
        -- URL
        SET @CanonicalRequest += 'kinesis.eu-west-2.amazonaws.com' + CHAR(13) 

        -- QUERYSTRING (must be sorted alphbetically)
        SET @CanonicalRequest += '' + CHAR(13) 

        -- HEADERS (must be sorted alphbetically)
        SET @CanonicalRequest += 'content-type:application/x-amz-json-1.1' + CHAR(13) 
        SET @CanonicalRequest += 'host:kinesis.eu-west-2.amazonaws.com' + CHAR(13) 
        SET @CanonicalRequest += 'x-amz-content-sha256:' + @x_amz_content_sha256 + CHAR(13) 
        SET @CanonicalRequest += 'x-amz-date:' + @timeStamp + CHAR(13) 
        SET @CanonicalRequest += 'x-amz-target:' + 'Kinesis_20131202.PutRecord' + CHAR(13) 

        -- SIGNED HEADERS
        DECLARE @signedheaders NVARCHAR(MAX) = 'content-type;host;x-amz-content-sha256;x-amz-date;x-amz-target'
        SET @CanonicalRequest += @signedheaders + CHAR(13) 

        -- HASHED PAYLOAD
        SET @CanonicalRequest += @x_amz_content_sha256

        DECLARE @CanonicalRequestHexbin VARBINARY(max) = HASHBYTES('SHA2_256 ',@CanonicalRequest);  

    -- STRING TO SIGN
        DECLARE @stringToSign NVARCHAR(MAX) = '';
        SET @stringToSign += 'AWS4-HMAC-SHA256' + CHAR(13) 
        SET @stringToSign += @timeStamp + CHAR(13) 
        SET @stringToSign += @scope + CHAR(13) 
        SET @stringToSign += LOWER(CONVERT([varchar](512), @CanonicalRequestHexbin,2))

    -- CALCULATE SIGNATURE
        DECLARE @DateKey VARBINARY(64); 
        DECLARE @DateRegionKey VARBINARY(64);
        DECLARE @DateRegionServiceKey VARBINARY(64);
        DECLARE @SigningKey VARBINARY(64);
        DECLARE @Signature VARBINARY(64);

        SET @DateKey                = dbo.HMAC('SHA2_256',CONVERT(VARBINARY(MAX), 'AWS4'+@awsSecretKey),CONVERT(VARBINARY(MAX),FORMAT(@dateTime, 'yyyyMMdd')))
        SET @DateRegionKey          = dbo.HMAC('SHA2_256', @DateKey,                CONVERT(VARBINARY(MAX),@awsRegion))
        SET @DateRegionServiceKey   = dbo.HMAC('SHA2_256', @DateRegionKey,          CONVERT(VARBINARY(MAX),@awsService))
        SET @SigningKey             = dbo.HMAC('SHA2_256', @DateRegionServiceKey,   CONVERT(VARBINARY(MAX),'aws4_request'))

        SET @Signature = dbo.HMAC('SHA2_256',@SigningKey,CONVERT(VARBINARY(MAX),@stringToSign));

    --BUILD Authorization
    DECLARE @AuthValue NVARCHAR(MAX) = '';
    SET @AuthValue += 'AWS4-HMAC-SHA256 Credential=' + @awsAccessKey + '/'+ @scope 
    SET @AuthValue += ',SignedHeaders=' + @signedheaders 
    SET @AuthValue += ',Signature=' + LOWER(CONVERT([varchar](512), @Signature,2))

    RETURN @AuthValue

And here is the call to kinesis

-- Open the connection.
    EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
    IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.
    EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
    SET @auth = dbo.CreateAuth('MYACCESSKEY','MYSECRETKEY',@postData, @datetime)
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @auth

    PRINT @auth
    PRINT ''

    DECLARE @hexbin VARBINARY(max) =  HASHBYTES('SHA2_256 ',@postData); --hash data
    DECLARE @x_amz_content_sha256 NVARCHAR(MAX) = LOWER(CONVERT([varchar](512), @hexbin,2)) -- get hex of hashed data
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'content-type', @contentType;
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'host', 'kinesis.eu-west-2.amazonaws.com';
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'x-amz-content-sha256', @x_amz_content_sha256;
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'x-amz-date', @xAmzDate;
    EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'x-amz-target', @xAmzTarget;
    EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;

-- Handle the response.
    EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
    EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
    EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.
    PRINT 'Status: ' + @status + ' (' + @statusText + ')';
    PRINT 'Response text: ' + @responseText;

-- Close the connection.
    EXEC @ret = sp_OADestroy @token;
    IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);

The HMAC function I'm using is this one... https://gist.github.com/rmalayter/3130462 I've verified that the results produced with the SHA2_256 & HMAC functions are correct using https://codebeautify.org/

I'm pretty sure I'm doing everything according to... https://docs.aws.amazon.com/general/latest/gr/sigv4_signing.html and https://docs.aws.amazon.com/AmazonS3/latest/API/sig-v4-header-based-auth.html

I'm lost as to what to look try next.

Answer 1

I've solved it now. There were a number of problems aside from my lazy hard coding.

• CanonicalURI should just be '/'. - thanks @AlwayLearning.
• Changed all string variables to VARCHAR rather than NVARCHAR, this was causing incorrect hashing.
• The date used for the @datekey also needs to be a VARCHAR CONVERT(VARCHAR(MAX),FORMAT(@dateTime, 'yyyyMMdd')) as the FORMAT() function returns a NVARCHAR.

And I found this site which was very helpful http://aws-signature.com.s3-website-us-west-2.amazonaws.com/

Successful function now looks like this...

ALTER FUNCTION [dbo].[CreateAuth]
(
    @awsAccessKey VARCHAR(MAX),
    @awsSecretKey VARCHAR(MAX),
    @content VARCHAR(MAX),
    @dateTime Datetime,
    @awsRegion VARCHAR(MAX),
    @awsService VARCHAR(MAX),
    @CanonicalRequestURI VARCHAR(MAX),
    @host VARCHAR(MAX),
    @xAmzTarget VARCHAR(MAX),
    @contentType VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @date VARCHAR(8) = FORMAT(@dateTime, 'yyyyMMdd')
    DECLARE @timeStamp VARCHAR(16) = FORMAT(@dateTime, 'yyyyMMddTHHmmssZ');
    DECLARE @scope VARCHAR(MAX) = @date + '/'+@awsRegion+'/'+@awsService+'/aws4_request'

    DECLARE @x_amz_content_sha256 VARCHAR(MAX);
    DECLARE @hexbin VARBINARY(max) =  HASHBYTES('SHA2_256 ',@content);   

    SET @x_amz_content_sha256 = LOWER(CONVERT([varchar](MAX), @hexbin,2))
    
    -- CANONICAL REQUEST
        DECLARE @CanonicalRequest VARCHAR(MAX) = '';
        -- HTTP verb
        SET @CanonicalRequest += 'POST' + CHAR(10) 
        -- URL
        SET @CanonicalRequest += @CanonicalRequestURI + CHAR(10) 

        -- QUERYSTRING (must be sorted alphbetically)
        SET @CanonicalRequest += '' + CHAR(10) 

        -- HEADERS (must be sorted alphbetically)
        SET @CanonicalRequest += 'content-type:' + @contentType + CHAR(10) 
        SET @CanonicalRequest += 'host:' + @host + CHAR(10) 
        SET @CanonicalRequest += 'x-amz-content-sha256:' + @x_amz_content_sha256 + CHAR(10) 
        SET @CanonicalRequest += 'x-amz-date:' + @timeStamp + CHAR(10) 
        SET @CanonicalRequest += 'x-amz-target:' + @xAmzTarget + CHAR(10) 
        SET @CanonicalRequest +=  CHAR(10)  

        -- SIGNED HEADERS
        DECLARE @signedheaders VARCHAR(MAX) = 'content-type;host;x-amz-content-sha256;x-amz-date;x-amz-target'
        SET @CanonicalRequest += @signedheaders + CHAR(10) 

        -- HASHED PAYLOAD
        SET @CanonicalRequest += @x_amz_content_sha256

        DECLARE @CanonicalRequestHexbin VARBINARY(max) = HASHBYTES('SHA2_256 ',@CanonicalRequest);  

    -- STRING TO SIGN
        DECLARE @stringToSign VARCHAR(MAX) = '';
        SET @stringToSign += 'AWS4-HMAC-SHA256' + CHAR(10) 
        SET @stringToSign += @timeStamp + CHAR(10) 
        SET @stringToSign += @scope + CHAR(10) 
        SET @stringToSign += LOWER(CONVERT([varchar](MAX), @CanonicalRequestHexbin,2))

    -- CALCULATE SIGNATURE
        DECLARE @DateKey VARBINARY(MAX); 
        DECLARE @DateRegionKey VARBINARY(MAX);
        DECLARE @DateRegionServiceKey VARBINARY(MAX);
        DECLARE @SigningKey VARBINARY(MAX);
        DECLARE @Signature VARBINARY(MAX);

        SET @DateKey                = dbo.HMAC('SHA2_256',CONVERT(VARBINARY(MAX), 'AWS4'+@awsSecretKey),CONVERT(VARBINARY(MAX),@date))
        SET @DateRegionKey          = dbo.HMAC('SHA2_256', @DateKey,                CONVERT(VARBINARY(MAX),@awsRegion))
        SET @DateRegionServiceKey   = dbo.HMAC('SHA2_256', @DateRegionKey,          CONVERT(VARBINARY(MAX),@awsService))
        SET @SigningKey             = dbo.HMAC('SHA2_256', @DateRegionServiceKey,   CONVERT(VARBINARY(MAX),'aws4_request'))

        SET @Signature = dbo.HMAC('SHA2_256',@SigningKey,CONVERT(VARBINARY(MAX),@stringToSign));

    --BUILD Authorization
    DECLARE @AuthValue VARCHAR(MAX) = '';
    SET @AuthValue += 'AWS4-HMAC-SHA256 Credential=' + @awsAccessKey + '/'+ @scope 
    SET @AuthValue += ',SignedHeaders=' + @signedheaders 
    SET @AuthValue += ',Signature=' + LOWER(CONVERT([varchar](MAX), @Signature,2))

    RETURN @AuthValue
    
END