Skip to main content

Authentication

Connecting to Cloud Versions of Foundation​

The below provides an overview of how to connect to cloud or hosted versions of Foundation. It takes about 15-30 minutes to complete.

Step 1: Create Agave User in Foundation​

The first step is to create a dedicated User to connect to Agave Sync. We recommend using a dedicated User for the integration, so you can track activity from your integration with your PM system.

To create a dedicated User:

  1. From the Foundation main menu, under Tools and System, select Security:

Company Tools
  1. Press the + sign, then Foundation Software LLC --> Companies --> Users. Right click the Users folder and select Add user...:

Company Tools
  1. Add a User ID named "Agave", select your Foundation Company, and make sure this user has ODBC Access enabled. Then press Save.
caution

Make sure to use "Agave" for the User ID since this is a requirement from Foundation.

danger

Your password must meet the following criteria:

  • Be at least 8 characters long
  • Contain at least one letter, one number, and one special character (e.g. !, @, #, $, etc.)
  • Not be part of an online data leak
    • We use Have I Been Pwned to securely check your password, without sending us your actual password to their servers. See here for more information.

Company Tools
  1. Double-click on Companies, and then press your Company:

Company Tools
  1. Press the Users tab. Right click in the User column and select Add:

Company Tools
  1. Enter the "Agave" User, and then press Close. Press Yes when prompted to save your changes.

Company Tools

Step 2: Email the Foundation Team Requesting Permissions to Import Data​

If you plan to sync data to Foundation for the following data records, you will need to request Foundation add the necessary permissions for your Agave User:

Data RecordFoundation Tables or Stored Procedures
AR Invoicesar_invoice, sp_GetNextNumber
AR Invoice (GL) Line Itemsar_invoice_gl
Customerssp_insert_update_customers
Employeessp_insert_update_employees
Equipment Timecard Entriespending_eq_usage
Job Cost Period Quantitiespending_job_quantity
Projects (Jobs)sp_insert_update_jobs
Purchase Orders, Subcontracts, and Subcontract Change Orderspending_po_sync_header
Purchase Orders, Subcontracts, and Subcontract Change Order Line Itemspending_po_sync_detail
Timecard Entriespending_timecards

Afterwards, you can email Tom Steinke at Foundation (tbs@foundationsoft.com) the below. Note, this is a crucial step as getting these permissions allows us to write data to these tables. We also recommend CCing your Agave Account Manager on the email so they can follow-up if necessary.

Subject: [Your Company Name] - Whitelist Tables/Stored Procedures

Hi Tom - hope you're well. Would you be able to whitelist the following Foundation Database and User to allow writing data to the following tables/stored procedures?

  1. Database: [Input your database name. Note this will start with "Cas_"]
  2. User: Agave
  3. Tables/Stored Procedures:
    1. ar_invoice
    2. ar_invoice_gl
    3. sp_insert_update_jobs
    4. sp_insert_update_customers
    5. sp_insert_update_employees
    6. sp_GetNextNumber
    7. pending_timecards
    8. pending_po_sync_header
    9. pending_po_sync_detail
    10. pending_eq_usage
    11. pending_job_quantity
How do I determine my database name?​

To determine your Foundation database name:

  1. From anywhere in Foundation, select File--> Change Database:

Company Tools
  1. A list of your database names will appear:

Company Tools

Step 3: Notify Agave to Make the Connection​

Notify your Agave Account Manager that you are ready to connect Foundation to your PM System, and provide them with the following information:

  • Hostname: this is the hostname of the Foundation database. For hosted Foundation instances, input "sql.foundationsoft.com".
  • Port: this is the port that enables Agave to communicate with the Foundation Database. Note, this should always default to 9000.
  • Database: this is the name of the Foundation database.
  • Username: the username must be for a registered Foundation User.
  • Password: this is the password for the registered Foundation User.

Connecting to On-prem Versions of Foundation​

The below provides an overview of how to connect to on-premise installations of Foundation. It takes about 15-30 minutes to complete, unless you need to upgrade to more recent versions of Microsoft Server and Microsoft SQL Server Management Studio which might take a few hours to complete.

Step 1: Confirm you are on the Correct Versions of Microsoft Server and Microsoft SQL Server Management Studio​

Agave Sync supports the following versions of Windows Server and Microsoft SQL Server Management Studio:

  • Windows 10
  • Windows 11
  • Windows Server 2016
  • Windows Server 2019
  • Windows Server 2022

Agave follows Microsoft's Product Lifecycle policy for Windows.

caution

As of October 2023, Windows 8.1 and Windows Server 2012 have reached end of support and do not receive security updates. If you are using either version, we recommend upgrading immediately. Agave does not support these versions of Windows.

For more information, see related Microsoft announcement.

Step 2: Whitelist Agave's Static IP Addresses​

You will need to request your IT team whitelist Agave's static IPs both at the firewall and SQL Server level:

  • 13.58.14.240
  • 18.219.160.98
  • 3.143.37.24

This allows our servers to communicate with the Foundation database hosted in your network.

note

If you are unable to authenticate with Agave Sync even after adding the above static IP addresses, we recommend following our guide on troubleshooting common issues with on-premise connections.

If your firewall also restricts outbound traffic, you will need to whitelist the following domains on port 443 (HTTPS/WSS) to allow real-time communication with Agave Sync:

DomainPurpose
rest.ably.ioREST API requests
realtime.ably.ioRealtime WebSocket connections
a.ably-realtime.comFallback host
b.ably-realtime.comFallback host
c.ably-realtime.comFallback host
d.ably-realtime.comFallback host
e.ably-realtime.comFallback host
internet-up.ably-realtime.comConnectivity check
ws-up.ably-realtime.comWebSocket connectivity check
tip

For forward compatibility, we also recommend whitelisting the following CNAME targets that these domains resolve to:

  • main.realtime.ably.net
  • main.a.fallback.ably-realtime.com
  • main.b.fallback.ably-realtime.com
  • main.c.fallback.ably-realtime.com
  • main.d.fallback.ably-realtime.com
  • main.e.fallback.ably-realtime.com
note

Ably does not publish static IP addresses, as their service uses dynamically assigned IPs. Domain-based whitelisting is the recommended approach.

Step 3: Create Agave User in Foundation​

The next step is to create a dedicated User to connect to Agave Sync. We recommend using a dedicated User for the integration, so you can track activity from your integration with your PM system.

To create a dedicated User:

  1. From the Foundation main menu, under Tools and System, select Security:

Company Tools
  1. Press the + sign, then Foundation Software LLC --> Companies --> Users. Right click the Users folder and select Add user...:

Company Tools
  1. Add a User ID named "Agave", select your Foundation Company, and make sure this user has ODBC Access enabled. Then press Save.
caution

Make sure to use "Agave" for the User ID since this is a requirement from Foundation.

danger

Your password must meet the following criteria:

  • Be at least 8 characters long
  • Contain at least one letter, one number, and one special character (e.g. !, @, #, $, etc.)
  • Not be part of an online data leak
    • We use Have I Been Pwned to securely check your password, without sending us your actual password to their servers. See here for more information.

Company Tools
  1. Double-click on Companies, and then press your Company:

Company Tools
  1. Press the Users tab. Right click in the User column and select Add:

Company Tools
  1. Enter the "Agave" User, and then press Close. Press Yes when prompted to save your changes.

Company Tools

Step 4: Confirm the "Agave" User has "db_datareader" and "db_datawriter" Permissions​

  1. Open Microsoft SQL Server Management Studio and connect to your Foundation Database:

Company Tools
  1. Go to Security --> Users. Right click the relevant User (i.e. "Agave") and select Properties:

Company Tools
  1. Go to Membership (or User Mapping) and confirm they have the following database role membership for the database you are using:
    • db_datareader
    • db_datawriter
caution

Make sure that the options for db_denydatareader and db_denydatawriter are unchecked as well.

Company Tools

Step 5: Grant Write Permissions for the "Agave" User​

If you intend to write data back to Foundation for the following data records, you will need to add CREATE and UPDATE permissions for the following tables:

Data RecordFoundation Tables
AR Invoicesar_invoice
AR Invoice (GL) Line Itemsar_invoice_gl
AR Cash Receiptspending_ar_cash
Equipment Timecard Entriespending_eq_usage
Job Cost Period Quantitiespending_job_quantity
Purchase Orders, Subcontracts, and Subcontract Change Orderspending_po_sync_header
Purchase Orders, Subcontracts, and Subcontract Change Order Line Itemspending_po_sync_item_detail
Timecard Entriespending_timecards

To grant permissions to these tables:

  1. Open the Tables directory, and scroll down to the relevant table (e.g. pending_po_sync_header):

Company Tools
  1. Right click on the relevant table, and select Properties:

Company Tools
  1. Select Permissions and then Search:

Company Tools
  1. Type in "Agave" in the text box, and press Browse:

Company Tools
  1. Select the "Agave" User and press OK. Then press OK again:

Company Tools
  1. Select the "Agave" User, and then select the Grant checkbox for the Insert and Update permissions. Then press OK:

Company Tools

Repeat the above step for each table you need access to.

Step 6: Grant Stored Procedure Permissions to the "Agave" User​

If you intend to write data back to Foundation for the following data records, you will need to add EXECUTE permissions for the following stored procedures:

Data RecordFoundation Stored Procedures
AR Invoicessp_GetNextNumber
Customerssp_insert_update_customers
Employeessp_insert_update_employees
Projects (Jobs)sp_insert_update_jobs

To grant permissions to these stored procedures

  1. Open the Programmability directory, expand Stored Procedures, and scroll down to the relevant stored procedure (e.g. sp_GetNextNumber):

Company Tools
  1. Right click on the relevant stored procedure, and select Properties:

Company Tools
  1. Select Permissions and then Search:

Company Tools
  1. Type in "Agave" in the text box, and press Browse:

Company Tools
  1. Select the "Agave" User and press OK. Then press OK again:

Company Tools
  1. Select the "Agave" User, and then select the Grant checkbox for the Execute permission. Then press OK:

Company Tools

Step 7: Notify Agave to Make the Connection​

Notify your Agave Account Manager that you are ready to connect Foundation to your PM System, and provide them with the following information:

  • Hostname: this is the hostname of the Foundation database. For hosted Foundation instances, input "sql.foundationsoft.com".
  • Port: this is the port that enables Agave to communicate with the Foundation Database. Note, this should always default to 9000.
  • Database: this is the name of the Foundation database.
  • Username: the username must be for a registered Foundation User.
  • Password: this is the password for the registered Foundation User.