Microsoft (Graph)
Interact with the Microsoft Graph API (Outlook, OneDrive, etc.) using SQL Server
- Install the SQLHTTP database on your SQL Server
- Register an application for a Web Platform and set a Redirect URL to http://localhost:53200 (or another port)
- Continue with the above application setup and obtain an Application ID and an Application Secret.
- Create the stored procedures documented below
- Determine which Permissions Scopes will be needed
- Execute the following SQL statement:
|
1 2 3 4 5 6 7 |
EXEC usp_MicrosoftGraph_Auth_Init @Profile = 'enter-profile-name-of-your-choosing', @ClientID = 'enter-application-id', @ClientSecret = 'enter-application-secret', @Scopes = 'enter-permission-scopes', --example: 'Mail.Read Files.ReadWrite' @RedirectURL = 'enter-your-redirecturl' --example: http://localhost:53200 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
CREATE PROCEDURE usp_MicrosoftGraph_Auth_Init( @Profile varchar(100), @ClientID varchar(500), @ClientSecret varchar(50), @Scopes varchar(MAX), @RedirectURL varchar(100)) AS SET NOCOUNT ON DECLARE @UserBrowseToURL varchar(MAX) DECLARE @QueryString nvarchar(MAX) EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientID', @Value = @ClientID EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientSecret', @Value = @ClientSecret EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RedirectURL', @Value = @RedirectURL SET @UserBrowseToURL = 'https://login.microsoftonline.com/common/oauth2/v2.0/authorize' --{tenant} was changed to common SET @Scopes = 'offline_access ' COLLATE SQL_Latin1_General_CP1_CI_AS + @Scopes EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'client_id', @ClientID, 'response_type', 'code', 'scope', @Scopes --parameter that should not be encoded SET @QueryString = @QueryString + '&redirect_uri=' COLLATE SQL_Latin1_General_CP1_CI_AS + @RedirectURL SET @UserBrowseToURL = @UserBrowseToURL + @QueryString DECLARE @URL nvarchar(MAX) DECLARE @Body nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @RedirectURLCode varchar(50) DECLARE @Timeout int DECLARE @TimeoutReached bit = 0 SET @Timeout = 180 --three minutes wait EXEC SQLHTTP.net.AuthListener @UserBrowseToURL = @UserBrowseToURL, @RedirectURL = @RedirectURL, @Timeout = @Timeout, @QueryString = @QueryString OUTPUT, @TimeoutReached = @TimeoutReached OUTPUT IF @TimeoutReached = 1 BEGIN RAISERROR('Timeout reached waiting for browser authentication', 16, 1) RETURN END SET @RedirectURLCode = SQLHTTP.net.Split(@QueryString, 'code=', 2) SET @RedirectURLCode = SQLHTTP.net.Split(@RedirectURLCode, '&', 1) SET @URL = 'https://login.microsoftonline.com/common/oauth2/v2.0/token' EXEC SQLHTTP.net.FormDataBuilder @Body OUTPUT, @Profile, 'code', @RedirectURLCode, 'client_id', @ClientID, 'client_secret', @ClientSecret, 'grant_type', 'authorization_code' --parameter that should not be encoded SET @Body = @Body + '&redirect_uri=' COLLATE SQL_Latin1_General_CP1_CI_AS + @RedirectURL EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'POST', @Body = @Body, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @BearerToken varchar(MAX) DECLARE @RefreshToken varchar(MAX) SELECT @BearerToken = [value] FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'access_token' SELECT @RefreshToken = [value] FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'refresh_token' EXEC SQLHTTP.net.AuthParamSet @Profile = @profile, @Name = 'BearerToken', @value = @BearerToken EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RefreshToken', @Value = @RefreshToken DECLARE @TokenCreatedDateTime nvarchar(100) SET @TokenCreatedDateTime = GetDate() EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'TokenCreatedDateTime', @Value = @TokenCreatedDateTime END GO |
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE usp_MicrosoftGraph_Auth_Header( @Profile varchar(100), @HttpSessionID uniqueidentifier) AS DECLARE @AuthorizationHeaderValue varchar(MAX) SET @AuthorizationHeaderValue = 'Bearer ' COLLATE SQL_Latin1_General_CP1_CI_AS + SQLHTTP.net.AuthParam(@Profile, 'BearerToken') EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'Authorization', @AuthorizationHeaderValue GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
CREATE PROCEDURE usp_MicrosoftGraph_Auth_Refresh( @Profile varchar(100) ) AS --Created based on the following documentation: https://developer.microsoft.com/en-us/graph/docs/authorization/app_authorization --However, the above documentation neglects to mention the "v2.0" portion of the URL, nor is the "resource" parameter needed/working DECLARE @URL nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Body nvarchar(MAX) DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @BearerTokenExpiration smalldatetime SET @URL = 'https://login.microsoftonline.com/common/oauth2/v2.0/token' SET @BearerTokenExpiration = SQLHTTP.net.AuthParam(@Profile, 'BearerTokenExpiration') IF ISNULL(@BearerTokenExpiration, '1/1/2050') > DATEADD(minute, -10, GetDate()) BEGIN EXEC SQLHTTP.net.FormDataBuilder @Body OUTPUT, @Profile, 'grant_type', 'refresh_token', 'client_id', '#ClientID', 'client_secret', '#ClientSecret', 'refresh_token', '#RefreshToken' --, --'resource', 'https://graph.microsoft.com/' --parameter that should not be encoded SET @Body = @Body + '&redirect_uri=' COLLATE SQL_Latin1_General_CP1_CI_AS + SQLHTTP.net.AuthParam(@profile, 'RedirectURL') EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'POST', @Body = @Body, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @BearerToken varchar(MAX) DECLARE @ExpiresIn int SELECT @BearerToken = [value] FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'access_token' SELECT @ExpiresIn = CONVERT(int, [value]) FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'expires_in' EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'BearerToken', @Value = @BearerToken SET @BearerTokenExpiration = DATEADD(second, @ExpiresIn, GetDate()) EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'BearerTokenExpiration', @Value = @BearerTokenExpiration END END GO |
- Calendar – Create
- Calendar Group – Create
- Calendar Groups – Fetch
- Calendar Open Extension – Create
- Calendar View – Fetch
- Calendars – Fetch
- Contact – Create
- Contact – Fetch
- Contact – Update
- Contact Child Folder – Create
- Contact Child Folders – Fetch
- Contact Folder – Create
- Contact Folder – Fetch
- Contact Folder – Update
- Contact Folders – Fetch
- Contacts – Fetch
- Drive – Fetch
- DriveItems – Fetch
- Event – Create
- Event – Fetch
- Event – Update
- Events – Fetch
- Mail – Send
- Mail Child Folder – Create
- Mail Child Folders – Fetch
- Mail Folder – Copy
- Mail Folder – Create
- Mail Folder – Delete
- Mail Folder – Move
- Mail Folder – Update
- Mail Folders – Fetch
- Message – Copy
- Message – Create
- Message – Create Draft
- Message – Create Forward
- Message – Create Reply
- Message – Create ReplyAll
- Message – Delete
- Message – Fetch
- Message – Forward
- Message – Move
- Message – Reply
- Message – ReplyAll
- Message – Send
- Message – Update
- Message File Attachment – Add
- Message File Attachment – Fetch
- Message Item Attachment – Add
- Message Item Attachment – Fetch
- Messages – Fetch
- Notebook – Create
- Override – Create
- Override – Delete
- Override – Update
- Overrides – Fetch
- People – Fetch
- Person – Fetch
- Reminder View – Fetch
- User – Fetch
- Users – Fetch
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.