(SQL Server) Create a Hash Signature for Value Added Tax Audit SOAP Message for Angola

Demonstrates how to create a hash signature that is required in Value Added Tax Audit Files in Angola, Africa.

It is the following two instructions that should be used to create a hash value to be put into a <hash></hash> field in SOAP XML file containing the tax data for the VAT authorities:

openssl dgst -shal -sign private.pem -outRegisto1.shal Registo1.txt

then

openssl enc -base64 - in Registo1.shal -out Registo1.b64 -A

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @pkey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @pkey OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load the private key from an PEM file.
    -- Provide the password in the 2nd argument.
    -- If no password is needed, just pass an empty string.
    DECLARE @success int
    EXEC sp_OAMethod @pkey, 'LoadAnyFormatFile', @success OUT, 'qa_data/rsa/rsaPrivKey_secret.pem', 'secret'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @pkey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkey
        RETURN
      END

    -- Assuming this is an RSA private key..
    DECLARE @rsa int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT

    -- Import the private key into the RSA object:
    EXEC sp_OAMethod @rsa, 'ImportPrivateKeyObj', @success OUT, @pkey
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkey
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- OpenSSL uses big-endian.
    EXEC sp_OASetProperty @rsa, 'LittleEndian', 0

    -- SHA1 hash and sign, and return base64.
    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
    DECLARE @b64sig nvarchar(4000)
    EXEC sp_OAMethod @rsa, 'SignStringENC', @b64sig OUT, '2018-05-18,2018-05-18T11:22:19;FAC 001/18;53.00;', 'sha1'

    PRINT @b64sig

    -- The signature is equal in size to your private key.  For example, if you have a 1024-bit RSA key,
    -- then 1024 bits = 128 bytes.   When 128 bytes is base64 encoded, the result is a string approximately
    -- 4/3rds the size of the original.  128 * 4 / 3 = (approximately) 172 bytes.

    -- If your RSA key is 2048-bits, then your signature will be twice as long.

    EXEC @hr = sp_OADestroy @pkey
    EXEC @hr = sp_OADestroy @rsa


END
GO