Solved

Microsoft Graph issue


Userlevel 1

Hello everyone,
Because the new Exchange connector that is scheduled for the month November of this year will not give adequate possibilities for our client, we're trying our luck with Microsoft Graph. We created a call and made it fit our needs using Postman.
We configured and use all the right credentials (client ID, tenant ID and client secret) for token generation. Actually, we created a call for getting data, and one for patching data. The call types are, of course, get and patch respectively. All calls are successful, and perform as expected, using Postman.
Next, we went to our Software Factory, to make Thinkwise perform the same calls. Using a process flow, we make the proper call, the same as we did using Postman.
We get a correct token in Thinkwise. When we copy this token from the debugger, and check it with jwt.ms, we get a valid token. In fact, when we use this token in Postman to make calls, it works perfectly. So, token generation works properly.
However, the calls that work in Postman, do not work in our Thinkwise solution. The error we get is a 401; Authorization_RequestDenied.
We checked the call type, the headers, the URI the call is made against and the body. Everything is as it should be. Yet, no worky.
We had a simple call to get users earlier in our Thinkwise solution, which worked. We messed with it, and it started to give a wrong_header error, telling us it expected a Json content-type attribute in the header. We fixed that. But ever since, any and every call we do from our Thinkwise solution results in the same Authorization_RequestDenied error. Again, the token that gets issued and which is used in the call is completely valid.
We tried completely closing and restarting the Thinkwise solution, clearing browser cache. And we checked, and checked again, all API permissions within the Azure environment that we're working from. As Postman seems to work flawlessly, and as we've set up the call identically in the Thinkwise solution, we're trying to find out what we're missing.
Has anyone successfully debugged an issue similar to this?
Best regards,
- Alex.

icon

Best answer by Dick van den Brink 27 July 2022, 17:07

View original

15 replies

Userlevel 2
Badge

Hi Alex,

can you try to log the response to maybe a logging table?
I believe the api should also return a message with extra information as documented here:

https://docs.microsoft.com/en-us/graph/errors

 

So I am curious what is in the "message” property of the JSON.

 

Edit: Can you also show how the headers are configured in the HTTP Connector? Maybe the token is not filled correctly.  

Userlevel 3
Badge +2

Hi Alex,

Did you check and save the cookie if any? to be reused with the other requests of the user ?

We faced similar issue but was due to cookie not saved on our side to reuse with the authorization token.

 

Userlevel 2
Badge

Another thing I just remembered, can you double check that access_token is correct? I remember another customer having issues because the process flow variable for the access_token was not big enough causing the access_token to be cut off.

Userlevel 1

Another thing I just remembered, can you double check that access_token is correct? I remember another customer having issues because the process flow variable for the access_token was not big enough causing the access_token to be cut off.


Hi Dick,

Thanks for the reply! We changed the domain of the variable that contains the token from VARCHAR(MAX) to NVARCHAR(MAX), to no avail.
The weird thing is, that the behavior looks as if the token doesn't fit, but factually something else is going wrong. We build a header containing the token. This header we can grab from the process monitor. The token in that header can be used for calls using Postman, no problem. Also, the entire header fits in the respective column of the database that would store the call, and the results of this call. However, once we've build the header, and parse it to the next process step, the header appears to be blank, therefore resulting in a 401 response.
A domain of type NVARCHAR(MAX) is the same for Thinkwise and the database, right?

The actual response message is as follows:

{"error":{"code":"InvalidAuthenticationToken","message":"Access token is empty.","innerError":{"date":"2022-07-25T12:58:13","request-id":"be1e344c-2095-41c1-9ae9-283fe3f0af67","client-request-id":"be1e344c-2095-41c1-9ae9-283fe3f0af67"}}}
 



 

Userlevel 1

Hi Alex,

Did you check and save the cookie if any? to be reused with the other requests of the user ?

We faced similar issue but was due to cookie not saved on our side to reuse with the authorization token.

 


Hi Mperrott,

We don’t get a cookie back from the Graph API as far as I can see. Should there always be a cookie?

Best regards,
- Alex.
 

Userlevel 2
Badge

Hi Alex,

The error is showing that the access token is empty.

Are you sure you added it to the HTTP Connector headers? I used it in my test like below:

[ { "Key": "Accept", "Value": "application/json" }, { "Key": "Authorization", "Value": "Bearer {var_access_token}" } ]

 

