SQL Server

SharePoint OAuth2 Client Credentials Authentication

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

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- This requires 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

    -- Provide the information needed for Chilkat to automatically fetch the OAuth2.0 
    -- access token as needed.
    DECLARE @jsonOAuthCC int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOAuthCC OUT

    -- --------------------------------------------------------------------------------------------------------
    -- You'll need to have registered an app in Azure beforehand.
    -- See How to Create SharePoint 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', 'CLIENT_ID'
    EXEC sp_OAMethod @jsonOAuthCC, 'UpdateString', @success OUT, 'client_secret', 'SECRET_VALUE'

    -- 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. 
    EXEC sp_OAMethod @jsonOAuthCC, 'UpdateString', @success OUT, 'token_endpoint', 'https://login.microsoftonline.com/TENANT_ID/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.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.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