Calling REST API in SSIS with Basic Authentication / Userid-Password (BASE64 Authorization header)

Introduction

In this blog post you will explain how to pass basic credentials (i.e. UserID/Password) along with your web request. You will also learn about setting up Authorization Header for HTTP Web Request in Base64 manually. To learn more about how to consume / call REST API in SSIS check this article.

HTTP Authorization Header basics

As per HTTP Standard you can pass credentials very simple way using basic Authorization header. Below is the sample of Basic Authorization header.

As you can see it consist of HeaderName=Authorization and Value=some base64 encoded string
Authorization: Basic QWxhZGRpbjpvcGVuIHNlc2FtZQ==
As per standard Base64 encoded string is made up with two elements. your_userid:your_password

NOTE: Base64 is encoding and not encryption method. So never hand over your encoded Authorization header string to anyone. Anyone can decode Base64 encoded string easily by using tools like this

Passing Basic Credentials along with HTTP Web Request

You can pass credentials (i.e. UserID/Password) in two different ways.

1. Automatic approach – Using ZS-HTTP Connection Manager (BASE64 Encoded Header automatically generated)
2. Manual approach – Supplying Authorization where UserID/Password encoded as Base64

Automatic approach – Using ZS-HTTP Connection Manager

Now lets look at how to use automatic approach to pass Basic Authorization in following 3 different tasks/components

1. Passing basic credentials in SSIS REST API Task
2. Passing basic credentials in SSIS JSON Source
3. Passing basic credentials in SSIS XML Source

Using SSIS REST API Task (Pass Basic credentials using Connection Manager)

The simplest way to pass credentials is use HTTP Connection Manager. If you are using REST API Task then you can use Url from Connection Access Mode. And on connection manager specify UserId and password to pass Basic Authorization Header

Advantages of this method are..
1) Its very simple
2) Credentials are encrypted in SSIS Package

NOTE: We recommend using https:// Urls whenever possible otherwise your web request can be decoded in plain text by any servers sitting between you and target and possibly they can get your password by decoding base64 header.

Base64 Encoded HTTP Basic Authorization Header – View in Fiddler

Using SSIS JSON/XML Source  (Pass Basic credentials using Connection Manager)

You can use HTTP Connection manager in JSON Source and XML Source too. Only difference is you have to check Use Credentials checkbox and select HTTP connection manager from available connection managers dropdown.

1. Select AccessMode as “File Path or Web URL”
2. Enter API URL
3. Check “Use Credentials” option
4. Click “New” next to Connection dropdown or select New “ZS-HTTP” connection from dropdown
5. Configure HTTP Connection as below (i.e. Enter URL, Select Basic Auth, Enter UserID, Password)
6. Clock OK on HTTP connection manager UI to save and now Test JSON or XML Source UI by clicking Preview (Assuming no other configuration needed)

SSIS JSON Source – Passing Basic Credentials (Supply UserID / Password) using HTTP Connection

Manual approach – Supplying Authorization where UserID/Password encoded as Base64

Our JSON Source, XML Source and REST API Task they all support passing BASE64 Encoded String for Authorization header (see below screenshot). Setting up userid and password in HTTP Connection Manger would do same trick as setting up it manually.

To encode any Header value in BASE64 format you simply have to add BASE64ENC format specifier after variable name.

Example: {{User::varUserAndPassword,BASE64ENC}}

SSIS JSON Source Task – Pass Base64 Encoded HTTP Authorization Header

Reference

Wikipedia Article – HTTP Basic Authorization Header

Download FREE SSIS Components