SQL Server
SQL Server
ING Open Banking OAuth2 Client Credentials
See more OAuth2 Examples
Demonstrates how to get an access token for the ING Open Banking APIs using client credentials.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 @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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
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
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
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
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
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
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
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
EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
EXEC sp_OAMethod @rsa, 'UsePrivateKey', @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
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
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
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
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.ing.com/oauth2/token', @req, @resp
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
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 @resp
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
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_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 @resp
EXEC @hr = sp_OADestroy @json
END
GO