(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
    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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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.'

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


END
GO