Quickbooks v5.0 serial key or number

This section shows how to connect in typical scenarios and details any necessary connection properties. See Establishing a Connection for a guide.

See Also

1. Sources: The Getting Started section provides you with the information you will need to create a data source on the Connections tab of the CData Data Sync administration console.
2. Destinations: After creating a source, create a destination on the Connections tab.
3. Jobs: Configure replication from the source to the destination on the Jobs tab.

To create the QuickBooks data source, click Add Connection on the Connections tab.

Connecting to QuickBooks

The Data Sync App makes requests to QuickBooks through the Remote Connector. The Remote Connector runs on the same machine as QuickBooks and accepts connections through a lightweight, embedded Web server. The server supports SSL/TLS, enabling users to connect securely from remote machines. The first time you connect, you will need to authorize the Data Sync App with QuickBooks. For more information, refer to our Using the Remote Connector guide.

Follow the steps below to authorize with QuickBooks and connect to a company file when both QuickBooks and the Data Sync App are running on your local machine.

1. Open QuickBooks as an administrator and open the company file you want to connect to.
2. Connect to QuickBooks. A dialog will appear in QuickBooks prompting you to authorize the Data Sync App. After granting access to the Data Sync App, you can now execute commands to QuickBooks.

3. If you want to connect to the company file when QuickBooks is closed, set the CompanyFile connection option when you execute commands. QuickBooks will open automatically in the background with the file specified.

   Note that if QuickBooks is open through the application UI, only that CompanyFile can be used.

The Remote Connector can be used to read and write to QuickBooks in situations where direct COM access to QuickBooks is not available (e.g., ASP.NET, Java, or QuickBooks on a remote machine). Follow the procedure below to connect to QuickBooks for the first time through the Remote Connector:

1. If you have not already done so, download the Remote Connector from remoteconnector.com and install the Remote Connector on the machine where QuickBooks is installed.
2. Open the company file you want to connect to in QuickBooks using an administrator account in single-user mode.

3. Open the Remote Connector from the system tray and add a user on the Users tab. Enter a User and Password and select the level of access in the Data Access menu.

   Note: The Remote Connector does not use the User and Password properties to access QuickBooks; the User and Password properties authenticate the user to the Remote Connector. Authentication to QuickBooks is handled based on the ApplicationName property.

4. When you first connect, a dialog will appear in QuickBooks prompting you to authorize the application. After authorizing the application, you can then execute commands to QuickBooks. Specify the URL of the Remote Connector and the User and Password. By default, the Remote Connector connects to the currently open company file.
5. If you want to access QuickBooks when QuickBooks is not running, save the company file information for the user. The Remote Connector will then automatically open QuickBooks in the background with the company file for that user.

Note that if the QuickBooks UI is open, you can only connect to that company file. Additionally, note that the user permissions you run the Remote Connector under must match the user permissions you run QuickBooks under. The Remote Connector installation process installs the Remote Connector as a service under the current user account.

How do I Connect to QuickBooks over SSL/TLS?

You can enable SSL/TLS on the Advanced tab.

You will also need to send your public key certificate to the Data Sync App. You can do so by setting the SSLServerCert property.

The following sections show how to resolve common connection errors and detail functionality that may be needed in more advanced integrations. Contact the support team for help tracing the source of an error or circumventing a performance issue.

Troubleshooting

Automatic Login Access Denied or Access Denied

Try the following to resolve this error:

• If this error occurs the first time that you connect to a company file, ensure that QuickBooks is open as an administrator in single-user mode. Be sure to open the company file that you want to allow access to.
• The application may have permission to access the company file but not to log in automatically. To allow automatic login, open the company file preferences in QuickBooks by clicking Edit -> Preferences -> Integrated Applications -> Company Preferences and then select the Remote Connector application. Then, try connecting again.
• In the company file preferences, ensure that the following option is NOT checked: "Don't allow any applications to access this company file".
• Set DelayAfterClose.

Could Not Start QuickBooks

This is the most common error and the solution depends on your specific QuickBooks installation. Follow the steps below to identify several typical causes for this error.

1. Check the log for the QuickBooks request processor, QBSDKLOG.txt. The file is not necessarily in the same directory as QuickBooks. The location of the file depends on the version of QuickBooks and the operating system you are using. The qbsdklog.txt may be in the following locations:

   C:\Program Files\Common Files\Intuit\QuickBooks

   C:\ProgramData\Intuit\QuickBooks

   C:\Documents and Settings\All Users\Intuit\QuickBooks

   Modify the QBSDK.INI file by appending the following value to the end:
   

   [LOG] LEVEL=verbose UnlimitedSize=Y

   This ensures that the log is not cleared and that the error message in the QBSDKLOG.txt file is actually captured.

