Accessing Anterra Data Warehouse (ADW) to sync Sage 300 CRE data securely and access in Power BI or SQL Server Management Studio (SSMS)

Accessing Anterra Data Warehouse (ADW) to sync Sage 300 CRE data securely and access in Power BI or SQL Server Management Studio (SSMS)

Prerequisites

If using SQL Server Management Studio, we recommend you have the latest version of the software from Microsoft before beginning this guide.  It is possible to have multiple versions of SSMS installed at the same time.  You can download the latest version for free from here

The latest version of Power BI Desktop can be downloaded here from Microsoft.

Install the AWS Certificate Authority

To establish a secure connection between your site and the ADW SQL Server database hosted in Amazon Web Services (AWS) you must have the latest certification authority (CA) public certificate installed and trusted. The steps below provide a step-by-step walk-through of this procedure.
  1. Download the AWS public certificate.
  2. Right-click on the downloaded certificate in Windows Explorer (rds-ca-2019-root.crt) and choose Install Certificate

    Windows context menu showing 'Install Certificate'
  3. Choose Local Machine and click Next
    Certificate Import Wizard where you should select 'Local Machine' and click Next
  4. Click Yes if prompted by User Access Control

  5. Choose Place all certificates in the following store then choose the Browse button and select the Trusted Root Certification Authorities
  6. Click Finish  on the last page of the wizard|
    Certificate Import Wizard showing last page where 'Finish' button should be clicked
  7. Click Yes on the prompts you receive (there may be up to five of these)
  8. Click OK on the final confirmation that the certificate was installed
The certificate will be installed, and you're now ready to connect securely to your Anterra Data Warehouse from Power BI or SQL Server Management Studio.

Connecting Securely using SQL Server Management Studio:

  1. Open up SQL Server Management Studio
  2. Choose Connect Object Explorer and make sure the Options section is open on the bottom.
  3. Connect to Server Dialog Entry Settings:
    1. Make sure that Server Type is 'Database Engine'
    2. Enter your Server Name as supplied by Anterra
    3. Choose SQL Server Authentication
    4. Enter your Login and Password as supplied
    5. Make sure Encryption is set to Mandatory
    6. Uncheck the 'Trust server certificate' box.
 You should now be able to connect to  the SQL Server if all settings are correct.

Connecting SQL Server Management Studio without installing a certificate

The installation of the certificate establishes trust between the two systems that are connecting.  Without the certificate the communication is still secure however it's not possible for your system to confirm the system it is connecting to.  To allow the connection to proceed in SQL Server Management Studio (SSMS) use the following steps:
  1. Enter the connection details on the  Connect to Server  dialog as you normally would and make sure the Options section is open.                    


  2. Connect to Server Dialog Entry Settings:
    1. Same settings as before in Server section
    2. Make sure that Encryption is set to Mandatory
    3. Check the 'Trust server certificate' box
You can now use SQL Server Management Studio as with any other SQL Server database.  Microsoft has many quickstart guides and tuturials if you are unfamiliar with using SSMS or need to brush up on some skills:
https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server

    • Related Articles

    • Set up SQL Data connections for Sage Crystal Reports

      Overview Sage 300 CRE uses Crystal Reports to generate formatted reports. To connect Crystal Reports, and thus the Sage report designs to your data which ADC has extracted into SQL Server, you will need to: Create a 32-bit ODBC connection called a ...

    • Troubleshooting Problems Data Sync to AnterraBI

      Some of the common reasons for the anterraDataCenter sync to stop running are as follows: The local or active directory account that is used by Windows Task Scheduler to run the data update has had its password changed. Your ERP system (Sage, ...

    • Setting up Viewpoint Access via VPN

      Introduction Synchronizing data to Anterra from Viewpoint is extremely fast and easy via a Virtual Private Network (VPN) connection and can be done from either on-premise or ViewpointOne. To provide the information needed to start the process of ...

    • Anterra WIP Management

      Anterra Work in Process Management & Reporting In depth review on why to use WIP with some common mistakes made during first use. Overview Anterra has comprehensive WIP Management and WIP Reporting in the Construction BI module. Typical menu access ...

    • 'Timberline Version Changed' appears when anterraDataCenter opens

      Background When anterraDataCenter opens, a dialog box with the title "Timberline Version Changed" appears Each time anterraDataCenter opens, a message appears that has the following text in it: "Timberline version has recently been updated. SQL ...