Here I have a process variable named var_access_token and I loaded the access_token from the database. Note: I see you already have a token variable with json values - there should be an access_token in it.

I think it is working in postman because in that case postman might be injecting the access_token to the request because oauth was configured.

With the GraphAPI you don't have to check for cookies - only the access_token is required.

Userlevel 1

We don't use the stored and reused access token from Postman. We copy and paste the token from the process monitor to test the token, and also we run that token through jwt.ms. The token is valid. Yet, it doesn't want to be injected into our header, or somehow the header doesn't fit in the variable that should hold it (as nvarchar(max)). The database holds the data when inserted manually, so I guess the variable will hold it just the same. Stil, only that header doesn't seem to get set. Or something. Everything else works fine. The token gets retreived correctly as well. It just doesn't want to be used for the subsequent call(s).

Userlevel 2
Badge

Can you show how the Http Connector is configured. I am interested in the Headers part (like below):

I see in your screenshot a variable named `headers` that looks empty. Is this the variable you are using in the HttpConnector - because that might be the problem? 

 

Userlevel 1

Hi Dick,

We're doing almost exactly what you're doing. We have a first call to get the token. This works correct. When we have a token, we make the actual call. That second Http Connector is configured as follows:
 

The variables are filled as described in the image below. This is a bit of a mess, we’re trying various things here. What happens, though, is: the @header_containing_token is set, but it remains NULL, because the @token that gets inserted in it, is NULL. The token itself is not NULL. We have a variable called @token_container, that gets filled with the reply to the token request, which is used to retrieve the token itself from.
 


Below is an image showing the monitor. The domain for both @token and @token_container are nvarchar(max), just as described here.
 


 

Userlevel 2
Badge

From the screenshots above it looks like: "set @token = json_value(@token_container, '$.access_token')" is not returning a value.
That is why you see the variable header containing the value: "Bearer Beer", because of your isnull(@token, 'Beer').
The first step is figuring out why that is not working. I can not see the full value of token_container so I can not comment on that. 

You are using the @header_containing_token variable, but that one is null because it is concatenating a null value to the string. @token is already null - and the code is doing again json_value(@token, '$.access_token'), but that already happened at line 1. So probably you want to just use @token there (after figuring out why that one is empty).

Userlevel 1

Yes, that's exactly the situation we tried to explain 😁 You're explaining what I described, and what we did to try to pinpoint the issue.

We're out of options than?

Userlevel 2
Badge

I think it is best to copy the value of token_container and try some things from the SQL Server Management Studio. To check if you can get the access_token correctly with the json_value function.

So if the json_value step is working from the management studio - it must also work from the gui.

Can you double check that the process_variables in the SF have “process output available” enabled? If not, it also requires you to regenerate the process procedure and execute the new code on the database.

If not, it might be wise to schedule a call for a quick look.

Userlevel 1

Hi Dick,

You were right. Porting the problematic behavior to the database sheds more light on the issue. Look at what I found. As you said, I put everything that matters into a query window.

As you can see, all parameters are NVARCHARS, maxed out. And, the Json value is correctly grabbed by the JSON_VALUE method. So, of course, one would expect the same correct result for the access_token attribute. So, this is what happens.

Somehow, this doesn't work. Maybe the value surpasses a length, or contains an illegal character of some sort. Weirdness! I'll keep posting whatever I find out regarding this. The server we use is an Azure SQL server by the way. Perhaps some settings need checking.

Best regards,
- Alex.

Userlevel 2
Badge

In that case, this Microsoft Article might help:

https://docs.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server?view=sql-server-ver16#return-long-text-value-with-openjson-instead-of-json_value

 

Question. I have description key in JSON text that contains long text. JSON_VALUE(@json, '$.description') returns NULL instead of a value.

Answer. JSON_VALUE is designed to return small scalar values. Generally the function returns NULL instead of an overflow error. If you want to return longer values, use OPENJSON, which supports NVARCHAR(MAX) values, as shown in the following example.

SELECT myText FROM OPENJSON(@json) WITH (myText NVARCHAR(MAX) '$.description')  

 

I am a bit curious about the size of the token in this case - I used a very long text in Azure SQL and it worked perfectly for me.

Userlevel 1

Thanks Dick! You're a lifesaver 😀 That works like a charm!

Reply