SQL Server

Azure AD Service-to-service access token request

See more Azure OAuth2 Examples

Send an Azure AD service-to-service token request to get an access token using a shared secret. 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 example 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

    -- To see the exact HTTP request sent and the response, set the SessionLogFilename property:
    EXEC sp_OASetProperty @http, 'SessionLogFilename', 'qa_output/chilkatHttpLog.txt'

    DECLARE @req int
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT

    -- Set the following request params:
    -- grant_type 	required 	Specifies the requested grant type. In a Client Credentials Grant flow, the value must be client_credentials.
    -- 
    -- client_id 	required 	Specifies the Azure AD client id of the calling web service. 
    --     To find the calling application's client ID, in the Azure portal, click Azure Active Directory, click App registrations, click the application. 
    --     The client_id is the Application ID
    -- 
    -- client_secret 	required 	Enter a key registered for the calling web service or daemon application in Azure AD. 
    --     To create a key, in the Azure portal, click Azure Active Directory, click App registrations, click the application, click Settings, click Keys, and add a Key.
    --     URL-encode this secret when providing it.
    -- 
    -- resource 	required 	Enter the App ID URI of the receiving web service. To find the App ID URI, in the Azure portal, click Azure Active Directory, 
    --     click App registrations, click the service application, and then click Settings and Properties.

    EXEC sp_OAMethod @req, 'AddParam', NULL, 'grant_type', 'client_credentials'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_id', 'MY_CLIENT_ID'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'client_secret', 'MY_CLIENT_SECRET'
    EXEC sp_OAMethod @req, 'AddParam', NULL, 'resource', 'https://service.contoso.com/'

    EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
    EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'

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

    EXEC sp_OAMethod @http, 'HttpReq', @success OUT, 'https://login.microsoftonline.com/<tenant_id>/oauth2/token', @req, @resp
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @resp
        RETURN
      END

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode

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

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0

    PRINT 'Response JSON:'
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    IF @respStatusCode >= 400
      BEGIN

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

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Sample response:

    -- {
    --   "token_type": "Bearer",
    --   "expires_in": "3599",
    --   "ext_expires_in": "3599",
    --   "expires_on": "1570059833",
    --   "not_before": "1570055933",
    --   "resource": "https://adminchilkatsoft.onmicrosoft.com/eb1b8ced-76b7-4845-aec5-d3e91776e345",
    --   "access_token": "eyJ0eXAiO ... pmgw"
    -- }

    -- To get the items from the JSON....
    DECLARE @token_type nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @token_type OUT, 'token_type'
    DECLARE @expires_in nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @expires_in OUT, 'expires_in'
    DECLARE @ext_expires_in nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @ext_expires_in OUT, 'ext_expires_in'
    DECLARE @expires_on nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @expires_on OUT, 'expires_on'
    DECLARE @not_before nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @not_before OUT, 'not_before'
    DECLARE @resource nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @resource OUT, 'resource'
    DECLARE @access_token nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @access_token OUT, 'access_token'

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


END
GO