There OAuth to be a better way (Power BI)

Edit: The base URL has changed from https://api.powerbi.com/beta/myorg to https://api.powerbi.com/v1.0/myorg

OAuth is clearly becoming the de-facto standard for authenticating API calls around the web. In the business intelligence arena, while we can discuss its pros and cons, we undoubtedly must get acquainted with it because sooner or later we’ll be tasked with importing data from one of the many “OAuth protected” web services.

In this third release of the OAuth series I’ll show how to get an Access Token from Microsoft Power BI. The same procedure can be used for many other Azure services (by changing the appropriate scope in the resource parameter).

Our typical scenario would be an unattended server process downloading data; I’m using a headless Linux box with cURL and jq.

You can see here and here my previous posts about how to use cURL to authenticate with flickr or BigQuery and make API calls with an Access Token.

The way Azure works is a little different. You get an Access Token valid for 1 hour and a Refresh Token. You can reuse the Access Token for as many calls as you want during the hour, and then you’ll need to ask for a new Access Token presenting the Refresh Token.

Prerequisites:

  • An Azure subscription with a real work domain (no personal account)

loginazure

  • An Azure Active Directory so you can add users to @yourdomain

azuread

  • A Power BI subscription with a user belonging to your Azure Active Directory

azure users

Once you have the requisites in place, follow this article to create an app and get a Client ID.

clientid

With the username, password and the Client ID you can use this script to get an Access Token:

[code language=”bash” gutter=”true” light=”false”]
#!/bin/bash

OAUTH_CLIENT_ID="yourClientId"
OAUTH_USERNAME="youruser@yourdomain.com"
OAUTH_PASSWORD="yourpassword"

POST_RESULT="$(curl -s -X POST -d "resource=https://analysis.windows.net/powerbi/api&client_id="$OAUTH_CLIENT_ID"&grant_type=password&username="$OAUTH_USERNAME"&password="$OAUTH_PASSWORD"&scope=openid" "https://login.windows.net/common/oauth2/token" | jq -r .)"

REFRESH_TOKEN="$(echo ${POST_RESULT} | /usr/local/bin/jq -r .refresh_token)"
ACCESS_TOKEN="$(echo ${POST_RESULT} | /usr/local/bin/jq -r .access_token)"
AUTH_HEADER="Authorization: Bearer ${ACCESS_TOKEN}"
echo "${AUTH_HEADER}"

echo "${AUTH_HEADER}" > ./auth_header.txt
echo "${REFRESH_TOKEN}" > ./refresh_token.txt

[/code]

This script will save two files: one is the Authorization Header and the other is the Refresh Token. You will use the Authorization Header passing it to every API call that you make (during 1 hour), for example to get a list of the available datasets in your Power BI storage use:

[code language=”bash” gutter=”true” light=”false”]
#!/bin/bash

AUTH_HEADER=$(<./auth_header.txt)
curl -k -s "https://api.powerbi.com/beta/myorg/datasets" -H "$AUTH_HEADER" | /usr/local/bin/jq -r .

[/code]

After an hour or so, you will ask for a new Access Token and store the new Authorization Header (can also be crontabbed every nn minutes):

[code language=”bash” gutter=”true” light=”false”]
#!/bin/bash

REFRESH_TOKEN=$(<./refresh_token.txt)
OAUTH_CLIENT_ID="yourClientId"
OAUTH_USERNAME="youruser@yourdomain.com"
OAUTH_PASSWORD="yourpassword"

POST_RESULT="$(curl -k -s -X POST -d "resource=https://analysis.windows.net/powerbi/api&client_id="$OAUTH_CLIENT_ID"&grant_type=refresh_token&username="$OAUTH_USERNAME"&password="$OAUTH_PASSWORD"&scope=openid&refresh_token=${REFRESH_TOKEN}" "https://login.windows.net/common/oauth2/token" | jq -r .)"

