|
OAuth Connection Manager
|
 
|
|
OAuth Connection Manager can be used along with REST API Task, JSON Source and XML Source to communicate with REST API Services which supports OAuth Security Protocol (e.g. Twitter, Facebook, Linkedin, Google, Microsoft). This connection manager handles complex authorization flow to obtain initial Token and renewing token automatically once its expired.
|
https://login.salesforce.com/services/oauth2/authorizeAuthorization Token URL
https://login.salesforce.com/services/oauth2/tokenScope
full offline_access
https://login.salesforce.com/services/oauth2/success
| Property Name | Description | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| RetainSameConnection | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| UserName | End-user’s username (only used when OAuth GrantType is Password). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| CustomAuthHeader | Custom header name which includes access token for each request. This option is useful for some API like eBay where you can use OAuth tokens for calling other API (e.g. SOAP / XML) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| CustomTokenPrefix | Prefix string you like to include before access token (e.g. Bearer). We will not add space after this prefix so use space after word if needed to separate prefix from token. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RemoveAuthHeader | Remove standard "Authorization" header if Custom Auth Header set. This option is ignored if you do not have CustomAuthHeader option set. Also this setting is only needed if some API do not like standard auth header along with custom auth header. If you have no case like that then do not set this option. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Provider |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| OAuthVersion |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RenewBeforeSec | How many seconds before token should be renewed. Enter 0 for default value=300 seconds (i.e. renew before 300 seconds). This setting is useful if API doesnt like token call too early (i.e. default 5 mins before expiration). If API token expiration is less than or equal to 600 seconds then renew before is set to 20 seconds before expiration. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RefreshTokenFilePath | File path to store token refresh information. Specify this path only if you know that your API keeps changing refresh token when you fetch new access token. For most API you dont have to worry about this option. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| EnableOAuthOverUrl | Set this option to true if your API requires OAuth credentials and signature to be passed via URL QueryString rather than HTTP Header (Authorization Header). This option is only valid for OAuthVersion=1.0 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| DoNotIncludeEmptyParamsInSignature | Do not include key=value pair in OAuth1 signature if value is empty. This setting is ignored for OAuth2. For certain API providers you have to check this setting to avoid signature mismatch. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| SignatureMethod | Signature Method for OAuth 1.0. Possible values are PLAINTEXT, HMAC-SHA1, HMAC-SHA256, HMAC-SHA512, RSA-SHA1, RSA-SHA256, RSA-SHA512 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| DoNotIncludeCredentialsInHeader | When you set this option to true Authorization header is not sent when grant type=Password or client credentials (OAuth 2.0). Change this option only when server rejects credentials passed via header. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| DoNotIncludeCredentialsInBody | When you set this option to true then password and userid is sent via Authorization header (Base64 encoded) rather than Body (e.g. userid=myuser&password=mypass123). Change this option only when you get error if credentials found in body. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ContentType | Set content type here (e.g. application/json) for /token call which needs JSON based input for POST call. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| AccessTokenAttrName | Set this to use non standard attribute name from the response for AccessToken (i.e. id_token). Leave this value blank to use default name as per OAuth 2.0 standard (i.e. access_token). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| AuthScheme | Auth scheme is a prefix in the Authorization header value. e.g. Authorization: Bearer xxxxxxxxxx. Default prefix is Bearer if you dont specify this property. To use blank prefix set this to {none} (including curly brackets). If your prefix has space then another space is not appended otherwise space automatically appended after prefix. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ScopeSeparator | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ExtraAttributesForTokenRequest | Some OAuth providers (e.g. Microsoft Dynamics AX API) may require you to pass additional information along with standard attributes (e.g. resource=SOME-URL) when you fetch new Access Token using Token URL. Enter additional attributes in key/value pairs separated by ampersand [&] (e.g. Prop1=aaaa&Prop2=bbbb ) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ExtraHeaders | If your provider requires you to pass additional header(s) for each token request or data request then supply this value. You can pass multiple headers in key/value pairs separated by double pipe (e.g. x-header1:AA||x-header2:BB). You can also use functions and system placeholder functions e.g. x-my-auth:<<[$clientid$]:[$clientsecret$], FUN_BASE64ENC>>. Allowed placeholders are [$clientid$], [$clientsecret$], [$userid$], [$password$]. Placeholders replaced first and then functions are invoked. See this help link for details about functions - https://zappysys.com/links/?id=10014 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| CacheToken | Set this option and enter a valid file path to cache the token. This option is helpful for some API providers, which enforce a limit on the number of active tokens. By checking this option, it reuses tokens across multiple connections / threads and processes so all of them uses only one active token and avoids extra token calls reducing number of token fetch calls. To enable token sharing you must use the same token cache file path in all connections connecting to the same API / using the same credentials. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ExtraAttributesForAuthRequest | Some OAuth providers (e.g. Zoho) may require you to pass additional information along with standard attributes (e.g. access_type=offline) when you start 3-legged oauth process using /auth URL. Enter additional attributes in key/value pairs separated by ampersand [&] (e.g. Prop1=aaaa&Prop2=bbbb ) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ReturnUrl | OAuth Return URL. After initial login short lived authorization code is sent to this URL before AccessToken is acquired. If you don't have a secure URL then use https://zappysys.com/oauth/ | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| OAuthGrantType | OAuth grant_type to use. Default is AuthorizationCode. For 3-legged authorization its AuthorizationCode (Popup login form and obtains token). If you want to user UserId/Password to get token then use Password grant (No login form displayed). If you want to use ClientID/ClientSecret to get token then use ClientCredentials grant type (No login form displayed). Refer your API documentation before assuming that your API supports these three grant types. Its possible that your API may support only one grant type out of these three types (i.e AuthorizationCode, Password and ClientCredentials).
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| TokenRefreshMode |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RequestTokenUrl | URL to get Access Token to make API calls. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| SslVersion | Ssl/Tls Protocol version for https URL. Change this setting if your server requires certain version of Ssl/Tls protocol.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ConnectionString | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| SupportPassword | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| UseClientCertificate | UseClientCertificate | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ClientCertificateStorageType | CertificateStorageType
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ClientCertificateStoreName | CertificateStoreName
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ClientCertificateThumbPrint | CertificateThumbprint | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ClientCertificatePath | CertificatePath | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Password | End-user’s password (only used when OAuth GrantType is Password). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ClientCertificatePassword | Password for X509 Client certificate | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| AccessTokenSecret | Token secret used to sign OAuth 1.0 Request (OAuth 1.0 enabled services such as Twitter use this) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| AuthorizationUrl | OAuth Endpoint Url which should be used to start Authorization process (consent/login screen) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| AccessTokenUrl | OAuth Endpoint Url which should be used to request Access Token | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Scopes | Scope(s) for OAuth. This is only used when you authorize for first time (login screen). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RefreshToken | Some OAuth 2.0 services such as Google API supports RefreshToken so when AccessToken expires you can request new AccessToken without going through manual authorization screen (login screen) again. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| AccessToken | AccessToken for authenticated REST API calls. This token is included along with each secure call you make to OAuth enabled REST API (Usually via Authorization Header. e.g. Authorization: Bearer xxxxxxxxxxxx). Some OAuth services such as Google expires AccessToken in short time (e.g. 1 hour) and some services such as Facebook has long lasting token (e.g. 60 days). Read your OAuth provider API documentation to learn more. If API supports renewing token then this connection manager will do automatically for you. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| UseCustomApp | UseCustomApp | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ClientId | Client Id or sometimes known as OAuth Application Id for your registered OAuth Application. If you are using DefaultApp provided by ZappySys then ignore this property. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ClientSecret | Client Secret or sometimes known as OAuth Application Secret for your registered OAuth Application. If you are using DefaultApp provided by ZappySys then ignore this property. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RetryMode | Error retry mode. Default is do not retry (None).
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RetryCountMax | Maximum number of retry attempts to try before throwing error. This option is ignored if RetryMode is set to None (Do not retry). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RetryMessageList | This option retries on a static retry_wait_time you entered using this formula. new_retry_wait_time = entered retry_wait_time * current_retry_attempt_counter (after each failed attempt attempt_counter is increased). So for example if your retry wait time is set to 3000ms (3sec) then with this option turned on you will see wait as follow: On 1st attempt = 3000*1 wait time= 3000ms, On 2nd attempt 3000*2=6000ms wait time, On 3rd attempt 3000*3=9000ms wait time... so on. This option is ignored if RetryMode is set to None (Do not retry). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RetryMultiplyWaitTime | This option increases wait time on each retry attempt using this formula. new_retry_wait_time = specified retry_wait_time * retry_attempt_counter (after each failed attempt retry_attempt_counter is increased by one). So for example if your retry wait time is set to 3000ms (3sec) then with this option turned on you will see wait as follow: On 1st attempt = 3000*1= 3000ms wait time, On 2nd attempt 3000*2=6000ms wait time, On 3rd attempt 3000*3=9000ms wait time... so on until max attempts reached. This option is ignored if RetryMode is set to None (Do not retry). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RetryStatusCodeList | Specifies a list of http status codes on which you like to retry. This option can be used if you want to narrow down retry attempts on a very specific list of http status codes separated by pipe rather than any random web exception. Use pipe to separate values e.g. 429|500 so on to retry for only these 3 status codes rather than any web exceptions. Refer to your API documentations to findout which errors you can retry on. This option is ignored if RetryMode is set to None (Do not retry). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| RetryWaitTimeMs | This value specifies how much time to wait (in milliseconds) before trying again on error(s). This option is ignored if RetryMode is set to None (Do not retry). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| UseProxy | Enable custom proxy settings (If this is not set then system default proxy will be used. To disable proxy totally uncheck this option and check DoNotUseDefaultProxy option if available) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ProxyUrl | Web URL of Proxy server (including port if necessary). [e.g. http://myproxyserver:8080/] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| DoNotUseDefaultProxy | Do not use system default proxy (This setting is ignored if UseProxy=true) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ProxyUseCreds | Enable passing userid and password to proxy server | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ProxyPassword | Proxy password | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ProxyUserName | Proxy username | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ConnectByProxy |
|
Load data from Exact Online into SQL Server using SSISIntroduction In this article, you will learn how to load data from Exact Online into SQL Server using SSIS. Exact Online is a popular CRM, ERP and HRM tool which also lets you manage your finances. As an example, we will use Exact Online contacts as data to load it into a SQL Server database. […] |
|
Read Visual Studio Team Services data in SSIS (TFS Online) – Call REST API / Load to SQL ServerIntroduction In this blog, we will learn How to read Visual Studio Team Services data in SSIS and load into SQL Server Table, along with few other topics such as. How to register an OAuth App for Visual studio Team Service – TFS Online (Team Foundation Server) REST API Call How to read all Issues data […] |
|
How to Import / Export data from Zuora API using SSISIntroduction In this post, we will learn how to call Zuora API using SSIS. We will go through steps to read data from Zuora and load into SQL Server using SSIS REST API Task and SSIS JSON Source Connector . At the end of this article, you will find a complete sample (dtsx file). Tasks/Components in […] |
|
Import / Export data from Amazon Athena using SSISIntroduction In our previous post we explored unlimited possibilities to call Amazon AWS API using SSIS. Now in this post we will learn how to import / export data from Amazon Athena using SSIS. Amazon Athena is very similar service like Google BigQuery which we documented already. To read data from Amazon Athena we will use […] |
|
How to run SSIS package with sensitive data on SQL ServerIntroduction In this article we will cover the steps how to run an SSIS package with sensitive data on SQL Server. 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 […] |
|
How to call Amazon AWS API using SSIS (EC2, Lambda, API Gateway, SQS)Introduction In this blog post you will learn how to call Amazon AWS API using SSIS (virtually any API) without a single line of code (No more JAVA, C#, Ruby, Python). Yes you heard it right 🙂 . If you are a SSIS / ETL Developer or even coder everyone loves drag & drop interface. SSIS […] |
![[Sticky] How to authenticate to an API with OAuth 2.0 using SSIS](https://i1.wp.com/zappysys.com/blog/wp-content/uploads/2017/09/api-180x180.png?resize=150%2C150&ssl=1)
|
[Sticky] How to authenticate to an API with OAuth 2.0 using SSISIntroduction 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. We are going to use OAuth Connection Manager SSIS component to achieve that, which is a part of ZappySys SSIS […] |
