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.  

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.

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"}}}
 

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.
 

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.

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

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? 

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.
 

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

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?

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.

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.

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.

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