How to securely connect your Acctivate database to the outside world

Secure_Connection_300x180

I am often asked if it’s possible to securely access Acctivate data so that customers can access their account information (like orders, invoices, etc.) from a website. The short answer is YES! And it’s easier than you might think.

There are many approaches to HOW and WHERE to use the customer information. If you have an ecommerce site already, then integrating the information in your current platform makes sense. Or you may have another software, like a CRM, where you want to include customer information. Your developer can do this for you using our Developing With Acctivate guide. If you don’t have something already in place that you can use, website or n ecommerce site, then you might be interested in our Customer Portal for Acctivate that acts as a customer friendly portal into the customer’s account information.

It doesn’t matter which approach you choose, the first step is setting up Acctivate to allow the remote connection securely.

This guide will step you through what is needed to prepare your Acctivate database for integration.

Do you already have an ecommerce site built using WooCommerce or Magento? We have ‘ready to use’ add-ons for these ecommerce platforms that will sync product data or allow customers to view their orders and invoices from the Acctivate system. Contact us at info@excelss.com or 940-566-2420 for more information.

Step 1: Create an Access Rule on your router or firewall

What is an Access Rule? Inbound Access Rules (which is what we will discuss in this article) control inbound Internet traffic from the public internet to your local network. Both routers and firewalls use access rules to verify that the source address is permitted to send traffic to your local network. You want to make sure only the authorized traffic is allowed to access your network. So you need to find the IP address of the server that will be connecting to Acctivate.

For security reasons, this should only be done with dedicated or vpn servers; NOT shared hosting accounts! If you are needing to connect a shared hosting to your Acctivate, see the section below titled ‘Using the Acctivate API’ (link this text to that section)

  • Set up a firewall rule to allow connections from the IP address you want to connect from. The steps to do this will depend on your Firewall. Contact your systems administrator or follow the instructions provided by your firewall or router for adding access rules.   
  • Forward port 1433 (or any port) to the internal IP address of the server where the Acctivate MSSQL Server is installed.
  • NOTE: Use an alternate port can if you have multiple SQL Server instances; make sure to adjust the remaining instructions to use the proper port.
  • Confirm that you can connect to the port from the server you will be using.

Step 2: Set up MSSQL Server

Configure MSSQL Server to accept remote connections

  • Run SQL Server Configuration Manager.
  • Go to SQL Server Network Configuration > Protocols for ACCTIVATE (if your MSSQL instance has been renamed, it may not be ACCTIVATE. Check with your systems administrator).
  • Make sure TCP/IP is enabled.
  • 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.)

Step 3: Create a READ ONLY database user

For additional security, you will need to create a user in your MS SQL Server that has READ ONLY permissions.

  • Using either Microsoft SQL Server Management Studio (which does not come pre-installed with Acctivate) or the command line, create a new database user.
  • Assign the user to the database for the current Acctivate company file.
  • Allow the user to only Read, not Write to your database.

The three queries below should do the trick for you:

CREATE LOGIN <loginname> WITH PASSWORD = <securepassword>;

CREATE USER <username> FOR LOGIN <loginname>;

GRANT SELECT ON SCHEMA::dbo TO <loginname> WITH GRANT OPTION;

Helpful Tip: The loginname and username do not have to be different.

Step 4: Set up Unix or Linux servers to access Microsoft SQL Server

If you are integrating Acctivate with an application running on a Linux or Unix server, you need to install and configure FreeTDS. FreeTDS is a set of libraries that allow your application on Unix or Linux to natively talk to Microsoft SQL Server.

Using FreeTDS allows you to keep the connection information for your SQL Server secure. FreeTDS is installed using the root user of your server. The root user is also the only user allowed to add, maintain, or even see the IP address of your Microsoft SQL Server. Your application will use an alias for the connections. This keeps your Microsoft SQL Server IP address ‘hidden’ from anyone other than the server root user. Your server system administrator can install and configure FreeTDS for you.

Most shared hosting companies do not allow libraries to be installed as a root user. In this case, you will need to use our Acctivate API, which allows you to connect to Acctivate from anywhere.

Step 5: Write your integration

Once you have configured the connection between your Microsoft SQL Server and the remote server, you are ready to begin writing the integration. How you approach this step will depend on the programming language used by your application. We have resources available to help you access Microsoft SQL Server using PHP.

If your programming language doesn’t have a good driver for MSSQL, you can also use our Acctivate Connector API, which gives you access to most Acctivate data through a RESTful API. Contact us for more information.

Optional Solution: Acctivate Connector API

Our Acctivate Connector API is a quicker way to integrate Acctivate with almost any other application. The Connector also allows you to bypass needing a dedicated/VPN web server. The Acctivate Connector API is a RESTful API that allows you to make requests to the various data endpoints and receive response data in json format.

When you use the Acctivate Connector API, we will also assist you with the configuration of your Acctivate server upon request.

Are you interested in integrating Acctivate with your website or application? Contact us at info@excelss.com or 940-566-2420 for more information on how we can help you reach your goals.

Lance Thames