top of page
Search

How To Call Microsoft Graph API From SQL Server

Updated: 16 hours ago

Calling APIs directly from SQL Server has been available in Azure SQL DB since 2022, but now it's available in on-prem in SQL Server 2025 preview. In a previous blog, I covered a simple example on calling an API in my previous blog, but I wanted to cover a more complex multi-call example.


First, you need to create an Application in your Microsoft Entra admin center and set up the correct permissions. Here are step-by-step instructions for registering and setting basic permissions.


For my test example, I had to add additional permissions (see highlighted), and I granted consent after adding.

Microsoft Entra Admin - Application Id

Once your application has been registered, you can call the Microsoft Graph API 'without a user'.


Let's walk through the code step by step, and then at the end, I'll provide you with a link to GitHub for the entire script.


First, if you haven't already done so, use sp_configure to enable the external rest endpoint.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE;

Next, we need to populate the @ClientId, @ClientSecret, and @TenantId variables from our application that we just registered.

DECLARE
    @ClientId VARCHAR(36) = '',  -- Replace with your Application ClientId
    @ClientSecret VARCHAR(40) = '', -- Replace with your Application Client Secret
    @TenantId VARCHAR(36) = '', -- Replace with your Tenant Id
    @TokenURL VARCHAR(200),
    @TokenAPIResponse NVARCHAR(MAX),
    @Token NVARCHAR(MAX),
    @APIResponse NVARCHAR(MAX);

Next, we need to set up the URL for the token request with the TenantId.

SET @TokenURL = 'https://login.microsoftonline.com/' + @TenantId + '/oauth2/v2.0/token';

Next, we must create the payload to request the token with our ClientId and ClientSecret.

DECLARE @TokenPayload NVARCHAR(MAX) = N'client_id=' + @ClientId + N'&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=' + @ClientSecret + N'&grant_type=client_credentials';

Now we can make the call to obtain the token, and save the response to the @TokenAPIResponse variable.

EXEC msdb.dbo.sp_invoke_external_rest_endpoint
    @url = @TokenURL,
    @method = N'GET',
    @headers = N'{"Content-Type":"application/x-www-form-urlencoded"}',
    @payload = @TokenPayload,
    @response = @TokenAPIResponse OUTPUT;

We need to extract the bearer token out of the response.

SELECT
    @Token = JSON_VALUE(@TokenAPIResponse, '$.result.access_token');
SELECT
    @Token AS Token;

The token should look something like this.

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkZha2UgVXNlciIsImlhdCI6MTUxNjIzOTAyMn0.dQw4w9WgXcQ9Zp3n5xY8vZ9z8fQw9xY8vZ9z8fQw9xY

Now we can create some variables, create the URL to access Microsoft Graph, and create the header with the content type and the Bearer token we just obtained from the login API.

DECLARE
    @Url NVARCHAR(1000),
    @HeaderValues NVARCHAR(MAX),
    @APIResult NVARCHAR(MAX);

SET @Url = N'https://graph.microsoft.com/v1.0/users';
SET @HeaderValues = N'{"Content-Type": "application/x-www-form-urlencoded", "Authorization": "Bearer ' + @Token + N'" }';

Next, we execute the sp_invoke_external_rest_endpoint stored procedure with the URL and Header.

EXEC msdb.dbo.sp_invoke_external_rest_endpoint
    @url = @Url,
    @method = N'GET',
    @headers = @HeaderValues,
    @response = @APIResponse OUTPUT;

If the call succeeds, you will see a response status of 200. Now we can look at the entire response.

SELECT
    @APIResponse AS APIResponse;
Microsoft Graph API Response Status 200

Then we can extract just the value of our API (our users).

SELECT
    @APIResult = Value
FROM
    OPENJSON(@APIResponse, '$.result')
WHERE
    [Key] = 'value';

SELECT
    @APIResult AS APIResult;
Microsoft Graph Users

I hope this step-by-step guide is helpful. Let me know in the comments below.


Comments


©2021-2025 by Jeff Taylor

bottom of page