You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.
View groupJoin the community to find out what other Atlassian users are discussing, debating and creating.
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 ...
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Connect with like-minded Atlassian users at free events near you!
Find an eventConnect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.
Host an eventYou're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.