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

Question

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.

Answer 1

Thanks @Olaf Helper ,@Seeya Xi-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.

Answer 2

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

Answer 3

Hi @Eugenie Meng ,

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