REFRESH_TOKEN="$(echo ${POST_RESULT} | /usr/local/bin/jq -r .refresh_token)"
ACCESS_TOKEN="$(echo ${POST_RESULT} | /usr/local/bin/jq -r .access_token)"

AUTH_HEADER="Authorization: Bearer ${ACCESS_TOKEN}"

echo "${AUTH_HEADER}"
echo "${AUTH_HEADER}" > ./auth_header.txt
echo "${REFRESH_TOKEN}" > ./refresh_token.txt

[/code]

So, no browser, no GUI, no problem!

No One Here Gets OAuth Alive (flickr)

I love web services, I love API, I can’t say the same for OAuth!

It’s frustrating at times, when you’re not using any of the existing libraries to get the entire flow running.

If you have a headless server, with a terminal console and limited programming languages at hand, one of the best options you have is cURL.

There’s a lot of documentation out there about this tool, and the official manual is more than enough to start with.

I’ll try to guide you through the Kafkaesque OAuth process using a web service API that still uses the 1.0 revision of OAuth: flickr

To follow the steps all you need is cURL, openssl and sed. I am using Amazon Linux on EC2. I assume you already have a flickr account.

1. Get an API Key

api_key

2. Get a Request Token:

[code language=”bash” gutter=”true” light=”false”]
#!/bin/bash

timestamp="$(/bin/date +%s)"
nonce="$(/bin/date +%s%T%N | /usr/bin/openssl base64 | /bin/sed -e s’/[+=/]//g’)"

request_url="GET&https%3A%2F%2Fwww.flickr.com%2Fservices%2Foauth%2Frequest_token"

consumerkey=b284fdada……f2fbbe1145
consumersecret=17a……..c36

basestring="$(echo ${request_url}’&oauth_callback%3Doob%26oauth_consumer_key%3D’${consumerkey}’%26oauth_nonce%3D’${nonce}’%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D’${timestamp}’%26oauth_version%3D1.0′)"
echo ‘basestring=’${basestring}

signature="$(echo -n ${basestring} | openssl dgst -sha1 -hmac ${consumersecret}’&’ -binary | /usr/bin/openssl base64 | /bin/sed -e s’/+/%2B/’ -e s’///%2F/’ -e s’/=/%3D/’)"
echo ‘signature=’${signature}

request_output="$(/usr/bin/curl –compressed -s -H "Accept-Encoding: gzip,deflate" "https://www.flickr.com/services/oauth/request_token?oauth_callback=oob&oauth_consumer_key="${consumerkey}"&oauth_nonce="${nonce}"&oauth_signature="${signature}"&oauth_signature_method=HMAC-SHA1&oauth_timestamp="${timestamp}"&oauth_version=1.0")"
# echo ‘request_output = ‘"${request_output}"
echo ${request_output} | awk -F'[;&]’ ‘{print $1}’
oauth_token="$(echo ${request_output} | awk -F'[;&]’ ‘{print $2}’)"
oauth_token_secret="$(echo ${request_output} | awk -F'[;&]’ ‘{print $3}’)"

echo ${oauth_token}
echo ${oauth_token_secret}
echo ”
echo "Please ask your user to open this url: ‘https://www.flickr.com/services/oauth/authorize?"${oauth_token}"’"

[/code]

3. Get User Authorization:

From step 2 you need to note down the oauth_token and oauth_token_secret, you’ll need them later. Open in a browser window the url resulting from the step 2. Once you authorize it, you’ll get a code, this is the oauth_verifier:

verifier

4. Get an Access Token:

[code language=”bash” gutter=”true” light=”false”]
#!/bin/bash

timestamp="$(/bin/date +%s)"
nonce="$(/bin/date +%s%T%N | /usr/bin/openssl base64 | /bin/sed -e s’/[+=/]//g’)"

request_url="GET&https%3A%2F%2Fwww.flickr.com%2Fservices%2Foauth%2Faccess_token"

consumerkey=b284fdada……f2fbbe1145
consumersecret=17a……..c36
oauth_token=72157651980726063-9ee23f2f77cc1edb
oauth_token_secret=f448f55629f2092e
oauth_verifier=518-883-896

