(SQL Server) Uni Economy API Client Credentials Flow

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.)

For more information, see https://developer.unieconomy.no/wiki/introduction/getting-started/server-application

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.

    -- Step 1 ------------------------------------------------------------------------------------------
    -- First create a client token...

    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

    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.
    DECLARE @success int
    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 sp_OAMethod @cert, 'ExportPrivateKey', @privKey OUT
    EXEC sp_OAGetProperty @cert, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

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

    -- Build the JOSE header
    DECLARE @jose int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @claims OUT

    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_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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- Fetch the discovery document...
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'QuickRequest', @resp OUT, 'GET', 'https://test-login.unieconomy.no/.well-known/openid-configuration'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        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
        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 @resp

        EXEC @hr = sp_OADestroy @cert
        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
        RETURN
      END

    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

    -- 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 @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 @json
        RETURN
      END

    -- ------------------------------------------------------
    -- Request the access token using our Client ID and JWT

    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_OASetProperty @req, 'HttpVerb', 'POST'
    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, 'PostUrlEncoded', @resp OUT, @tokenEndpoint, @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 @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 @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 @resp

        EXEC @hr = sp_OADestroy @cert
        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 @json
        EXEC @hr = sp_OADestroy @req
        RETURN
      END

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

    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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 @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 @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 @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 @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 @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 @json
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @sbResponse


END
GO