SQL Server Requires Chilkat v11.0.0+

Microsoft Teams OAuth2 Client Credentials Authentication

See more Microsoft Teams Examples

Demonstrates how to authenticate with OAuth 2.0 using the client credentials grant type. SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    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.
    --  This is the new "Integrated OAuth2 Client Credentials" feature introduced in Chilkat v9.5.0.96.  
    DECLARE @jsonOAuthCC int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOAuthCC OUT

    --  --------------------------------------------------------------------------------------------------------
    --  You'll need to have registered an app in Azure beforehand.
    --  
    --  Note: Creating a Team requires the following Application permissions:
    --  Team.Create, Teamwork.Migrate.All, Group.ReadWrite.All**, Directory.ReadWrite.All**
    --  
    --  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

    --  ------------------------------------------------------------------------------
    --  To create a Microsoft Team, we want to send an HTTP request like the following:
    --  
    --  Note: The following is the minimal JSON for a request using Applicaton permissions (i.e. the access token retrieved using the client credentials grant).
    --  
    --  POST https://graph.microsoft.com/v1.0/teams
    --  Content-Type: application/json
    --  
    --  {
    --     "template@odata.bind":"https://graph.microsoft.com/v1.0/teamsTemplates('standard')",
    --     "displayName":"My Sample Team",
    --     "description":"My Sample Team�s Description",
    --     "members":[
    --        {
    --           "@odata.type":"#microsoft.graph.aadUserConversationMember",
    --           "roles":[
    --              "owner"
    --           ],
    --           "user@odata.bind":"https://graph.microsoft.com/v1.0/users('0040b377-61d8-43db-94f5-81374122dc7e')"
    --        }
    --     ]
    --  }

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, '"template@odata.bind"', 'https://graph.microsoft.com/v1.0/teamsTemplates(''standard'')'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'displayName', 'My Sample Team'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'description', 'My Sample Team�s Description'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0]."@odata.type"', '#microsoft.graph.aadUserConversationMember'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0].roles[0]', 'owner'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'members[0]."user@odata.bind"', 'https://graph.microsoft.com/v1.0/users(''4ee732c3-322e-4a6b-b729-2fd1eb5c6004'')'

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-type', 'application/json'

    DECLARE @resp int
    EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT

    EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://graph.microsoft.com/v1.0/teams', @json, 'application/json', @resp
    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 @json
        EXEC @hr = sp_OADestroy @resp
        RETURN
      END

    --  A successful response is indicated by a 202 response status code and an empty response body.

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response Status Code: ' + @iTmp0

    PRINT 'Response Body:'
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jsonOAuthCC
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @resp


END
GO