This document describes how to add an on-premises SQL database as a datasource in BrightGauge. If you'd like a step-by-step guide for your datasource, click on the link for it below:
- ConnectWise Automate On Premises (Formerly Known as LabTech)
- ConnectWise Manage On Premises
- ECI e-Automate On Premises
- Kaseya On Premises
- MS-SQL Express
- N-Able Reporting Manager
- Quickbooks On-Prem
- SQL Server (MS-SQL)
Set Up the BrightGauge Agent
If you are connecting with BrightGauge using an on premises datasource, you will need to download the BrightGauge agent and install on your network in order to provide connectivity. The agent runs as a service and only passes the queries needed by BrightGauge to your systems, and encrypts the raw data that it sends back. It can be installed on any Windows server or workstation that has access to the databases you'd like to connect to BrightGauge.
Choose Host Machine For Agent
Note: You can have just ONE agent for all datasources, if they are on the same internal network.
Select a machine (windows server or workstation) that has access to the database servers with your databases installed. This machine is where you install the BrightGauge Agent and where BrightGauge securely connects in from the Internet.
Of course, you can also install the agent on the database servers, as long we are able to connect from our External IP address.
NOTE: The BrightGauge Agent uses .NET 4, you can download and install that ahead of time here.
NOTE: You may run into Certificate conflicts on 2003 r2 servers. If you install on a non-web enabled 2k8 server and install went clean.
You will need to open up your firewall to allow communication from BrightGauge through a specific port to the machine you identified above. We recommend Port 2666. You can find the BrightGauge IP Addresses when connecting the datasource in your BrightGauge portal right next to the link to download the agent.
Otherwise, please email us at firstname.lastname@example.org and we will provide you with our IP Addresses.
We have also seen that even when the Windows Firewall is turned off, it is blocking traffic. Please create an advanced port exception in the firewall on the machine you choose to install the agent on. Instructions on that can be found here.
Install the Agent
For instructions on installing the BrightGauge agent, please refer to Install the BrightGauge Agent.
Configure the Agent
If running on Windows Server 2008 and later (Windows Vista and later for desktops), make sure to run the BrightGauge Manager as an administrator:
Right-click on the Manager desktop shortcut and select Properties > Compatibility > Run as administrator.
You'll need your BrightGauge portal, username, and password, all of which were emailed to you when you first signed up. Local IP is either the IP of the box behind a NAT, or if your box is exposed to the internet it is the public IP of that machine again.
After all information is filled out on the agent, click on Test Connection.
If your test is successful, proceed to click on Save & Start Service.
If you're having issues connecting or have questions, here are a few documents that can help:
- Common Agent Connectivity Issues
- Windows Firewall Inbound Rule for the Agent
- Multiple Databases, how many agents are needed?
Add a User in the SQL Datasource
Now that you have the agent installed and connected, we need to create a user within your database for us to use when running queries. Please follow the directions below for your database.
- MS-SQL (ConnectWise Manage, TigerPaw, Kaseya, N-Able Reporting Manager)
- MySQL (ConnectWise Automate)
MS-SQL (ConnectWise Manage, TigerPaw, Kaseya, N-Able Reporting Manager)
To add an MS-SQL user:
- Open Microsoft SQL Server Management Studio.
- Right-click the Logins folder under the Security folder and select New Login….
- Select the SQL Server authentication radio button. (Windows Authenticated users cannot be supported. Due to the nature of our agent, Windows Authenticated users work sometimes, at best. SQL Server Authenticated users are the only way we can guarantee a correct login to the database).
- De-select the Enforce password policy option. If unable to leave unchecked, de-select the requirement of changing password in next log in.
- Enter the Login Name and Password.
- Click User Mapping in the left panel.
- Select the checkbox next to each database the agent needs access to, ensuring that the ‘db_datareader’ checkbox is selected below for each database. For Connectwise Datasource users wanting to use TIME ENTRY information for reporting - please allow for additional SQL permissions to use stored functions.
- Click OK.
MySQL (ConnectWise Automate)
To add a MySQL user:
- Open SQLyog.
- Click on the User Manager button in the top navigation.
- Click Add New User.
- Enter the Username and Password.
- Click Create.
- Select the Select privilege and click Save Changes.
Connecting Your Datasource to BrightGauge
Now you can continue on with onboarding within the web application. Click on your datasource:
And here's some help with the information you'll need with you when you go through onboarding:
- Connection Name
- External IP address for the BrightGauge Agent
- Port Opened through firewall
- Database IP Address or Machine Name (if using multiple SQL instances, use the IP or machine name followed by a back slash and then the instance name: 192.168.1.100\instance or SQLServer\instance)
- Database name
- UserName you created in the database
- Password you created for that user name
Here's the screenshot so you can see where each piece of data is needed:
After you save your datasource in BrightGauge, you're all done!
If you have any questions, please contact Support by selecting Help > Open a Ticket from the top menu bar.