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
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.
Right-click on the downloaded certificate in Windows Explorer (rds-ca-2019-root.crt) and choose Install Certificate
Choose Local Machine and click Next
Click Yes if prompted by User Access Control
Choose Place all certificates in the following store then choose the Browse button and select the Trusted Root Certification Authorities
Click Finish on the last page of the wizard|
Click Yes on the prompts you receive (there may be up to five of these)
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:
Open up SQL Server Management Studio
Choose Connect Object Explorer and make sure the Options section is open on the bottom.
Connect to Server Dialog Entry Settings:
Make sure that Server Type is 'Database Engine'
Enter your Server Name as supplied by Anterra
Choose SQL Server Authentication
Enter your Login and Password as supplied
Make sure Encryption is set to Mandatory
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:
Enter the connection details on the
Connect to Server
dialog as you normally would and make sure the Options section is open.
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 ...
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, ...
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 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 ...
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 ...