July 25, 2023 at 2:20 pm
I am trying to figure out a way to get oauth2.0 bearer token from the rest API.
So from Postman I can do a POST method for "https://helloworld.org:443/xyz/oauth2/token"
And in the body, I choose x-www-form-urlencoded and put grant_type as "password" and put the values for my username and password. This gives me a token which is a bearer token and I use this to get data out of the API endpoints.
I want to do this in SSIS and I cannot use the web service task because this API is a REST API and not SOAP so it does not have any services that returns the token back. I have to do it in a script task. When I run the script task I get this error "Error occurred while fetching the bearer token: Not Found" Its a 404 error.
My questions are:
Do I have to include the port number in the apiEndpoint?
In this line, is it adding "/token" to the call?
var response = client.PostAsync("/token", content).Result
Thanks for any help.
I tried the above code and did a lot of googling and research.
Below is my script task code:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
namespace ST_9e6c1ec9747f4bca8db831645eddf61d
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// Variables to store the credentials and API endpoint
string username = "sadao002";
string password = "dsf83837%%";
string apiEndpoint = "https://helloworld.org:443/xyz/oauth2";
// Create an HttpClient instance
using (HttpClient client = new HttpClient())
{
// Set the base address of the API
client.BaseAddress = new Uri(apiEndpoint);
// Define the content (username, password, and grant_type) to be sent as form data
var formData = new System.Collections.Generic.Dictionary<string, string>
{
{ "username", username },
{ "password", password },
{ "grant_type", "password" }
};
var content = new FormUrlEncodedContent(formData);
// Set the content type header
content.Headers.ContentType = new MediaTypeHeaderValue("application/x-www-form-urlencoded");
// Send the request and receive the response
var response = client.PostAsync("/token", content).Result;
if (response.IsSuccessStatusCode)
{
// Read the response content as string
string responseBody = response.Content.ReadAsStringAsync().Result;
// Assuming the response contains a JSON object with an "access_token" field for the bearer token
// You can parse the JSON manually, or using System.Text.Json.JsonSerializer if you're using .NET Core 3.0 or later.
// For simplicity, we'll just look for "access_token":"value" pattern.
int tokenIndex = responseBody.IndexOf("\"access_token\":\"");
if (tokenIndex >= 0)
{
tokenIndex += "\"access_token\":\"".Length;
int tokenEndIndex = responseBody.IndexOf("\"", tokenIndex);
string bearerToken = responseBody.Substring(tokenIndex, tokenEndIndex - tokenIndex);
// Now you have the bearer token, you can use it for your subsequent requests.
// For example, you can store it in a package variable or use it in the same script task.
MessageBox.Show("Bearer Token: " + bearerToken, "Token Fetched Successfully");
}
else
{
MessageBox.Show("Unable to fetch bearer token. Response does not contain a valid token.", "Error");
}
}
else
{
MessageBox.Show("Error occurred while fetching the bearer token: " + response.ReasonPhrase, "Error");
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
public enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
}
}
}
July 25, 2023 at 4:14 pm
In the PostAsync method you're appending "/token" to the end of the HttpClient's base url which you assigned as:
string apiEndpoint = "https://helloworld.org:443/xyz/oauth2/token";
That would make the combined url ending in "/token/token", no?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 25, 2023 at 4:30 pm
Sorry about that, apiEndpoint corrected, it was a typo. I am getting the same error still. 404 not found.
Is the port number required there? Thanks.
July 25, 2023 at 4:52 pm
Sorry about that, apiEndpoint corrected, it was a typo. I am getting the same error still. 404 not found.
Is the port number required there? Thanks.
Try it without the "/" in "/token" in the PostAsync method
var response = client.PostAsync("token", content).Result;The default port for HTTPS is 443 and HttpClient will handle that automatically. If the server you're targeting follows standard conventions it shouldn't be necessary
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 25, 2023 at 5:26 pm
Tried it without the / and just "token". Still got the same error. Not Found.
July 25, 2023 at 8:03 pm
In Postman you could open the POST method and then click on the '</>' code symbol. That should generate the cURL code of the request. When I ask ChatGPT-4 to generate a cURL request based on your code and then I submit it (I used Bash) the result is a 404. Are you sure you have the correct url? Also, it looks like the certificate of the top level domain has expired (-k means 'insecure') so this is for testing purposes only and not recommended
curl -k -X POST https://helloworld.org:443/xyz/oauth2/token \
-H 'Content-Type: application/x-www-form-urlencoded' \
-d 'username=sadao002&password=dsf83837%%&grant_type=password'
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 26, 2023 at 4:52 am
This URL "https://helloworld.org:443/xyz/oauth2/token" is a fake one that I just created so I could as the question. I cannot provide the real URL. Sorry, I should have made that clear. But the real URL works and is the same URL I have inserted in the code, I mean the value of this variable "apiEndpoint" is exactly the same after I append "/token" to it compared to the real URL I used in Postman where I am successfully getting the token back. Thanks.
string apiEndpoint = "https://helloworld.org:443/xyz/oauth2";
July 26, 2023 at 5:00 am
Got it! The port 443 is I think the default. So we should be good there. Your point is valid though. Thanks.
July 26, 2023 at 2:40 pm
Steve,
You are going to laugh at this. and you were correct!! I was researching what httpclient.baseaddress should be and stumbled upon this stack post. https://stackoverflow.com/questions/23438416/why-is-httpclient-baseaddress-not-working
It says: "It turns out that, out of the four possible permutations of including or excluding trailing or leading forward slashes on the BaseAddress and the relative URI passed to the GetAsync method -- or whichever other method of HttpClient -- only one permutation works. You must place a slash at the end of the BaseAddress, and you must not place a slash at the beginning of your relative URI, as in the following example."
You did tell me to take out the forward slash "/" from the relative URI. But I did change the base URI to include that forward slash at the end thinking maybe the PostAsync method would by default put that forward slash in front of the word "token". It didn't and when the above explanation said that a slash must be placed at the end of BaseAddress which is the string variable "apiEndpoint", I did that. And it worked! I was able to get the bearer token.
Thank you so much for your help. I want to look at this yesterday but didn't have too much time so I looked at it this morning with some fresh eyes and wanted to tell you what I had discovered. Sorry, I need to reply in a more timely manner. I will strive for it. I will have more C#/script task/Script components questions for sure. Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply