Configuring an ADF Linked Service Connection for QuickBooks Online
For information on how to enable a connection for use with linked services, see Configuring a Linked Service Connection.
Field labels in this walkthrough follow the underlying Azure Data Factory property names, with the connection column that carries each value named alongside. The exact labels shown in the BimlFlex Connection Editor may differ.
After selecting QuickBooks from the Linked Service Type dropdown, the form required for creating a QuickBooks Online Linked Service will appear.
Source Only Connector
A QuickBooks Online connection is used as a source. It is not available as a target.
Objects of a QuickBooks Online connection are typed as Table or View.
The dataset addresses each object as a table, taking the table name from the TargetObjectName parameter.
Each QuickBooks Online object is copied whole. The copy passes the TargetSchemaName parameter and the source object name in the TargetObjectName parameter to the dataset, and BimlFlex composes no source query for the object.
A filled Override SQL on the object holds a QuickBooks native query and is passed whole as the copy source query. When the Override SQL is empty, the text of the object's Override SQL extension point is passed the same way.
The object's Where SQL, Group By SQL and Order By SQL and the Append Source SQL extension point do not apply to a QuickBooks Online object.
Choosing an Authentication Method
| Method | Credentials | Additional Settings |
|---|---|---|
| OAuth 2.0 | Service Principal Id, Service Principal Key and Refresh Token | companyId |
Recommendation: Store the Service Principal Key and the Refresh Token in Azure Key Vault rather than entering them as plaintext.
Complete Example: OAuth 2.0 with Azure Key Vault
| Field | Example Value |
|---|---|
| Connect via Integration Runtime | AutoResolveIntegrationRuntime |
| Endpoint | quickbooks.api.intuit.com |
| Company ID | 1234567890 |
| Service Principal Id | <client-id-of-quickbooks-app> |
| Azure Key Vault Linked Service | bfx-kv |
| Secret Name (for Service Principal Key) | BFX-LND-QuickBooksConsumerSecret |
| Secret Name (for Refresh Token) | BFX-LND-QuickBooksRefreshToken |
Required Fields
Common Required Fields
A QuickBooks Online Linked Service connection uses a single Authentication Type, so the same fields are required on every connection.
These common required fields are:
- Connect via Integration Runtime
- Endpoint
- Company ID
- Service Principal Id
- Service Principal Key (or Azure Key Vault)
- Refresh Token (or Azure Key Vault)
Connect via Integration Runtime
Connect via Integration Runtime names the integration runtime for a QuickBooks Online Linked Service connection.
The default value is AutoResolveIntegrationRuntime.
To use another runtime, select it from the dropdown or choose + New to add one.
When a custom value is saved in the linked service form, it will be added to the custom integration runtimes.
The custom values that appear in this dropdown can be maintained in Settings under Azure - AzureIntegrationRuntime.
Endpoint
The Endpoint identifies the QuickBooks Online server the connector reads from. The value is quickbooks.api.intuit.com.
It is stored in the connection's LS_Endpoint column and maps to the ADF endpoint property.
When the Endpoint column is empty, the value is read from the connection's Additional Settings (LS_AdditionalSettings) under the key endpoint.
Company ID
The Company ID identifies the QuickBooks company to authorize (for example, 1234567890).
The Company ID is stored in the connection's Additional Settings (LS_AdditionalSettings) under the key companyId, and maps to the ADF companyId property.
In the BimlFlex Connection Editor, the Company ID is entered in the Company ID field on the ADF Linked Service tab.
Authentication Type
The QuickBooks Online Linked Service connection signs in with OAuth 2.0 and the refresh token. BimlFlex does not read the connection's Authentication Type column for this connector. BimlFlex has no interactive sign-in for QuickBooks Online. Tokens and secrets are typed in or referenced from Azure Key Vault.
OAuth 2.0
OAuth 2.0 authentication authorizes API access with the consumer key and consumer secret of the QuickBooks Online application and a refresh token.
The Service Principal Id supplies the consumer key and is stored in the connection's LS_ServicePrincipalId column. It maps to the ADF consumerKey property.
In the BimlFlex Connection Editor, the consumer key is entered in the Consumer Key field on the ADF Linked Service tab.
The Service Principal Key supplies the consumer secret as plaintext in the LS_ServicePrincipalKey column, or as an Azure Key Vault reference held in the LS_ServicePrincipalKeyKVS_ fields that resolves to the secret at runtime. It maps to the ADF consumerSecret property.
In the editor, the consumer secret is typed on the Consumer Secret tab, or the AKV Linked Service, Secret Name and optional Secret Version of the reference are entered on the Azure Key Vault tab beside it.
The Refresh Token is entered as plaintext in the LS_RefreshToken column, or as an Azure Key Vault reference held in the LS_RefreshTokenKVS_ fields that resolves to the token at runtime. It maps to the ADF refreshToken property.
In the editor, the token is typed on the Refresh Token tab, or the AKV Linked Service, Secret Name and optional Secret Version of the reference are entered on the Azure Key Vault tab beside it.
QuickBooks refresh tokens rotate, so a stored refresh token can expire between builds and is then replaced with a current one in Azure Key Vault or in the connection.
It is suggested that Azure Key Vault be used in place of manually entering the Service Principal Key and the Refresh Token.
Azure Data Factory Linked Service Additional Information
For additional information on ADF QuickBooks Online Linked Service and its connection requirements see the Azure Data Factory QuickBooks Online Connector documentation.