SQL Server

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

-- 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
    -- 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.
    -- 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