basestring="$(echo ${request_url}’&oauth_consumer_key%3D’${consumerkey}’%26oauth_nonce%3D’${nonce}’%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D’${timestamp}’%26oauth_token%3D’${oauth_token}’%26oauth_verifier%3D’${oauth_verifier}’%26oauth_version%3D1.0′)"
echo ‘basestring = ‘${basestring}

signature="$(echo -n ${basestring} | openssl dgst -sha1 -hmac ${consumersecret}’&’${oauth_token_secret} -binary | /usr/bin/openssl base64 | /bin/sed -e s’/+/%2B/’ -e s’///%2F/’ -e s’/=/%3D/’)"
echo ‘signature = ‘${signature}

request_output="$(/usr/bin/curl –compressed -s -H "Accept-Encoding: gzip,deflate" "https://www.flickr.com/services/oauth/access_token?oauth_consumer_key="${consumerkey}"&oauth_nonce="${nonce}"&oauth_signature="${signature}"&oauth_signature_method=HMAC-SHA1&oauth_timestamp="${timestamp}"&oauth_token="${oauth_token}"&oauth_verifier="${oauth_verifier}"&oauth_version=1.0")"
echo ‘request_output = ‘"${request_output}"

oauth_token="$(echo ${request_output} | awk -F'[;&]’ ‘{print $2}’)"
oauth_token_secret="$(echo ${request_output} | awk -F'[;&]’ ‘{print $3}’)"

echo ${oauth_token}
echo ${oauth_token_secret}

[/code]

5. Use the Access Token with API calls:

According to the OAuth 1.0 spec the Access Token should never expire. So you only need to do the steps 1-4 one time.

With every API call you should use the oauth_token you received in step 4, in this example I’m using the flickr.test.login

[code language=”bash” gutter=”true” light=”false”]
#!/bin/bash

timestamp="$(/bin/date +%s)"
nonce="$(/bin/date +%s%T%N | /usr/bin/openssl base64 | /bin/sed -e s’/[+=/]//g’)"

request_url="GET&https%3A%2F%2Fwww.flickr.com%2Fservices%2Frest"

consumerkey=b284fdada……f2fbbe1145
consumersecret=17a……..c36
oauth_token=721576519…..393-c6255e48d9fed8b7
oauth_token_secret=8547b…..1524ff0a

basestring="$(echo ${request_url}’&format%3Djson%26method%3Dflickr.test.login%26nojsoncallback%3D1%26oauth_consumer_key%3D’${consumerkey}’%26oauth_nonce%3D’${nonce}’%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D’${timestamp}’%26oauth_token%3D’${oauth_token}’%26oauth_version%3D1.0′)"
echo ‘basestring = ‘${basestring}

signature="$(echo -n ${basestring} | openssl dgst -sha1 -hmac ${consumersecret}’&’${oauth_token_secret} -binary | /usr/bin/openssl base64 | /bin/sed -e s’/+/%2B/’ -e s’///%2F/’ -e s’/=/%3D/’)"
echo ‘signature = ‘${signature}

request_output="$(/usr/bin/curl –compressed -s -H "Accept-Encoding: gzip,deflate" "https://www.flickr.com/services/rest?format=json&method=flickr.test.login&nojsoncallback=1&oauth_consumer_key="${consumerkey}"&oauth_nonce="${nonce}"&oauth_signature="${signature}"&oauth_signature_method=HMAC-SHA1&oauth_timestamp="${timestamp}"&oauth_token="${oauth_token}"&oauth_version=1.0")"
echo ‘request_output = ‘
echo "${request_output}" | /usr/bin/jq .
[/code]

if the call is successful you’ll receive your user id and username

output

# standing on the shoulders of giants: thanks to https://twittercommunity.com/t/can-you-get-public-timeline-using-oauth-by-only-using-curl-and-openssl-in-unix-shell/1476/2