natanstr | 2023-01-24 02:13:45 UTC | #1
Hi Guys! I'm having a hard time trying to set a connection to Genesys API using MSSQL Server. Below is the code:
DECLARE @Object int DECLARE @ret int DECLARE @ResponseText varchar(max) DECLARE @json table(json_Table nvarchar(max)) DECLARE @body varchar(max) DECLARE @Apilink varchar(max) DECLARE @AuthHeader nvarchar(max) DECLARE @contentType nvarchar(64) DECLARE @clientID nvarchar(100) DECLARE @clientSecret nvarchar(100) DECLARE @stringText varchar(500) DECLARE @encode varchar(500)
set @clientID='XXXXX' set @clientSecret='XXXXX' set @stringText = @clientID+':'+@clientSecret
set @encode = (SELECT CAST(@stringText as varbinary(max)) FOR XML PATH(''), BINARY BASE64) set @authHeader = 'BASIC '+@encode --set @contentType = 'application/x-www-form-urlencoded' set @contentType = 'application/json'
set @Apilink = 'https://api.mypurecloud.com/api/v2/workforcemanagement/businessunits/XXXX'
--Exec @ret = spOACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT; Exec @ret = spOACreate 'MSXML2.XMLHTTP', @Object OUT; Exec @ret = spOAMethod @Object,'open', null, 'get',@Apilink,'false' Exec spOAMethod @Object,'setRequestHeader',null,'Authentication',@authHeader Exec spOAMethod @Object,'setRequestHeader',null,'Content-Type',@contentType Exec spOAMethod @Object,'send'--,null, @body
--Exec sp_OAMethod @Object,'responseText',@ResponseText output
Insert into @json(jsonTable) Exec spOAGetProperty @Object, 'responseText'
select * from @json
I tried to use MSXML2.ServerXMLHTTP.6.0 parameter for the object but I keep receiveing the error: "No authentication bearer token specified in authorization header." But I do not intend to use the auth Bearer method, that's why I used the basic encoding (base64) code.
When I use MSXML2.XMLHTTP parameter I get NULL value for the @json table.
Does anyone know how I fix it? Is there another way of retrieving data and exporting it to a SQL Server table?
Thanks in advance!
Jerome.Saint-Marc | 2023-02-03 17:29:19 UTC | #2
Hello,
I don't have experience with running such query for MSSQL Server. But what I can say is that you are not managing/handling authorization properly.
The Platform API expects a Bearer token (Authorization header). Basic authentication is not supported with Platform API access.
So you'll need to get a token first - in your case, using an OAuth Client Credentials flow (no user involved). That part indeed leverages basic authentication. You then need to use the retrieved token in your Platform API requests (sending it as Bearer token in Authorization header).
See here for info on OAuth Client Credentials Grant flow.
Regards,
system | 2023-02-24 08:09:32 UTC | #3
This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.
This post was migrated from the old Developer Forum.
ref: 18102