Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Oauth 2 with APICaller_POST_Encoded #42

Closed
sramsay335i opened this issue Dec 21, 2020 · 2 comments
Closed

Oauth 2 with APICaller_POST_Encoded #42

sramsay335i opened this issue Dec 21, 2020 · 2 comments

Comments

@sramsay335i
Copy link

sramsay335i commented Dec 21, 2020

Hi Geral,

When I use the your SQL example, I get the following json string back:{"access_token":"5f36e31256c2db5ec3d68f6b5206aafa23c6ab6f","expires_in":3600,"token_type":"Bearer","scope":"api"}
I was expecting to get back the json result of the API I was calling in the @Audience variable. Here's the SQL example I am using:

DECLARE @wurl VARCHAR(MAX) = 'https://westshorelms.docebosaas.com/oauth2/token'
DECLARE @Header VARCHAR(MAX) = '[]'
DECLARE @grant_type VARCHAR(20) = 'client_credentials'
DECLARE @client_id VARCHAR(15) = 'xxx'
DECLARE @client_secret VARCHAR(40) = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
DECLARE @Audience VARCHAR(100) = 'https://westshorelms.docebosaas.com/manage/v1/user'
DECLARE @Body VARCHAR(MAX) = CONCAT('grant_type=',@grant_type,'&client_id=',@client_id,'&client_secret=',@client_secret,'&audience=',@Audience)
DECLARE @results AS TABLE (Context VARCHAR(MAX) NOT NULL)
INSERT INTO @results EXEC dbo.APICaller_POST_Encoded @url = @wurl, @headers = @Header, @JsonBody = @Body
SELECT Context FROM @results

Do you see anything odd with the SQL? The bearer token I get back in the string doesn't work when I try calling the @Audience URL in Postman using Bearer Token Authorization.

Sean

@geral2
Copy link
Owner

geral2 commented Dec 22, 2020

Hi @sramsay335i,
Could you please add the code below after your select context from @Result;

Let me know if it works. Keep in mind you need to create the procedure dbo.APICaller_GETAuth.


--Result: column per value.
DROP TABLE IF EXISTS #TokenData;

SELECT 
		 [scope]		
		,[expires_in]	
		,[token_type]	
		,[access_token]
INTO #TokenData
 FROM (
			SELECT Context 
			  from @Results
		)tb
	OUTER APPLY OPENJSON  (context)  
  WITH
    ( [access_token]	VARCHAR(MAX)  '$.access_token'
	, [scope]			VARCHAR(1000) '$.scope'
	, [expires_in]		VARCHAR(100)  '$.expires_in'
	, [token_type]		VARCHAR(100)  '$.token_type'
    );

	----Result: Row per value 

DECLARE @Token	   AS VARCHAR(MAX)	

SELECT @Token   = CONCAT(token_type,' ',access_token)
 FROM #TokenData 

DELETE FROM @Results;

INSERT INTO @Results
EXEC  [dbo].[APICaller_GETAuth]
 @URL		=  @audience
,@Token		= @Token

SELECT Context 	from @Results

@geral2
Copy link
Owner

geral2 commented Feb 16, 2021

Hey, @sramsay335i Did it work?

@geral2 geral2 closed this as completed Feb 22, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants