(SQL Server) OAuth2 Token using IdentityServer4 with Client Credentials

Demonstrates how to get an OAuth2 access token using the client credential flow with IdentityServer4.

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

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

    -- The first step is to fetch your IdentityServer4's discovery document
    -- (OpenID Connect defines a discovery mechanism, called OpenID Connect Discovery, where an OpenID server publishes its metadata at a well-known URL, 
    -- typically https://server.com/.well-known/openid-configuration

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'QuickRequest', @resp OUT, 'GET', 'https://localhost:5000/.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 @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 @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

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

    -- 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://localhost:5000",
    --   "jwks_uri": "https://localhost:5000/.well-known/openid-configuration/jwks",
    --   "authorization_endpoint": "https://localhost:5000/connect/authorize",
    --   "token_endpoint": "https://localhost:5000/connect/token",
    --   "userinfo_endpoint": "https://localhost:5000/connect/userinfo",
    --   "end_session_endpoint": "https://localhost:5000/connect/endsession",
    --   "check_session_iframe": "https://localhost:5000/connect/checksession",
    --   "revocation_endpoint": "https://localhost:5000/connect/revocation",
    --   "introspection_endpoint": "https://localhost:5000/connect/introspect",
    --   "frontchannel_logout_supported": true,
    --   "frontchannel_logout_session_supported": true,
    --   "backchannel_logout_supported": true,
    --   "backchannel_logout_session_supported": true,
    --   "scopes_supported": [
    --     "openid",
    --     "profile",
    --     "email",
    --     "MyCompany.profile",
    --     "MyCompany.Identity.WebApi",
    --     "MyCompany.TriHub.WebApi",
    --     "offline_access"
    --   ],
    --   "claims_supported": [
    --     "sub",
    --     "updated_at",
    --     "locale",
    --     "zoneinfo",
    --     "birthdate",
    --     "gender",
    --     "website",
    --     "profile",
    --     "preferred_username",
    --     "nickname",
    --     "middle_name",
    --     "given_name",
    --     "family_name",
    --     "name",
    --     "picture",
    --     "email_verified",
    --     "email",
    --     "userId",
    --     "groups",
    --     "fullname"
    --   ],
    --   "grant_types_supported": [
    --     "authorization_code",
    --     "client_credentials",
    --     "refresh_token",
    --     "implicit",
    --     "password"
    --   ],
    --   "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"
    --   ],
    --   "subject_types_supported": [
    --     "public"
    --   ],
    --   "id_token_signing_alg_values_supported": [
    --     "RS256"
    --   ],
    --   "code_challenge_methods_supported": [
    --     "plain",
    --     "S256"
    --   ]
    -- }
    -- 

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

    -- Request the access token using our Client ID and Client Secret.
    -- We're going to duplicate this CURL statement:

    -- curl --request POST \
    --   --url '<tokenEndpoint>' \
    --   --header 'content-type: application/x-www-form-urlencoded' \
    --   --data 'grant_type=client_credentials&client_id=CLIENT_ID&client_secret=CLIENT_SECRET'

    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, 'grant_type', 'client_credentials'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_id', 'CLIENT_ID'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_secret', 'CLIENT_SECRET'

    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 @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 @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

    -- Our JSON response should contain this:
    -- {
    --   "access_token":"eyJz93a...k4laUWw",
    --   "token_type":"Bearer",
    --   "expires_in":86400
    -- }

    -- Get the access token:
    DECLARE @accessToken nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @accessToken OUT, 'access_token'

    -- The access token is what gets added to "Authorization: Bearer <access_token>"
    -- for the subsequent REST API calls..

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @req


END
GO