Connecting an On Premise SQL Based Datasource

Randall Berg -

Looking to add an On Premise SQL database as a datasource in BrightGauge?  Great, you're in the right place.  Here's some steps to get that setup. Below are generic steps to help you get setup, but if you'd like a step by step guide for your datasource, click on the link for it below:

 

1. ConnectWise Manage

2. TigerPaw

3. ConnectWise Automate (LabTech)

4. Kaseya

5. N-Able Reporting Manager

6. QuickBooks On Premise

7. Generic SQL Server (MS-SQL)

8. Generic mySQL

9. Generic PostgreSQL

 

Setting Up the BrightGauge Agent

If you are connecting with BrightGauge using an on-premise 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.

Step 1 - Choose Host Machine For Agent

(You can have just ONE agent for all datasources, if they are on 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.

Step 2 - 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.

Step 3 - Install Agent

You can download the agent by going to https://YOURDOMAIN.brightgauge.co . If this is the first time connecting a datasource, you'll see the available options immediately, otherwise head to Account Settings -> Datasources (Click on the down arrow next to your name at the top right). Select the appropriate datasource and at the top, you'll see a link to download the agent as well as the IP addresses for our servers (if you want to limit the firewall exception created in step 2). To install on Windows Server 2008 and later (Windows Vista and later for desktops), make sure to run the install as an administrator:

Right click on the installer -> run as administrator
OR
Right click on the installer -> properties -> compatibility -> run as administrator

 

Step 5 - Configure 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 -> 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.

Once all information is filled out on the gent, 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?

 

Adding a user in the SQL datasource

So now that you have the agent installed and connected, we now need to create a user within your database for us to use when running queries.  Please follow the directions below for your database.

1. MS-SQL (ConnectWise, TigerPaw, Kaseya, N-Able Reporting Manager)

2. mySQL (LabTech)

MS-SQL (ConnectWise, TigerPaw, Kaseya, N-Able Reporting Manager)

1. Open Microsoft SQL Server Management Studio.

2. Right click the Logins folder under the Security folder, then click ‘New Login…’.

steps1-21.jpg

3. Check 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. Uncheck the ‘Enforce password policy’ option (if unable to leave unchecked, just uncheck the requirement of ‘changing password in next log in’).

5. Fill in Login Name and password.

steps3-5.jpg

6. Select the ‘User Mapping’ page in the left panel.

7. PERMISSIONS - Click 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.  Video here - http://goo.gl/P3xeP

8. Click OK, and you’re done.

steps6-8.jpg

mySQL (LabTech)

1. Open SQLyog.

2. Click on the User Manager button in the top navigation.
steps1-2_mysql.jpg

3. Click on the Add New User button.

step3_mysql.jpg

4. Fill in the Username & Password, and press the Create button.

step4_mysql.jpg

5. Check the SELECT privilege and then press the Save Changes button. You’re done.

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
 
 
 
 
 
 
Once you've saved your datasource in BrightGauge, you're all done!
 
If you come across any issues at any point throughout this process, please do not hesitate to email your implementation manager or our support desk support@brightgauge.com
Have more questions? Submit a request

Comments

Powered by Zendesk