question

EugenieMeng-7024 avatar image
0 Votes"
EugenieMeng-7024 asked EugenieMeng-7024 commented

Consume Jira Rest API using SSIS, return 403 error from SQL server agent job

I have an SSIS package to consume REST API GET request to extract Jira data into our SQL server, with a 3rd party tool called Zappysys.

I used basic authentication (username/password) in the HTTP connection manager for Jira authentication. The SSIS package works fine on my local machine using SSDT. But when the package is deployed to the SSIS server, and ran from SQL server agent job, the job fails. I keep getting a 403 Forbidden error message, please see attached error message.

I am very new to SQL server as well as HTTP requests. Much appreciated if anyone has any idea about what the issue is. More than happy to provide more info. 118070-403-error-1st-page.png118058-403-error-2nd-page.png


sql-server-generalsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EugenieMeng-7024 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
EugenieMeng-7024 avatar image
0 Votes"
EugenieMeng-7024 answered EugenieMeng-7024 commented

Thanks @OlafHelper-2800 ,@SeeyaXi-msft

I've spoken to our Jira owner. I think the cause of the problem is that, we are using Okta 2 factor authentication to authenticate all the company applications. To solve my issue, either we need to create an API token, or figure out a way to bypass Okta. This is not an SQL issue but I will still give an update once we figure out how to bypass Okta.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Either a service account (works for me) or an API token is required.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @EugenieMeng-7024,

The error indicates that you don't have authorization to perform the request.

Here is about how to Manage project permissions.
If you do not have enough permission to operate it, please communicate with your DBA.

Best regards,
Seeya

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 commented

403 Forbidden error message

Http error 403 = Access denied: https://en.wikipedia.org/wiki/HTTP_403
The SQL Server-Agent service account don't have the permission to access the Jira API.
Create a Agent Proxy with your credentials, as you have access and use that proxy to execute the SSIS job: Create a SQL Server Agent Proxy


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @OlafHelper-2800 ,

Many thanks for your reply.

Currently the SSIS job is ran under a proxy account. In this case, do I need to ask our DBA to grant any specific permission on this proxy account in order to access Jira API?

0 Votes 0 ·

Not on the SQL proxy for Jira access, the other way round: Grant access in Jira for the SQL proxy.

1 Vote 1 ·