Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Use API REST in MS SQL

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

 

2 answers

1 accepted

1 vote
Answer accepted

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

Hi ulien,

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

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you