(SQL Server) Get Access Token using a Pre-Created JSON Web Token

Demonstrates how to get an access token using a pre-created JSON Web Token (JWT).

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.

    -- We're going to duplicate this CURL statement:
    -- curl -X POST https://api-sandbox.abnamro.com/v1/oauth/token \
    -- -H "Content-Type: application/x-www-form-urlencoded" \
    -- -H "API-Key: xxxxxx" \
    -- -d 'client_assertion_type=urn:ietf:params:oauth:client-assertion-type:jwt-bearer&grant_type=client_credentials&client_assertion=eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJ4eHh4eHgiLCJleHAiOiIxNDk5OTQ3NjY4IiwiaXNzIjoibWUiLCJhdWQiOiJodHRwczovL2F1dGgtc2FuZGJveC5hYm5hbXJvLmNvbS9vYXV0aC90b2tlbiJ9.jGwHKG_YjgKpR8NPpaLu6nJ97obeP2vcxg6fOWBKdJ0&scope=tikkie'

    -- Load our pre-creaed private key PEM file.
    -- 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.
    DECLARE @privkey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privkey OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @privkey, 'LoadPemFile', @success OUT, 'qa_data/pem/abnAmroPrivateKey.pem'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privkey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @privkey
        RETURN
      END

    -- 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 @privkey
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @jsonHeader
        EXEC @hr = sp_OADestroy @jsonPayload
        RETURN
      END

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    DECLARE @req int
    -- Use "Chilkat_9_5_0.HttpRequest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT

    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_assertion_type', 'urn:ietf:params:oauth:client-assertion-type:jwt-bearer'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'grant_type', 'client_credentials'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_assertion', @jwtStr
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'scope', 'tikkie'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostUrlEncoded', @resp OUT, 'https://api-sandbox.abnamro.com/v1/oauth/token', @req
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @privkey
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @jsonHeader
        EXEC @hr = sp_OADestroy @jsonPayload
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        RETURN
      END

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @resp

        EXEC @hr = sp_OADestroy @privkey
        EXEC @hr = sp_OADestroy @jwt
        EXEC @hr = sp_OADestroy @jsonHeader
        EXEC @hr = sp_OADestroy @jsonPayload
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        RETURN
      END

    -- Get the JSON result:
    -- {
    --     "access_token": "{your access token}",
    --     "expires_in": "{duration of validity in seconds}",
    --     "scope": "{scope(s) for which the access token is valid}",
    --     "token_type": "{it is always Bearer}"
    -- }
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'access_token'
    PRINT 'access_token: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'token_type'
    PRINT 'token_type: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'expires_in'
    PRINT 'expires_in: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'scope'
    PRINT 'scope: ' + @sTmp0

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @privkey
    EXEC @hr = sp_OADestroy @jwt
    EXEC @hr = sp_OADestroy @jsonHeader
    EXEC @hr = sp_OADestroy @jsonPayload
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @json


END
GO