{"id":1327,"date":"2017-06-13T14:34:23","date_gmt":"2017-06-13T14:34:23","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1327"},"modified":"2025-09-24T05:21:34","modified_gmt":"2025-09-24T05:21:34","slug":"rest-api-authentication-with-oauth-2-0-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/rest-api-authentication-with-oauth-2-0-using-ssis\/","title":{"rendered":"How to authenticate to an API with OAuth 2.0 using SSIS \/ ODBC"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

\"\"<\/a>In the world of REST APIs you have to know how to authenticate, before using any API\u00a0method. In this article we will cover a topic how to authenticate to\u00a0an API with OAuth 2.0<\/a>\u00a0technology using SSIS or ODBC Drivers. We are going to use OAuth Connection Manager <\/em>SSIS component to achieve that, which is a part of\u00a0ZappySys SSIS PowerPack<\/a>. Concepts listed in this articles also applicable for ODBC PowerPack<\/a> 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<\/p>\n

 <\/p>\n

\n

Prerequisites<\/span><\/span><\/h2>\n

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:<\/p>\n

    \n
  1. For SSIS:<\/b> SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\n
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven’t already).<\/li>\n
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\n
  5. For ODBC:<\/b>Download and Install\u00a0ZappySys ODBC PowerPack<\/a>\u00a0(This includes XML \/ JSON \/ REST API and few other drivers for SQL Server and ODBC connectivity in tools like Excel, Power BI, SSRS)<\/li>\n<\/ol>\n<\/div>\n

    Concepts about OAuth 2.0<\/h2>\n

    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<\/a> (or few more you can search). Don’t get overwhelmed by details \ud83d\ude42 because you don’t have to worry when you use SSIS OAuth Connection Manager<\/a>. Most of complexity of OAuth Protocol such as Token Extract, Token Renew is taken care by connection manager behind the scene for you.<\/p>\n

    Important things you will have to do to Call API in SSIS using OAuth<\/b> are listed below.<\/p>\n

      \n
    1. Register OAuth App to obtain ClientID \/ Client Secret<\/li>\n
    2. Confirm OAuth 2.0 Grant Type (By default<\/strong> it will be Authorization Code Grant<\/strong> (i.e. 3-legged grant – Which shows Login Page when you click Generate Token Button). If you see mention about Grant Type=Client Credentials<\/strong> or Password<\/strong> Grant on your API help file then on you must configure SSIS OAuth Connection Manager with OAuth Version=2.0<\/strong> and Change OAuth Grant Type<\/strong>\u00a0drop down on the UI to correct setting. Refresh Token is only supported in\u00a0Authorization Code Grant<\/strong>.<\/li>\n
    3. Obtain necessary Token URL (i.e. Token endpoint(s) – Usually documented in your API helpfile. Example : https:\/\/somesite\/oauth\/token )<\/li>\n
    4. Optional<\/strong> – Identify Scopes (i.e. Permission you want to grant to the token. Many times its not needed)<\/li>\n
    5. Optional<\/strong> – Identify redirect URL (Usually entered on App registration Page.. Same URL must be entered on the SSIS OAuth Connection Manager > Advanced Tab)<\/li>\n<\/ol>\n

      <\/h2>\n

      Video Tutorial<\/h2>\n
      <\/div><\/div>
      <\/div>
      <\/div>
      <\/div><\/div><\/div>