(SQL Server) SharePoint OAuth2 Client Credentials Authentication

See more SharePoint Online Examples

Demonstrates how to authenticate with OAuth 2.0 using the client credentials grant type. This is for SharePoint Online which is the cloud-based service provided by Microsoft as part of Office 365. (Users access SharePoint Online through a web browser, and the service is hosted on Microsoft's servers.)

Note: This example uses the new "Integrated OAuth2 Client Credentials" feature introduced in Chilkat v9.5.0.96.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    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

    -- Provide the information needed for Chilkat to automatically fetch the OAuth2.0 
    -- access token as needed.
    -- This is the new "Integrated OAuth2 Client Credentials" feature introduced in Chilkat v9.5.0.96.  
    DECLARE @jsonOAuthCC int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOAuthCC OUT

    -- --------------------------------------------------------------------------------------------------------
    -- You'll need to have registered an app in Azure beforehand.
    -- See How to Create an Azure App Registration for OAuth 2.0 Client Credentials
    -- --------------------------------------------------------------------------------------------------------

    -- Use the values from your App Registration in Azure.
    EXEC sp_OAMethod @jsonOAuthCC, 'UpdateString', @success OUT, 'client_id', 'e1cd2318-xxxx-4916-bfd6-xxxxxxxxxxxx'
    EXEC sp_OAMethod @jsonOAuthCC, 'UpdateString', @success OUT, 'client_secret', 'K.d8Q~RoJxxxxvCj_u_xxxxxxxxxxxxxxxxxxxxx'

    -- You'll always use this scope for client credentials, because the allowed actions
    -- are determined by the App Permissions defined when you registered the app in Azure.
    EXEC sp_OAMethod @jsonOAuthCC, 'UpdateString', @success OUT, 'scope', 'https://graph.microsoft.com/.default'

    -- The token endpoint includes your tenant ID.  Use your tenant ID (not the tenant ID shown here).
    EXEC sp_OAMethod @jsonOAuthCC, 'UpdateString', @success OUT, 'token_endpoint', 'https://login.microsoftonline.com/112d7ed6-71bf-4eba-a866-738364321bfc/oauth2/v2.0/token'

    -- Provide the OAuth2 information. Chilkat will use this information to automatically fetch the OAuth2 access token as needed,
    EXEC sp_OAMethod @jsonOAuthCC, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    -- Get information about all SharePoint sites.
    DECLARE @sbJson int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbJson OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://graph.microsoft.com/v1.0/sites/getAllSites', @sbJson
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonOAuthCC
        EXEC @hr = sp_OADestroy @sbJson
        RETURN
      END

    DECLARE @statusCode int
    EXEC sp_OAGetProperty @http, 'LastStatus', @statusCode OUT


    PRINT 'Response status code = ' + @statusCode

    IF @statusCode <> 200
      BEGIN
        EXEC sp_OAMethod @sbJson, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonOAuthCC
        EXEC @hr = sp_OADestroy @sbJson
        RETURN
      END

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

    EXEC sp_OAMethod @jsonResp, 'LoadSb', @success OUT, @sbJson
    EXEC sp_OASetProperty @jsonResp, 'EmitCompact', 0

    EXEC sp_OAMethod @jsonResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    PRINT 'Success.'

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jsonOAuthCC
    EXEC @hr = sp_OADestroy @sbJson
    EXEC @hr = sp_OADestroy @jsonResp


END
GO