2. Check if the error message in this file matches one of those below:

   Is your company file on a mapped drive? If so, the QBSDKLOG will contain entries similar to the ones below: 20140623.102658 E 5136 RequestProcessor Unknown QBInstanceFinder error. File Name: "S:\Quickbooks\MyCompanyFile.qbw" hr = 80070057 20140623.102658 E 5136 RequestProcessor Could not find or create an instance of QuickBooks using InstanceFinder hr = 80040403 The solution is to use the network path instead of a mapped drive.

   Does the QBSDKLOG contain many error messages like the one below? 20140612.145409 I 272 RequestProcessor Previous instance of QB is going down..! Wait and retry to get a new instance. This error can occur if a request is received to start a new QuickBooks instance while the previous QuickBooks instance is closing. Repeated connections to QuickBooks in short succession can cause abnormal behavior. You can set DelayAfterClose to wait before a new connection is opened. You can also reuse the same connection for multiple requests by enabling persistent connections on the Advanced tab in the Remote Connector and then setting the QBXMLVersion property in the connection string.

   In addition to enabling persistent connections, you can use the following Remote Connector settings to have more control over starting and stopping the QuickBooks process. When enabled, the Remote Connector will take steps to resolve connection issues if the conditions below are met. In some scenarios, the Remote Connector may forcibly end the QuickBooks process. USE THESE SETTINGS WITH CAUTION: CloseAndRetryConnect: Boolean with a default of 'false'. If set to true, the Remote Connector will attempt to close the QuickBooks process if an error is encountered when the Remote Connector attempts to launch a new instance of QuickBooks. All other settings are only applicable when this setting is set to true. CloseAndRetryCount: Integer with a default of 3. The number of times to try to close and relaunch QuickBooks. Our developers found that 3 worked in all cases we encountered. CloseAndRetryTimeout: Integer with a default of 30. The number of seconds to wait for the current QuickBooks process to close before trying to connect again. CloseAndRetryErrorList: Comma-separated list of error codes with a default of '0x80040408,0x80040402'. Specifies a list of error codes that will cause the Remote Connector to try and forcibly end the QuickBooks process. A value of '*' means all errors. It is not recommended that you change this property unless you have a very specific reason to do so. You can set each of these properties as registry keys in HKEY_LOCAL_MACHINE\SOFTWARE\RemoteConnector. In most situations, only CloseAndRetryConnect needs to be set the True. Restart the Remote Connector for the changes to take effect.

Fine Tuning Data Access

You can use the following properties to gain more granular control over how the Data Sync App surfaces functionality in the QuickBooks API:

• CustomFieldMode: By default, custom fields are displayed and modified as XML values. However, you can also use JSON to work with custom fields.
• IncludeLineItems: By default, line items are returned only when you query a line item table such as InvoiceLineItems. This saves performance when querying base transaction tables such as Invoices. Set this property to true to retrieve line item data when you query the base transaction table.
• IncludeLinkedTxns: By default, the Data Sync App does not retrieve linked transactions when you query a base table, similar to IncludeLineItems.

Customizing the SSL Configuration

By default, the Data Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false and in addition set the following.

To authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties: Set FirewallType, FirewallServer, and FirewallPort. To tunnel the connection, set FirewallType to TUNNEL. To authenticate specify FirewallUser and FirewallPassword. To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Troubleshooting the Connection

To show Data Sync App activity from query execution to network traffic, use Logfile and Verbosity. The examples of common connection errors below show how to use these properties to get more context. Contact the support team for help tracing the source of an error or circumventing a performance issue.

• Authentication errors: Typically, recording a Logfile at Verbosity 4 is necessary to get full details on an authentication error.
• Queries time out: A server that takes too long to respond will exceed the Data Sync App's client-side timeout. Often, setting the Timeout property to a higher value will avoid a connection error. Another option is to disable the timeout by setting the property to 0. Setting Verbosity to 2 will show where the time is being spent.

