(SQL Server) ABN AMRO Create Signed JSON Web Token

Demonstrates how to create a signed JWT to be used for authenticating requests to the ABN AMRO REST API's.

For more information, see https://developer.abnamro.com/get-started#headingFive

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Create public/private key pair (RSA)
    DECLARE @rsa int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Generate a 2048-bit key.
    DECLARE @success int
    EXEC sp_OAMethod @rsa, 'GenerateKey', @success OUT, 2048
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- Export the key to PEM files.
    -- Write one PEM file for the private key, and one for the public key.
    DECLARE @privkey int
    EXEC sp_OAMethod @rsa, 'ExportPrivateKeyObj', @privkey OUT
    EXEC sp_OAMethod @privkey, 'SavePemFile', @success OUT, 'qa_data/pem/abnAmroPrivateKey.pem'

    DECLARE @pubkey int
    EXEC sp_OAMethod @rsa, 'ExportPublicKeyObj', @pubkey OUT
    EXEC sp_OAMethod @pubkey, 'SavePemFile', @success OUT, 1, 'qa_data/pem/abnAmroPublicKey.pem'
    -- Note: Please share your public key along with your app name and developer email id at api.support@nl.abnamro.com. 
    -- Token generation will not work unless public key is associated with your app.

    -- Create the JWT.
    DECLARE @jwt int
    -- Use "Chilkat_9_5_0.Jwt" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Jwt', @jwt OUT

    -- Create the header:
    -- {
    --     "typ": "JWT",
    --     "alg": "RS256"
    -- }
    DECLARE @jsonHeader int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonHeader OUT

    EXEC sp_OAMethod @jsonHeader, 'UpdateString', @success OUT, 'typ', 'JWT'
    EXEC sp_OAMethod @jsonHeader, 'UpdateString', @success OUT, 'alg', 'RS256'

    -- Create the payload:
    -- {
    --     "nbf": 1499947668,
    --     "exp": 1499948668,
    --     "iss": "me",
    --     "sub": "anApiKey",
    --     "aud": "https://auth-sandbox.abnamro.com/oauth/token"
    -- }
    DECLARE @jsonPayload int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonPayload OUT

    DECLARE @curDateTime int
    EXEC sp_OAMethod @jwt, 'GenNumericDate', @curDateTime OUT, 0

    -- Set the "not process before" timestamp to now.
    EXEC sp_OAMethod @jsonPayload, 'AddIntAt', @success OUT, -1, 'nbf', @curDateTime

    -- Set the timestamp defining an expiration time (end time) for the token
    -- to be now + 1 hour (3600 seconds)
    EXEC sp_OAMethod @jsonPayload, 'AddIntAt', @success OUT, -1, 'exp', @curDateTime + 3600

    EXEC sp_OAMethod @jsonPayload, 'UpdateString', @success OUT, 'iss', 'me'
    EXEC sp_OAMethod @jsonPayload, 'UpdateString', @success OUT, 'sub', 'anApiKey'
    EXEC sp_OAMethod @jsonPayload, 'UpdateString', @success OUT, 'aud', 'https://auth-sandbox.abnamro.com/oauth/token'

    -- Produce the smallest possible JWT:
    EXEC sp_OASetProperty @jwt, 'AutoCompact', 1

    DECLARE @jwtStr nvarchar(4000)
    EXEC sp_OAMethod @jsonHeader, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @jsonPayload, 'Emit', @sTmp1 OUT
    EXEC sp_OAMethod @jwt, 'CreateJwtPk', @jwtStr OUT, @sTmp0, @sTmp1, @privkey
    EXEC sp_OAGetProperty @jwt, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @jwt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @jsonHeader
        EXEC @hr = sp_OADestroy @jsonPayload
        RETURN
      END

    EXEC @hr = sp_OADestroy @privkey

    EXEC @hr = sp_OADestroy @pubkey

    -- Here is the JWT:

    PRINT @jwtStr

    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @jwt
    EXEC @hr = sp_OADestroy @jsonHeader
    EXEC @hr = sp_OADestroy @jsonPayload


END
GO