(SQL Server) OneDrive with OAuth2 Client Credentials

See more OneDrive Examples

Demonstrates how to authenticate with Microsoft OneDrive using OAuth2 client credentials. This example uses a new feature available starting in Chilkat v9.5.0.97, which is to provide the client ID, client secret, and tenant ID to Chilkat, and internally Chilkat will fetch OAuth2 access token via client credentials as necessary. Therefore, it is no longer necessary for the application to explicitly fetch the OAuth2 access token beforehand.

Note: This example requires Chilkat v9.5.0.97 or greater.

For more information, see https://learn.microsoft.com/en-us/onedrive/developer/rest-api/api/drive_list?view=odsp-graph-online

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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- --------------------------------------------------------------------------------------------------------
    -- 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
    -- 
    -- Note: For OneDrive, your registered app needs one or more of these permissions:  Files.Read.All, Files.ReadWrite.All, Sites.Read.All, Sites.ReadWrite.All
    -- --------------------------------------------------------------------------------------------------------

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use your App's actual client ID here:
    DECLARE @success int
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_id', '2871da2c-8176-4b7f-869b-2311aa82e743'

    -- Use your App's actual client secret here:
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_secret', '2hu9Q~r5QuryUcEkNbg1btLtnfU1VUXzhSCG6brH'

    -- Use this specific scope.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'scope', 'https://graph.microsoft.com/.default'

    -- Replace the tenant ID part of the URL with your tenant ID.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'token_endpoint', 'https://login.microsoftonline.com/114d7ed6-71bf-4dbe-a866-748364121bf2/oauth2/v2.0/token'

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    -- List drives.
    -- You can specify a {siteId}, {groupId}, or {userId} in the URL
    -- to access document libraries for a group, a site, or a specific user.
    -- Use your specific Id instead of what we're using here:
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'userId', '4fe732c3-322e-4a6b-b729-2fd1eb5c6104'
    DECLARE @resp nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @resp OUT, 'https://graph.microsoft.com/v1.0/users/{$userId}/drives'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- The response should be JSON.
    DECLARE @jsonResp int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jsonResp OUT

    EXEC sp_OASetProperty @jsonResp, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonResp, 'Load', @success OUT, @resp

    -- A successful response should return a status code of 200.
    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAMethod @jsonResp, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
        PRINT 'Response status = ' + @iTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonResp
        RETURN
      END

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

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jsonResp


END
GO