- 1 Introduction
- 2 Prerequisites
- 3 Concepts about OAuth 2.0
- 4 Video Tutorial
- 5 Grant Types in OAuth 2.0
- 6 Understand AccessToken and RefreshTokens in OAuth 2.0
- 7 Step-by-Step – API Authentication with SSIS OAuth Connection
- 8 Using System Default Browser to get the token
- 9 What is Google OAuth AccessToken
- 10 Using OAuth 2.0 in ODBC Drivers / Data gateway
- 11 Parameterization and deployment to production
- 12 Conclusion
- 13 Keywords
In the world of REST APIs you have to know how to authenticate, before using any API method. In this article we will cover a topic how to authenticate to an API with OAuth 2.0 technology using SSIS or ODBC Drivers. We are going to use OAuth Connection Manager SSIS component to achieve that, which is a part of ZappySys SSIS PowerPack. Concepts listed in this articles also applicable for ODBC PowerPack product. If you like to access API data in BI Tools (e.g. Power BI, Excel, MS Access) or SQL Server directly then use ODBC PowerPack
PrerequisitesBefore we perform steps listed in this article, you will need to make sure following prerequisites are met:
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages
Concepts about OAuth 2.0
In this post we will not cover in depth about OAuth 2.0 protocol. If you want to learn how OAuth 2.0 Protocol works then check article like this (or few more you can search). Don’t get overwhelmed by details 🙂 because you don’t have to worry when you use SSIS OAuth Connection Manager. Most of complexity of OAuth Protocol such as Token Extract, Token Renew is taken care by connection manager behind the scene for you.
Important things you will have to do to Call API in SSIS using OAuth are listed below.
- Register OAuth App to obtain ClientID / Client Secret
- Confirm OAuth 2.0 Grant Type (By default it will be Authorization Code Grant (i.e. 3-legged grant – Which shows Login Page when you click Generate Token Button). If you see mention about Grant Type=Client Credentials or Password Grant on your API help file then on you must configure SSIS OAuth Connection Manager with OAuth Version=2.0 and Change OAuth Grant Type drop down on the UI to correct setting. Refresh Token is only supported in Authorization Code Grant.
- Obtain necessary Token URL (i.e. Token endpoint(s) – Usually documented in your API helpfile. Example : https://somesite/oauth/token )
- Optional – Identify Scopes (i.e. Permission you want to grant to the token. Many times its not needed)
- Optional – Identify redirect URL (Usually entered on App registration Page.. Same URL must be entered on the SSIS OAuth Connection Manager > Advanced Tab)
Grant Types in OAuth 2.0
There are multiple Grant Types in OAuth. API may enforce one or other based on Security Level they want to enforce. You can look at your API documentation to find out which Grant Type they are using. In above example of Hubspot API we used Authorization Code Grant (Its Default grant type in SSIS OAuth Connection Manager)
Authorization Code Grant Type
This is most common grant type for any popular API you may access (e.g. Google API, Facebook, LinkedIn ). In this grant type it requires 3-legged authorization for first time before you can obtain Access / Refresh Token. Basically you will see Login Prompt for first time which you have to Accept before you can get token(s). After that you may not have to Log in again if you are using ZappySys SSIS OAuth Connection manager. Refreshing Token is automatica after first login call.
Client Credentials Grant Type
In this grant you have to obtain ClientID and Secret from API Portal somewhere (Usually found under OAuth App Section). This is a simple Grant Type and you don’t have to use your Real Credentials (UserID/ Password) to call API. Check this article to learn more about this option.
Password Grant Type
In this grant you may use your User ID and Password to obtain Token for API call. This is not very popular Grant Type because you have to use your real credentials to make API call.
Understand AccessToken and RefreshTokens in OAuth 2.0
Along with GrantType there is one more concept in OAuth you have to understand. Its about two types of Tokens you get in OAuth 2.0
- Access Token
- Refresh Token
Here is brief description about each type of token.
If you are using Authorization Code Grant Type (see previous section) then most API will send you RefreshToken and AccessToken after you finish 3-legged authorization first time (i.e. Login Dialog box). Refresh Token usually never expires. Some API may expire refresh tokens but after a long time (e.g. 90 days). If you have case of expiring RefreshToken then you may have to use this Pattern
Access Token is needed to call API and its usually short lived (expires in 1-2 hours).
If you are using Authorization Code Grant Type then you have to use RefreshToken to get new AccessToken.
Step-by-Step – API Authentication with SSIS OAuth Connection
Now let’s look at an example of how to call API using SSIS OAuth Connection Manager and REST API Task. We will use HubSpot API as an example but concepts remain same for any other API such as Google API, Facebook, Twitter, LinkedIn
- Login into the service provider (whose API you want to use) membership area.
- Find API settings and create an application (API App). It will be your little bridge between user’s data and SSIS. Usually you will have to configure just a few fields:
- Scopes, your API App will be operating under
E.g. for HubSpot, it looks like this:
- Once you created an application, you should see fields Client ID and Client Secret nearby:
- Write down Client ID and Client Secret. We will need them later for authentication.
- Open SSIS Designer and create a new package.
- Right mouse click in the Connection Managers area and press New connection… and then ZS-OAUTH. This will create a new OAuth Connection.
- Select OAuth Provider which will be used by an API when authenticating. Usually, OAuth Provider’s name is present in API’s name you want to use. E.g. Select Google as OAuth Provider for Google Sheets API:
- This step is optional (use it if you didn’t find predefined OAuth Provider). For custom OAuth Provider you will need to configure:
- Authorization Url and Access Token Url, which you will find in a guideline of an API.
For HubSpot API these look like this:
- Callback/Return Url, which you should have the same as Redirect Url field in you API App settings.
For HubSpot API it is alright to enter your secure website address (with https). In this example, we will use https://zappysys.com/oauth.
- Authorization Url and Access Token Url, which you will find in a guideline of an API.
- Now it’s time to supply Client Id and Client Secret you wrote down in Step #4, enter Scopes / Permissions your user will need (we will use
contacts) and Generate a Token:
- Login with user account whose data you wish to access or just select a user if no login is needed:
- Grant user access to your API App:
- You will get a prompt to save Access and Refresh tokens to a backup file. Press “Yes” to save them.
- That’s it! From now on your SSIS task/component via your API App will be able to use user’s data which you just granted access to.
Using System Default Browser to get the token
If embedded browser has some issue to extract your token then you can close the window by pressing [X] in the title bar. Once you do that you will be prompted to use System Default browser to finish the token extraction. If you click Yes then Full browser will be launched.
NOTE: Below feature is added in version published after Dec 31, 2019 (v18.104.22.16831 or later) so make sure you have latest version.
Untrusted App Warning
Sometimes Google might show you below warning. In order to continue the flow you might have to Click on Advanced and then click Go to YourAppName (unsafe) like below
Select the account
Copy code and finish the flow
What is Google OAuth AccessToken
Using OAuth 2.0 in ODBC Drivers / Data gateway
So in previous section we talked how to setup OAuth in SSIS. If you like to use similar setup for Drivers in ODBC PowerPack then use below steps.
Following 3 drivers support OAuth Connection.
ODBC Driver OAuth Setup
If you are using ZappySys ODBC Drivers for API integration then use below steps to configure OAuth Connection to call API.
- Search for ODBC in start menu and select ODBC Data Source (64 bit)
- Click ADD to Create a New ODBC DSN (Use System Tab if you creating DSN accessed by service account or other users on that machine)
- Select desired ZappySys API driver (e.g. JSON Driver, XML Driver or CSV Driver)
- When UI is visible like below you can select Connection Type dropdown to OAuth and click Configure.
Data Gateway OAuth Setup
If you are using Data Gateway for integration scenario like Import API data in SQL Server then use almost same steps as previous section (ODBC DSN Setup) but in this case we have to click on New Data Source on Gateway Config UI as below.
In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way, you won’t face authentication related errors when a package is deployed to a production server.
Check our article on how to configure packages with sensitive data on your production or development server.
In this tutorial we learned how to access user’s data from within SSIS using an API by authenticating with OAuth 2.0. As you have probably noticed already, OAuth 2.0 was used not only for authentication, but for authorization too (remember the Scope you had to set?). In subsequent article you will really get your hands dirty (not really, it is actually simple) and learn how to call an API method using SSIS REST API Task.
Connect to a RESTful API with OAuth using SSIS | How to authenticate to an API with OAuth using SSIS | Authenticate a user using OAuth with SSIS | Grant user access to API using OAuth from within SSIS | Authorize a user to access REST API with OAuth using SSIS