Use API REST in MS SQL

[Julien REBILLARD]

Hello,

I am trying to retrieve the list of organizations in my instance of JIRA Service Management 4.14.1 (install on a local server), from an SQL stored procedure under SQL Server 2008 R2 (other dedicated server).
The goal would be to be able to retrieve the list of organizations in JIRA to compare with my internal organization and thus be able to recreate the organization in JIRA using the POST / rest / servicedeskapi / organization API.

First of all, I would like to be able to recover my list of organizations.

I am using this SQL code, but I cannot pass the authentication phase :

DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);
DECLARE @Authorization NVARCHAR(200);

--set your post params
SET @authHeader = 'BASIC dXNlcmFkbWluOnBhc3NhZG1pbg=='; -- = useradmin:passadmin
SET @contentType = 'application/json';
--SET @postData = 'KeyValue1=value1&KeyValue2=value2'
SET @postData = ''
SET @url = 'http://URL_JIRA:1234/rest/servicedeskapi/organization'

-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
--set a custom header Authorization is the header key and VALUE is the value in the header
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC sp_OAMethod @token, 'SetRequestHeader', NULL, 'X-ExperimentalApi', 'opt-in';
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;

-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
go

And response :

Status: 401 ()
Response text: <?xml version="1.0" encoding="UTF-8" standalone="yes"?><status><status-code>401</status-code><message>Client must be authenticated to access this resource.</message></status>

If someone could tell me how to validate the authentication, that would be nice because I'm stuck here ...

[Nicholas Mitchell · Accepted solution]

Hi Julien,

I am expecting that you have already solved this, but is the base64 string for your useradmin:passadmin a combination of the useradmin email address and an API Token for the password?

Apologies if you have worked all this out already.

Cheers

Nick

[Julien REBILLARD]

Hi,

Finally I changed technology and I made my application in C #, as I could not do in MS SQL.

[Joshua Sneed Contegix]

Hi ulien,

It's not you, it's the app. Cheers!