Hi Team,

​

I want to access OKTA API using SQL Server sp_OACreate procedure.

Code snippet and response text as below.

DECLARE @authHeader VARCHAR(8000);

DECLARE @contentType VARCHAR(8000);

Declare @Object as Int;

Declare @ResponseText as Varchar(8000);

declare @body varchar(max)

declare @hr int

declare @Apilink varchar(max)='https://companydomain.okta.com/api/v1/users' 

declare @querystring varchar(max) ='search=profile.firstName+eq+"FirstName"+and+profile.lastName+eq+"LastName"+and+profile.email+eq+"FirstName.LastName@companydomain.com"+and+profile.uid+eq+"FirstName.LastName@companydomain.com"';

SET @authHeader = 'XXXXXXXXXXXXXXXX';

SET @contentType = 'application/json; charset=utf-8';

--=profile.email+eq+

Exec @hr=sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;

IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',@Apilink,'false'

IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

EXEC @hr=sp_OAMethod @Object, 'setRequestHeader', NULL, 'Authorization', @authHeader;

IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

EXEC @hr=sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-type', @contentType;

IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

Exec @hr=sp_OAMethod @Object, 'send', null, @querystring

IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

Exec @hr=sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

print @ResponseText

ResponseText:

{"errorCode":"E0000005","errorSummary":"Invalid session","errorLink":"E0000005","errorId":"oaeZnqFBz4uTFqpKi9j8NROwg","errorCauses":[]}

Please help me to resolve that.

Thanks in advance.

Thanks,

Chetan Ramesh Chaudhari