Use this article to install the ToolWatch Integration Connector and connect it to your Spectrum database. First, you install the connector, give it access to your Spectrum database, and check its network connection. Then you send your connection details to ToolWatch, and our team confirms that everything is working. If needed, you can also change the connector region and set up a cost code view.
Install the ToolWatch Integration Connector
Download the .MSI file from secure.toolwatch.com/g/connector-install.
Run the .MSI file.
After the install is complete, open a web browser and go to http://localhost:5280. A Windows Service now runs in the background.
Record the Connector ID shown on the page.
Grant the Connector Access to the Spectrum Database
In Microsoft SQL Management Studio, expand the Security menu.
Right-click Logins and select New Login.
Enter a login name, such as TWConnectorUser.
Select SQL Server authentication.
Enter a password of your choice.
Clear the Enforce password policy checkbox.
Make sure the Default database is your Spectrum database.
Go to the User Mapping page.
Make sure TWConnectorUser and your Spectrum database are selected.
Grant the user these permissions: db_datareader and public.
In your Spectrum database, expand the Security menu.
Right-click Users and select New User.
Enter the login name you created in step 3 as both the user name and the login name.
Go to the Membership page.
Grant the database user this role: db_datareader.
Change the Connector Region (If Needed)
Depending on your ToolWatch region, you may have to change what server the ToolWatch Integration Connector communicates with. By default the Integration Connector communicates with the East region. ToolWatch will inform you during integration setup if this needs to be changed. To change this region on the Windows machine with the connector:
Open a web browser and go to http://localhost:5280/.
Confirm that you see a version number on this page. If you do not, download and install the latest version of the connector.
To the right of API URL, click edit.
On the page that loads, enter the API URL for your region from the Region API URLs list below.
As instructed on the confirmation page, restart the Windows Service.
Refresh the page to see that the change has taken effect.
Region API URLs
Check the Network Connection
Networking rules where the ToolWatch Connector is installed might block traffic to these API endpoints.
On the computer where the ToolWatch Connector is installed, open a web browser.
Go to the API URL for your region.
Make sure the text "ToolWatch Connector API" appears.
To run more connection tests, go to the connector page at http://localhost:5280/.
Click Check connection.
Note: The Check connection link runs a series of connection tests for the current API endpoint. If the first test is successful, you will see "Success!". Otherwise, you will see the response code and error message for the connection request, the DNS name resolution for the API endpoint, and whether you were able to connect to the API endpoint through a TCP connection on port 443. You can run as many connection checks as needed. If the connection check fails, contact ToolWatch Support.
Send Your Connection Details to ToolWatch
Please let us know when the connector has been installed and we will have our team check to confirm everything is working properly. When you reply, please be ready to provide the following information, some of which will be derived from following the install instructions:
Connector ID
Database address or server name
Database name
Database login name
Database login password
Default Company ID(s)
If you would prefer not to send this information via email, then please call our support team at 720-856-3441 and they will be happy to capture the information over the phone.
Set Up the Cost Code View
The Use Cost Code View setting allows the integration to filter cost codes before sending them to ToolWatch. This can be used to filter to cost types or filter out Spectrum phase codes by number or description. Once this setting is enabled both the mapping and the sync will only access the Spectrum phases thru an SQL view. To function properly the setting requires a view be created on the Spectrum SQL database as follows:
The view needs to be created on the hosting Spectrum SQL database in the dbo schema.
The view needs to be named exactly: ToolWatchCostCodeView
The ToolWatchCostCodeView can not use parameters as no parameters will be passed.
The ToolWatchCostCodeView has to return the expected fields by these exact names: Id, Company_Code, Job_Number, Phase_Code, Cost_Type, Phase_Description, Status_Code, Cost_Type_Description, GL_Account, Detail_Flag, JobId, Division
Any and all filters need to be in the WHERE clause or a result of any JOINS added to the view.
The ToolWatchCostCodeView should filter by Spectrum company code.
When ToolWatch selects from tables it starts with this SELECT statement:
SELECT p.Id,
p.Company_Code,
p.Job_Number,
p.Phase_Code,
p.Cost_Type,
p.Description AS Phase_Description,
p.Status_Code,
c.Description AS Cost_Type_Description,
c.GL_Account,
c.Detail_Flag,
j.id AS JobId,
j.Division
FROM JC_PHASE_MASTER_MC p
JOIN JC_COST_TYPE_MASTER_MC c
ON p.Company_Code = c.Company_Code
AND p.Cost_Type = c.Cost_Type
JOIN JC_JOB_MASTER_MC j
ON p.Job_Number = j.Job_Number
AND p.Company_Code = j.Company_Code
WHERE p.Company_Code = (the company code for the sync) its a string
The view should be written and implemented by a SQL professional. ToolWatch is not responsible for maintaining the view as it is on the customer's database. We will help as much as we can to get the view coded and working.
Sample View:
CREATE VIEW ToolWatchCostCodeView AS
SELECT p.Id,
p.Company_Code,
p.Job_Number,
p.Phase_Code,
p.Cost_Type,
p.Description AS Phase_Description,
p.Status_Code,
c.Description AS Cost_Type_Description,
c.GL_Account,
c.Detail_Flag,
j.id AS JobId,
j.Division
FROM JC_PHASE_MASTER_MC p
JOIN JC_COST_TYPE_MASTER_MC c
ON p.Company_Code = c.Company_Code
AND p.Cost_Type = c.Cost_Type
JOIN JC_JOB_MASTER_MC j
ON p.Job_Number = j.Job_Number
AND p.Company_Code = j.Company_Code
WHERE p.Company_Code = 'abc'
Q: What is the ToolWatch Connector?
A: The ToolWatch Connector is a Windows Service that allows the ToolWatch Cloud to integrate with on-premise systems.
Q: Where should this connector be installed?
A: For Vista, Spectrum, and CMiC integration customers, the connector can be installed on any host that has network access to the database or the APIs exposed by the application server. This can be the database server itself, the application server, or a completely separate host.
For Sage 100 Contractor or Sage 300 CRE integrations, the connector must be installed on a host that has filesystem access to the application database. This can be either via the local filesystem, or via a network share.
Q: What are the network requirements?
A: For all integrations the host must be able to make outbound HTTPS calls to:
api-connector.toolwatch.net
api-connector.east.toolwatch.net
api-connector.central.toolwatch.net
api-connector.west.toolwatch.net
If you need to whitelist these destinations, you must whitelist the domain, and not the IP address, as the IP address will change frequently as our cloud platform elastically scales up and down.
No inbound network access is required.
Q: Are there any requirements for the host?
A: The host only needs to be running Windows with a recent version of the .NET framework. There are no other external requirements such as SQL Management Studio. CPU, memory, and disk requirements are negligible.
Q: How much load will this put on our production system?
A: The load caused by the integration should be negligible relative to a normal workload, but as with all things appropriate testing is advised. A simple approach would be to initially try the integration after hours for load testing. Using a ToolWatch sandbox account would further allow for testing different configurations without worrying about affecting production data.
Q: How do you uninstall the ToolWatch Connector?
A: Use the Windows Add/Remove Programs in Windows Settings. The connector will be listed as "ToolWatch Connector" and can be uninstalled using the remove option.