- 1 Introduction
- 2 HTTP Authorization Header basics
- 3 Passing Basic Credentials along with HTTP Web Request
- 3.1 Automatic approach – Using ZS-HTTP Connection Manager
- 3.2 Manual approach – Supplying Authorization where UserID/Password encoded as Base64
- 4 Reference
- 5 Download FREE SSIS Components
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.
- Automatic approach – Using ZS-HTTP Connection Manager (BASE64 Encoded Header automatically generated)
- 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
- Passing basic credentials in SSIS REST API Task
- Passing basic credentials in SSIS JSON Source
- 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.
- Select AccessMode as “File Path or Web URL”
- Enter API URL
- Check “Use Credentials” option
- Click “New” next to Connection dropdown or select New “ZS-HTTP” connection from dropdown
- Configure HTTP Connection as below (i.e. Enter URL, Select Basic Auth, Enter UserID, Password)
- 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)
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.
SSIS JSON Source Task – Pass Base64 Encoded HTTP Authorization Header
Wikipedia Article – HTTP Basic Authorization Header