SQL Server
SQL Server
OAuth2 Token using IdentityServer4 with Client Credentials
See more OAuth2 Examples
Demonstrates how to get an OAuth2 access token using the client credential flow with IdentityServer4.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 assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
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 @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://localhost:5000/.well-known/openid-configuration', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
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 @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
-- 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 @resp
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
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, '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_OASetProperty @req, 'HttpVerb', 'POST'
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 @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 @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
-- 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 @resp
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @req
END
GO