• The certificate presented by the server cannot be validated: This error indicates that the Data Sync App cannot validate the server's certificate through the chain of trust. (If you are using a self-signed certificate, there is only one certificate in the chain.)

  To resolve this error, you must verify yourself that the certificate can be trusted and specify to the Data Sync App that you trust the certificate. One way you can specify that you trust a certificate is to add the certificate to the trusted system store; another is to set SSLServerCert.

The Remote Connector for QuickBooks is a simple application that facilitates connections to company files from your application. The Remote Connector accepts connections via a lightweight embedded Web server that runs on the machine where QuickBooks is installed. The server supports SSL/TLS, enabling users to connect securely from remote machines.

The first time you connect to QuickBooks, you must authorize your application. Complementing the per-application authentication of QuickBooks, the Remote Connector has per-user authentication. Before connecting to QuickBooks for the first time, configure at least one Remote Connector user.

You can configure users through the UI on the Users tab. You can then follow the procedure in "Getting Started" to connect an application to QuickBooks. After connecting, you can monitor QuickBooks connections on the Status tab.

It is recommended to configure the Remote Connector in the UI, but you can also run the Remote Connector from the command line. This can simplify deploying the Remote Connector in scenarios where normally there is not a user logged in, such as running a Web server. See the Advanced page to configure the Remote Connector when you are not using the UI.

The Remote Connector automatically manages the connection to QuickBooks, but you can configure almost every aspect of how users connect to QuickBooks through the Remote Connector. The following pages outline the capabilities of the Remote Connector and how to get started.

The Users tab provides an interface to add, edit, and delete users. At least one user must be added before communicating with QuickBooks.

This tab displays a list of existing users along with information about the user's configuration.

When adding or editing a user, the following options are available:

• User: Sets the username. This is required.
• Password: Sets the password for the user. This is required when using Basic authentication (default).
• Company File: Specifies the company file with which the application will communicate. By default this is the company file that is currently open in QuickBooks. This can also be set to the absolute path to a company file (.qbw file). A company file must be specified in order to access the company file when QuickBooks is closed.
• Authentication: Specifies the type of authentication to perform when the user connects. The Remote Connector supports the following authentication methods:

  Basic Authentication (default):	Authenticates the user with a username and password.
  Windows Authentication:	Authenticates the user as a Windows user. In this case the Password field is not applicable. When the Remote Connector receives a connection request, it will authenticate the user to Windows using the credentials supplied in the request.

• Application Name: Optionally sets the name of the application as seen by QuickBooks. Authentication to QuickBooks is handled based on the provided application name.
• Data Access: Specifies the allowed access for the user.

  Full:	Allows read and write access for the user.
  Read-only:	Restricts the user to read-only operations. QuickBooks data cannot be modified.

The Test Connection button provides a quick way to verify the application can connect with QuickBooks.

When a user is added the Remote Connector will prompt you to authorize the application with QuickBooks if necessary.

The Status tab provides a log of the activity happening with the Remote Connector. Logs can be cleared or copied by right-clicking in the Recent Activity window.

You can adjust the detail of the logs to include information useful when troubleshooting: Select the granularity in the Log Mode menu on the Advanced tab. On the Advanced tab, you can also configure the Remote Connector to write logs to a file and select the log rotation interval.

The Advanced tab allows granular control over the Remote Connector's server. The Remote Connector contains an embedded Web server that runs as a Windows service and listens for HTTP requests. Each request contains the XML data to be communicated to QuickBooks as well as configuration settings specifying how the connection is to be opened. The Remote Connector then communicates with QuickBooks via COM and returns the QuickBooks response (or an error message) in the HTTP reply.

This chapter details how to control each of these aspects of connecting to QuickBooks through the UI, command-line interface, and the registry. The following sections detail the options available on the Advanced tab.

Logging Options

• Write Logs to a Folder: Enables or disables writing log files to the specified folder in addition to writing logs to the Status tab.
• Folder: Specifies the folder where log files are written.
• Log Rotation: Determines how logs are organized on disk. Creates one file for each day, week, or month, depending on the following values:

  Daily (default):	Uses a new log file every day. Files are written with the format "yyyy_MM_dd.txt". For example, "2013_09_23.txt".
  Weekly:	Uses a new log file every week. Files are written with the format "yyyy_ww.txt". For example, "2013_34.txt", where 34 means this is the 34th week of 2013.
  Monthly:	Uses a new log file every month. Files are written with the format "yyyy_mm.txt". For example, "2013_09.txt".

