(SQL Server) ING Open Banking OAuth2 Client Credentials

Demonstrates how to get an access token for the ING Open Banking APIs using client credentials.

For more information, see https://developer.ing.com/openbanking/get-started/openbanking

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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs_and_keys/ING/example_client_tls.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

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

    EXEC sp_OAMethod @bdPrivKey, 'LoadFile', @success OUT, 'qa_data/certs_and_keys/ING/example_client_tls.key'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load example_client_tls.key'
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdPrivKey
        RETURN
      END

    -- The OAuth 2.0 client_id for these certificates is e77d776b-90af-4684-bebc-521e5b2614dd. 
    -- Please note down this client_id since you will need it in the next steps to call the API.

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

    EXEC sp_OAMethod @privKey, 'LoadAnyFormat', @success OUT, @bdPrivKey, ''
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdPrivKey
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- Associate the private key with the certificate.
    EXEC sp_OAMethod @cert, 'SetPrivateKey', @success OUT, @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdPrivKey
        EXEC @hr = sp_OADestroy @privKey
        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

    EXEC sp_OAMethod @http, 'SetSslClientCert', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdPrivKey
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- Calculate the Digest and add the "Digest" header.  Do the equivalent of this:
    -- payload="grant_type=client_credentials"
    -- payloadDigest=`echo -n "$payload" | openssl dgst -binary -sha256 | openssl base64`
    -- digest=SHA-256=$payloadDigest
    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'SHA256'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    DECLARE @payload nvarchar(4000)
    SELECT @payload = 'grant_type=client_credentials'
    DECLARE @payloadDigest nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashStringENC', @payloadDigest OUT, @payload

    -- Calculate the current date/time and add the Date header.  
    -- reqDate=$(LC_TIME=en_US.UTF-8 date -u "+%a, %d %b %Y %H:%M:%S GMT")  
    DECLARE @dt int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
    -- The desire date/time format is the "RFC822" format.
    EXEC sp_OAMethod @dt, 'GetAsRfc822', @sTmp0 OUT, 0
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Date', @sTmp0

    -- Calculate signature for signing your request
    -- Duplicate the following code:

    -- 	httpMethod="post"
    -- 	reqPath="/oauth2/token"
    -- 	signingString="(request-target): $httpMethod $reqPath
    -- 	date: $reqDate
    -- 	digest: $digest"
    -- 	signature=`printf "$signingString" | openssl dgst -sha256 -sign "${certPath}example_client_signing.key" -passin "pass:changeit" | openssl base64 -A`

    DECLARE @httpMethod nvarchar(4000)
    SELECT @httpMethod = 'POST'
    DECLARE @reqPath nvarchar(4000)
    SELECT @reqPath = '/oauth2/token'

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

    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, '(request-target): '
    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, @httpMethod
    EXEC sp_OAMethod @sbStringToSign, 'ToLowercase', @success OUT
    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, ' '
    EXEC sp_OAMethod @sbStringToSign, 'AppendLine', @success OUT, @reqPath, 0

    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'date: '
    EXEC sp_OAMethod @dt, 'GetAsRfc822', @sTmp0 OUT, 0
    EXEC sp_OAMethod @sbStringToSign, 'AppendLine', @success OUT, @sTmp0, 0

    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'digest: SHA-256='
    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, @payloadDigest

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

    EXEC sp_OAMethod @signingPrivKey, 'LoadPemFile', @success OUT, 'qa_data/certs_and_keys/ING/example_client_signing.key'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @signingPrivKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdPrivKey
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @sbStringToSign
        EXEC @hr = sp_OADestroy @signingPrivKey
        RETURN
      END

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

    EXEC sp_OAMethod @rsa, 'ImportPrivateKeyObj', @success OUT, @signingPrivKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdPrivKey
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @sbStringToSign
        EXEC @hr = sp_OADestroy @signingPrivKey
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
    DECLARE @b64Signature nvarchar(4000)
    EXEC sp_OAMethod @sbStringToSign, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rsa, 'SignStringENC', @b64Signature OUT, @sTmp0, 'SHA256'

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

    EXEC sp_OAMethod @sbAuthHdrVal, 'Append', @success OUT, 'Signature keyId="e77d776b-90af-4684-bebc-521e5b2614dd",'
    EXEC sp_OAMethod @sbAuthHdrVal, 'Append', @success OUT, 'algorithm="rsa-sha256",'
    EXEC sp_OAMethod @sbAuthHdrVal, 'Append', @success OUT, 'headers="(request-target) date digest",'
    EXEC sp_OAMethod @sbAuthHdrVal, 'Append', @success OUT, 'signature="'
    EXEC sp_OAMethod @sbAuthHdrVal, 'Append', @success OUT, @b64Signature
    EXEC sp_OAMethod @sbAuthHdrVal, 'Append', @success OUT, '"'

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

    EXEC sp_OAMethod @sbDigestHdrVal, 'Append', @success OUT, 'SHA-256='
    EXEC sp_OAMethod @sbDigestHdrVal, 'Append', @success OUT, @payloadDigest

    -- Do the following CURL statement:

    -- 	curl -i -X POST "${httpHost}${reqPath}" \
    -- 	-H 'Accept: application/json' \
    -- 	-H 'Content-Type: application/x-www-form-urlencoded' \
    -- 	-H "Digest: ${digest}" \
    -- 	-H "Date: ${reqDate}" \
    -- 	-H "authorization: Signature keyId=\"$keyId\",algorithm=\"rsa-sha256\",headers=\"(request-target) date digest\",signature=\"$signature\"" \
    -- 	-d "${payload}" \
    -- 	--cert "${certPath}tlsCert.crt" \
    -- 	--key "${certPath}tlsCert.key"

    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, 'grant_type', 'client_credentials'
    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Accept', 'application/json'
    EXEC sp_OAMethod @dt, 'GetAsRfc822', @sTmp0 OUT, 0
    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Date', @sTmp0
    EXEC sp_OAMethod @sbDigestHdrVal, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Digest', @sTmp0
    EXEC sp_OAMethod @sbAuthHdrVal, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Authorization', @sTmp0
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostUrlEncoded', @resp OUT, 'https://api.sandbox.ing.com/oauth2/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 @cert
        EXEC @hr = sp_OADestroy @bdPrivKey
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @sbStringToSign
        EXEC @hr = sp_OADestroy @signingPrivKey
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @sbAuthHdrVal
        EXEC @hr = sp_OADestroy @sbDigestHdrVal
        EXEC @hr = sp_OADestroy @req
        RETURN
      END

    -- If successful, the status code = 200

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response Status Code: ' + @iTmp0
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    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 @hr = sp_OADestroy @resp

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- A successful response contains an access token such as:
    -- {
    --   "access_token": "eyJhbGc ... bxI_SoPOBH9xmoM",
    --   "expires_in": 905,
    --   "scope": "payment-requests:view payment-requests:create payment-requests:close greetings:view virtual-ledger-accounts:fund-reservation:create virtual-ledger-accounts:fund-reservation:delete virtual-ledger-accounts:balance:view",
    --   "token_type": "Bearer",
    --   "keys": [
    --     {
    --       "kty": "RSA",
    --       "n": "3l3rdz4...04VPkdV",
    --       "e": "AQAB",
    --       "use": "sig",
    --       "alg": "RS256",
    --       "x5t": "3c396700fc8cd709cf9cb5452a22bcde76985851"
    --     }
    --   ],
    --   "client_id": "e77d776b-90af-4684-bebc-521e5b2614dd"
    -- }

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    DECLARE @kty nvarchar(4000)

    DECLARE @n nvarchar(4000)

    DECLARE @e nvarchar(4000)

    DECLARE @use nvarchar(4000)

    DECLARE @alg nvarchar(4000)

    DECLARE @x5t nvarchar(4000)

    DECLARE @access_token nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @access_token OUT, 'access_token'
    DECLARE @expires_in int
    EXEC sp_OAMethod @json, 'IntOf', @expires_in OUT, 'expires_in'
    DECLARE @scope nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @scope OUT, 'scope'
    DECLARE @token_type nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @token_type OUT, 'token_type'
    DECLARE @client_id nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @client_id OUT, 'client_id'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'keys'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @kty OUT, 'keys[i].kty'
        EXEC sp_OAMethod @json, 'StringOf', @n OUT, 'keys[i].n'
        EXEC sp_OAMethod @json, 'StringOf', @e OUT, 'keys[i].e'
        EXEC sp_OAMethod @json, 'StringOf', @use OUT, 'keys[i].use'
        EXEC sp_OAMethod @json, 'StringOf', @alg OUT, 'keys[i].alg'
        EXEC sp_OAMethod @json, 'StringOf', @x5t OUT, 'keys[i].x5t'
        SELECT @i = @i + 1
      END

    -- This example will save the JSON containing the access key to a file so that
    -- a subsequent example can load it and then use the access key for a request, such as to create a payment request.
    EXEC sp_OAMethod @json, 'WriteFile', @success OUT, 'qa_data/tokens/ing_access_token.json'

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @bdPrivKey
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @sbStringToSign
    EXEC @hr = sp_OADestroy @signingPrivKey
    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @sbAuthHdrVal
    EXEC @hr = sp_OADestroy @sbDigestHdrVal
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @json


END
GO