Encoding basic authentication in an url with Power Query/M language

Power BI

More than one year ago, I edited a blog post about how to get data from Neo4j with Power BI and yesterday, Chris Skardon published a cover enriched with more details (useful if you’re new to Power BI). But Chris did more than a cover and also changed the way to authenticate. In my version, I circumnavigated the authentication obstacle by adjusting the server settings to let me run anonymous queries but Chris overcame it by providing a header to the web request (See bellow).

let
  Source = Web.Contents( "http://localhost:7474/db/data/transaction/commit",
    [
      Headers = [#"Authorization" = "Basic bmVvNGo6bmVv"],
      Content=Text.ToBinary("{
        ""statements"" : [ {
          ""statement"" : ""...""} ]
        }")]
      )
    ]
in
  Source

Chris explained that he encoded his user/password combination with the help of C# or PowerShell. Then he added this encoding string in the header for the Authorization tag, after the basic keyword.

This is a good idea but I thought it was also a good idea to explain how to execute this base64-encoding without relying on an external process … just with Power Query M Language!

The first step is to correctly format the string to encode. The expected format is user:password. I can create two parameters User and Password and then creating a new query named Credentials and apply the format.

let
    Source = User & ":" & Password,

After this you need to convert your text to bytes by the help of the function Text.ToBinary

    Bytes = Text.ToBinary(Source),

When you have the bytes, you need to encode them to a string with the help of the Base64 binary encoding.

    TextForm = Binary.ToText(Bytes, BinaryEncoding.Base64)
in
    TextForm

You can now edit the main query and replace Headers = [#"Authorization" = "Basic bmVvNGo6bmVv"], by Headers = [#"Authorization" = "Basic " & Credentials],.

That’s it, the encoding is performed in Power Query M language.

Full code:

let
    Source = User & ":" & Password,
    Bytes = Text.ToBinary(Source),
    TextForm = Binary.ToText(Bytes, BinaryEncoding.Base64)
in
    TextForm

4 comments

  1. Could this possibly work for the Teradata.Database() function?

    The connection string is: Data Source=myServerAddress;User ID=myUsername;Password=myPassword;

    For the .NET Teradata driver.

    Reply

    1. This post is about encoding authentication in an url … not sure what’s the link between a connection string and an url? Especially that Power BI as a native connector for Teradata. Did you want to comment on this https://seddryck.wordpress.com/2017/02/04/how-to-connect-to-mysql-with-nbi/ post? If yes, then the answer is “no” … you must use an oledb or ODBC driver not a native driver. Biut if you use an oledb or ODBC driver then “yes” it will work.

      Reply

  2. Excellent post. I’ve encountered an issue where a similar application works perfectly in Power BI desktop client, but errors out in the service (when published to the cloud) due to data source credential authorization. I’m using anonymous authentication, but the service still asks for credentials. Selecting “anonymous authentication in the web service does nothing…simply cycles through the same error.

    Reply

  3. Syddryck!

    I appreciate your explanation on encoding authentication in a URL. Your approach to circumventing authentication obstacles by adjusting the server settings and providing a header to the web request was really useful.

    Particularly the base64-encoding seems a great alternative for those not wishing to rely on an external process.

    I was just writing an article on BinaryEncoding and added the functions that can make use of it (https://powerquery.how/binaryencoding-base64/). It’s not as elaborate as your article but great for syntax.

    Thanks for the post, looking forward to more content on Power Query and BI.

    Best,
    Rick

    Reply

Leave a comment