• Log Mode: Sets the verbosity of the log output. In most situations, Info (the default) is sufficient. The Verbose option is helpful for debugging purposes.

IP Options

• Port: The port on which the server listens.
• Allowed Clients: A comma-separated list of host names or IP addresses that can access the server. The wildcard character '*' is supported. If unspecified (default) any client can connect.

Enabling Persistent Connections

All communications to QuickBooks company files must first go through QuickBooks. If QuickBooks is closed, this means that for each attempt to connect to the company file, QuickBooks needs to be launched and then closed again. By default the Remote Connector queues requests for data and performs the necessary authentication for each request. The following options can be used to override this behavior and keep the connection to the company file alive after the query finishes executing, so further requests will respond more quickly.

Warning: If a user attempts to manually open QuickBooks while a persistent connection is opened, QuickBooks will throw an error stating that the company file is already in use.

• Enable Persistent Connection: This is disabled by default: Normally your code controls when the connection to QuickBooks is opened and closed by calling the Open and Close methods; however, when this setting is enabled, the Remote Connector establishes a persistent connection to QuickBooks even when Open and Close are not used. This allows multiple applications to share the connection and simultaneously access the Remote Connector.
• Idle Timeout: Sets the idle timeout for the persistent connection in seconds. This is only applicable to the persistent connection. If there is no activity within this time window the Remote Connector closes the connection.

Enabling TLS/SSL

The following options are used to configure TLS/SSL:

• Enable TLS: Enables or disables TLS (1.x) communication.
• Select Certificate: Loads an existing certificate.
• Generate Certificate: Creates a new certificate.

In addition to the UI, the Remote Connector has a command-line interface that makes it easy to deploy on machines where a user is not always logged in, for example, a Web server. To facilitate deployment to these environments, the Remote Connector contains two executables:

The syntax for managing the Remote Connector Windows service from the command line is as follows:

The following commands are available:

You can start the MySQL and TDS daemons from the command line, as shown below: The following command-line options are available:

All configuration data is read from the registry at "HKEY_LOCAL_MACHINE\SOFTWARE\RemoteConnector". Each user will have a separate subkey with user-specific settings. For instance "HKEY_LOCAL_MACHINE\SOFTWARE\RemoteConnector\User1".

Application-Level Settings

User-Level Settings

The CData Data Sync App models entities in the QuickBooks API as tables, views, and stored procedures. There are three parts to the Data Model: Tables, Views, and Stored Procedures. These are defined in schema files, which are simple, text-based configuration files.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

The CData Data Sync App models the data in tables so that it can be easily queried and updated.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

The Data Sync App exposes tables for data sources that support both retrieving and updating data. Generally querying QuickBooks tables is the same as querying a table in a relational database; in the following sections you can find QuickBooks-specific information on querying the tables; for example, any columns required in the WHERE clause and fields required to insert.

CData Data Sync App - QuickBooks Tables

Create, update, delete, and query QuickBooks Accounts. To update Accounts, set the QBXMLVersion to 6.0 or higher.

Table Specific Information

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Accounts are Id, Name, Type, IsActive, and TimeModified. TimeModified may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. Name may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

Insert

To add an Account, specify the Name and Type fields.

Columns

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query QuickBooks Bill Expense Line Items.

Table Specific Information

Bills may be inserted, queried, or updated via the Bills, BillExpenseItems, or BillLineItems tables. Bills may be deleted by using the Bills table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Bills are Id, Date, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId, IsPaid, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

Insert

You can also use Bills and BillExpenseItems to insert a Bill.

To add a Bill, specify a Vendor, Date, and at least one Expense or Line Item. All columns denoted with a # are Expense Line Item columns and can be used for inserting multiple expense Line Items for a new Bill transaction. For example, the following will insert a new Bill with two Expense Line Items:

Columns

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query QuickBooks Bill Line Items.

Table Specific Information

Bills may be inserted, queried, or updated via the Bills, BillExpenseItems, or BillLineItems tables. Bills may be deleted by using the Bills table.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Bills are Id, Date, ReferenceNumber, VendorName, VendorId, AccountsPayable, AccountsPayableId, IsPaid, and TimeModified. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

Insert

You can also use Bills and BillExpenseItems to insert a Bill.

To add a Bill, specify a Vendor, Date, and at least one Expense or Line Item. All columns denoted with a # are Line Item columns and can be used for inserting multiple Line Items for a new Bill transaction. For example, the following will insert a new Bill with two Line Items:

Columns