SQL Server
SQL Server
Get Access Token using a Pre-Created JSON Web Token
See more ABN AMRO Examples
Demonstrates how to get an access token using a pre-created JSON Web Token (JWT).Chilkat SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- 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
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privkey OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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
EXEC @hr = sp_OACreate 'Chilkat.Jwt', @jwt OUT
-- Create the header:
-- {
-- "typ": "JWT",
-- "alg": "RS256"
-- }
DECLARE @jsonHeader int
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
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 = 0
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
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
DECLARE @req int
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'
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, 'https://api-sandbox.abnamro.com/v1/oauth/token', @req, @resp
IF @success = 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
EXEC @hr = sp_OADestroy @resp
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 @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 @resp
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
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 @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 @resp
EXEC @hr = sp_OADestroy @json
END
GO