How to set up remote connection to the ACCTivate database

You will be provided with the IP address for our API connection.
This is the ONLY IP address that should be allowed through your firewall for our purposes. We very strongly advise against allowing all IP addresses.

Set up your firewall

Set up your firewall to accept incoming requests and forward them to your MSSQL Server

  • Get the IP address for the API Connector.  You can find this information in your client portal or from the Excel Support Staff
  • Set up port 1433 to allow connections from the API connector’s IP address.  
  • Forward port 1433 to the internal IP address of the server where your Acctivate SQL Server is installed.
  • NOTE: An alternate port can be used if you have multiple SQL Server instances or want to use an uncommon port; adjust the remaining instructions to use the proper port and inform us of which port to use.

Set up your SQL Server

Set up your MSSQL Server to accept remote connections

  • Run SQL Server Configuration Manager.
  • Go to SQL Server Network Configuration > Protocols for ACCTIVATE (or your instance name if it’s not ACCTIVATE).
  • Make sure TCP/IP is enabled. o Restart SQL Server service.
  • Add a Windows Firewall exception on the server for TCP ports 1433 and 1434 on the local subnet.
If this doesn't work . . .

  • Right-click on TCP/IP and select Properties.
  • Verify that, under IP2, the IP Address is set to the computer’s IP address on the local subnet.
  • Scroll down to IPAll.
  • Make sure that TCP Dynamic Ports is blank. (Default is usually some 5-digit port number.)
  • Make sure that TCP Port is set to 1433. (Default is usually blank.)

Create a database User

Please create a user with READ ONLY access unless you are integrating with Acctivate Business Activities.

  • Using either Microsoft SQL Server Management Studio (which does not come pre-installed with Acctivate) or the command line, create a new user for the API connector.
  • Assign the user to the database for the current Acctivate company file.
  • Give the user READ ONLY access unless you need to integrate with Acctivate Business Activities.  Business Activity integration requires the ability to create, modify, and delete records.

Update Your Account

Sign into your account on our client portal and provide us with the following information:

  • Public IP address or hostname
  • Port to use for connection
  • Database name for your ACCTivate company file
  • SQL Server user for the above database. 
  • Password for the SQL Server user.

Troubleshooting

If you are experiencing problems, try one of these suggestions:

Check TCP/IP Properties

  • Run SQL Server Configuration Manager.
  • Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.
  • Make sure TCP/IP is enabled.
  • Right-click on TCP/IP and select Properties.
  • Verify that, under IP2, the IP Address is set to the computer’s IP address on the local subnet.
  • Scroll down to IPAll.
  • Make sure that TCP Dynamic Ports is blank. (It may be set to a 5-digit port number.)
  • Make sure that TCP Port is set to 1433. (It may be blank.)

Check for other MSSQL Server Instances

If the Acctivate MSSQL Server instance is not the only instance, it might be necessary to UNASSIGN port 1433 on other instances or assign this instance to a different port.  Otherwise, the router will only direct traffic to the default instance.

Start the SQL Server Browser service

Some users have found that it helps to start the SQL Server Browser service. We’ve not run into a situation that required this, but if all else fails, it might be a good next thing to try!

Was this article helpful?

Related Articles