SQL Server
SQL Server
Uni Economy API Client Credentials Flow
See more OAuth2 Examples
Demonstrates how to do OAuth 2.0 using the client credentials flow for the Uni Economy API. (This means that the server can authenticate against the identity server without human interaction.)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.
-- Step 1 ------------------------------------------------------------------------------------------
-- First create a client token...
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_OASetProperty @cert, 'VerboseLogging', 1
-- Note: .pfx and .p12 files are identical. The only difference is the file extension.
-- Also, if your .p12 password is longer than 64 chars, you'll need Chilkat v9.5.0.83 or later.
-- To shorten the password, import your .p12 onto your Windows computer by double-clicking on the .p12 file,
-- make sure when importing that keys are exportable, then re-export with private keys to a .pfx with a new password.
EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/UniCert_Norge_Test_secret.pfx', 'secret'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @privKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT
EXEC sp_OAMethod @cert, 'GetPrivateKey', @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 @privKey
RETURN
END
DECLARE @jwt int
EXEC @hr = sp_OACreate 'Chilkat.Jwt', @jwt OUT
-- Build the JOSE header
DECLARE @jose int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jose OUT
-- Use RS256. Pass the string "RS384" or "RS512" to use RSA with SHA-384 or SHA-512.
EXEC sp_OAMethod @jose, 'AppendString', @success OUT, 'alg', 'RS256'
EXEC sp_OAMethod @jose, 'AppendString', @success OUT, 'typ', 'JWT'
-- Now build the JWT claims (also known as the payload)
-- Our JWT claims will contain members as shown here:
-- {
-- "jti": "ad612fce-3e71-4f6a-8af1-7eb0414b4eea", <-- generated unique global identifier
-- "sub": "99999999-aaaa-bbbb-cccc-ddddeeeeffff", <-- This is the clientId
-- "iat": 1588102982, <-- These are date/time values.
-- "nbf": 1588102982,
-- "exp": 1588103042,
-- "iss": " 99999999-aaaa-bbbb-cccc-ddddeeeeffff",
-- "aud": "https://test-login.unieconomy.no/connect/token"
-- }
-- Use your own client ID.
DECLARE @myClientId nvarchar(4000)
SELECT @myClientId = '99999999-aaaa-bbbb-cccc-ddddeeeeffff'
DECLARE @claims int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @claims OUT
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
EXEC sp_OAMethod @crypt, 'GenerateUuid', @sTmp0 OUT
EXEC sp_OAMethod @claims, 'AppendString', @success OUT, 'jti', @sTmp0
EXEC sp_OAMethod @claims, 'AppendString', @success OUT, 'sub', @myClientId
-- Set the timestamp of when the JWT was created to now minus 60 seconds
DECLARE @curDateTime int
EXEC sp_OAMethod @jwt, 'GenNumericDate', @curDateTime OUT, -60
EXEC sp_OAMethod @claims, 'AddIntAt', @success OUT, -1, 'iat', @curDateTime
-- Set the "not process before" timestamp to now minus 60 seconds
EXEC sp_OAMethod @claims, '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 @claims, 'AddIntAt', @success OUT, -1, 'exp', @curDateTime + 3600
EXEC sp_OAMethod @claims, 'AppendString', @success OUT, 'iss', @myClientId
EXEC sp_OAMethod @claims, 'AppendString', @success OUT, 'aud', 'https://test-login.unieconomy.no/connect/token'
-- Produce the smallest possible JWT:
EXEC sp_OASetProperty @jwt, 'AutoCompact', 1
-- Create the JWT token. This is where the RSA signature is created.
DECLARE @jwt_token nvarchar(4000)
EXEC sp_OAMethod @jose, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @claims, 'Emit', @sTmp1 OUT
EXEC sp_OAMethod @jwt, 'CreateJwtPk', @jwt_token OUT, @sTmp0, @sTmp1, @privKey
PRINT @jwt_token
-- Step 2 ------------------------------------------------------------------------------------------
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
-- Fetch the discovery document...
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://test-login.unieconomy.no/.well-known/openid-configuration', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @jose
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Received response status code ' + @iTmp0
PRINT 'Response body containing error text or JSON:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @jose
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
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
-- We have the discovery document, which contains something like this:
-- You can use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "issuer": "https://test-login.unieconomy.no",
-- "jwks_uri": "https://test-login.unieconomy.no/.well-known/openid-configuration/jwks",
-- "authorization_endpoint": "https://test-login.unieconomy.no/connect/authorize",
-- "token_endpoint": "https://test-login.unieconomy.no/connect/token",
-- "userinfo_endpoint": "https://test-login.unieconomy.no/connect/userinfo",
-- "end_session_endpoint": "https://test-login.unieconomy.no/connect/endsession",
-- "check_session_iframe": "https://test-login.unieconomy.no/connect/checksession",
-- "revocation_endpoint": "https://test-login.unieconomy.no/connect/revocation",
-- "introspection_endpoint": "https://test-login.unieconomy.no/connect/introspect",
-- "device_authorization_endpoint": "https://test-login.unieconomy.no/connect/deviceauthorization",
-- "frontchannel_logout_supported": true,
-- "frontchannel_logout_session_supported": true,
-- "backchannel_logout_supported": true,
-- "backchannel_logout_session_supported": true,
-- "scopes_supported": [
-- "openid",
-- "profile",
-- "email",
-- "offline_access",
-- "AppFramework.All",
-- "AppFramework",
-- "AppFramework.Sales",
-- "IdentityAPI",
-- "widgetApi",
-- "TestScope.test",
-- "TestScope.Cars",
-- "HaglandAPI",
-- "LicenseAdmin",
-- "LicenseAdmin.Product.Read",
-- "SoftRig.Product.Write",
-- "TestAPI.test",
-- "offline_access"
-- ],
-- "claims_supported": [
-- "sub",
-- "updated_at",
-- "name",
-- "family_name",
-- "given_name",
-- "middle_name",
-- "nickname",
-- "preferred_username",
-- "picture",
-- "website",
-- "gender",
-- "birthdate",
-- "zoneinfo",
-- "locale",
-- "profile",
-- "email",
-- "email_verified"
-- ],
-- "grant_types_supported": [
-- "authorization_code",
-- "client_credentials",
-- "refresh_token",
-- "implicit",
-- "password",
-- "urn:ietf:params:oauth:grant-type:device_code",
-- "delegation"
-- ],
-- "response_types_supported": [
-- "code",
-- "token",
-- "id_token",
-- "id_token token",
-- "code id_token",
-- "code token",
-- "code id_token token"
-- ],
-- "response_modes_supported": [
-- "form_post",
-- "query",
-- "fragment"
-- ],
-- "token_endpoint_auth_methods_supported": [
-- "client_secret_basic",
-- "client_secret_post",
-- "private_key_jwt",
-- "private_key_jwt"
-- ],
-- "id_token_signing_alg_values_supported": [
-- "RS256"
-- ],
-- "subject_types_supported": [
-- "public"
-- ],
-- "code_challenge_methods_supported": [
-- "plain",
-- "S256"
-- ],
-- "request_parameter_supported": true
-- }
-- ------------------------------------------------------
-- The next steps are to (1) get the token_endpoint,
-- and (2) verify that the client_credentials grant type is supported.
DECLARE @tokenEndpoint nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @tokenEndpoint OUT, 'token_endpoint'
DECLARE @grantTypes int
EXEC sp_OAMethod @json, 'ArrayOf', @grantTypes OUT, 'grant_types_supported'
DECLARE @clientCredentialsIdx int
EXEC sp_OAMethod @grantTypes, 'FindString', @clientCredentialsIdx OUT, 'client_credentials', 1
EXEC @hr = sp_OADestroy @grantTypes
-- If clientCredentialsIdx is less then zero (-1) then the "client_credentials" string was not found.
IF @clientCredentialsIdx < 0
BEGIN
PRINT 'The client credentials grant type is not supported.'
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @jose
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
RETURN
END
-- ------------------------------------------------------
-- Request the access token using our Client ID and JWT
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_id', @myClientId
EXEC sp_OAMethod @req, 'AddParam', NULL, 'scope', 'AppFramework.Sales'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'grant_type', 'client_credentials'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_assertion_type', 'urn:ietf:params:oauth:client-assertion-type:jwt-bearer'
EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_assertion', @jwt_token
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, @tokenEndpoint, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @jose
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @req
RETURN
END
-- Make sure we got a 200 response status code, otherwise it's an error.
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'POST to token endpoint failed.'
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Received response status code ' + @iTmp0
PRINT 'Response body containing error text or JSON:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @jose
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @req
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- The JSON response will look like this:
-- {
-- "access_token": "...",
-- "expires_in": 3600,
-- "token_type": "Bearer",
-- "scope": "AppFramework.Sales"
-- }
-- Get the access token:
DECLARE @accessToken nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @accessToken OUT, 'access_token'
PRINT 'accessToken = ' + @accessToken
-- ------------------------------------------------------
-- Use the access token in a request.
-- We'll just send a GET request to https://test.unieconomy.no/api/init/companies
-- Tell the http object to use the OAuth2 access token in the "Authorization: Bearer ..." header.
EXEC sp_OASetProperty @http, 'AuthToken', @accessToken
DECLARE @sbResponse int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://test.unieconomy.no/api/init/companies', @sbResponse
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @jose
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbResponse
RETURN
END
-- Examine the response status code.
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAMethod @sbResponse, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
PRINT 'response status: ' + @iTmp0
PRINT 'Received error response.'
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @jose
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbResponse
RETURN
END
EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbResponse
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Success.'
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwt
EXEC @hr = sp_OADestroy @jose
EXEC @hr = sp_OADestroy @claims
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @sbResponse
END
GO