Connecting an On Premises SQL Based Datasource

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:

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.

Firewall Rules

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 support@brightgauge.com 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:

 

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:

  1. Open Microsoft SQL Server Management Studio.
  2. Right-click the Logins folder under the Security folder and select New Login….

    steps1-21.jpg

  3. 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).
  4. De-select the Enforce password policy option. If unable to leave unchecked, de-select the requirement of changing password in next log in.
  5. Enter the Login Name and Password.

    steps3-5.jpg

  6. Click User Mapping in the left panel.
  7. 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. 
  8. Click OK.

    steps6-8.jpg

 

MySQL (ConnectWise Automate)

To add a MySQL user:

  1. Open SQLyog.
  2. Click on the User Manager button in the top navigation.

    steps1-2_mysql.jpg

  3. Click Add New User.

    step3_mysql.jpg

  4. Enter the Username and Password.

    step4_mysql.jpg

  5. Click Create.
  6. Select the Select privilege and click Save Changes.

    step5_mysql.jpg

Connecting Your Datasource to BrightGauge

Now you can continue on with onboarding within the web application.  Click on your datasource:

select datasource

And here's some help with the information you'll need with you when you go through onboarding:

  1. Connection Name
  2. External IP address for the BrightGauge Agent
  3. Port Opened through firewall
  4. 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)
  5. Database name
  6. UserName you created in the database
  7. Password you created for that user name

Here's the screenshot so you can see where each piece of data is needed:

add_new_datasource.png

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.

Was this article helpful?
1 out of 1 found this helpful

Comments

0 comments