Establishing a Connection
The provider connects to Sage 50 UK data through the SData REST API included in the Sage 50 UK installation. SData allows access to local company datasets as well as datasets on network drives.
After Configuring the Sage SData Service, set the following connection properties.
Authenticating to Sage 50 UK
The User and Password properties, under the Connection section, must be set to valid Sage 50 UK user credentials. These values are the same values used to log in to the Sage 50 UK software. To authenticate with HTTP digest to the SData service, set AuthScheme.
Connecting to a Company
In addition to the authentication values, the URL property, under the Connection section, should be set to the address of the company dataset desired. To obtain the address, do the following:
- If you have not already done so, open the Sage 50 UK software.
- Select Tools > Internet Options .
- Select the Sdatasettings tab.
- Click Details next to the Sage 50 software application you want to connect to. A window opens containing a list of company names along with the addresses to their corresponding datasets.
- Set the URL property to the value in the address field next to the company desired.
Note: If the dataset you want to connect to is not displayed, the permissions on the Sage 50 UK folder location may not be correct. If you are connecting to a dataset on a networked drive, ensure:
- You are using UNC paths to the folder on the machine you are using as your SData provider.
- You set the SData service login rights to a user who has full rights over the network share or map drive.
Configuring the Sage SData Service
Starting the SData Service
The Lyftrondata provider for Sage 50 UK connects to Sage 50 UK via the Sage SData service (which is Sage's Web toolkit for connecting to Sage instances) that is built into the Sage 50 UK software. SData allows for remote access to Sage software applications. By default, Sage UK 2015 instances will have SData turned on and ready for use.
You can follow the steps below to verify that the SData service is started.
- If you have not already done so, open Sage 50.
- Navigate to Tools > Internet Options. The Internet Options window is displayed.
- Select the Sdatasettings tab. A list is displayed of Sage software applications that are currently available.
- To turn the SData service on for the application, select the On option.
- If the SData Service Status does not read "SData is currently running", click the Advanced button.
- In the dialog that is displayed, specify the Port Number desired when making the connection and click the Restart button.
- If the Windows Firewall button is enabled, click this button to unblock the port. If you have any additional firewalls on the machine, ensure that they are configured to allow connections to be made on the specified port number.
Once you apply any changes, you can then establish a connection to your Sage 50 UK software.
Enabling HTTPS Support
The Sage SData service provides secure and encrypted connections via HTTPS. Data confidentiality and the authenticity of the server are provided by digital certificates. If you do not have a certificate, use IIS to generate a self-signed certificate.
You can follow the steps below to configure the SData service to use a certificate; the provider will validate this certificate against the system trust store by default. If you generated a self-signed certificate, you can add the certificate to this certificate store or set SSLServerCert.
Fulfilling the Certificate Requirements
The certificate has the following requirements:
- The certificate must have a full valid trust chain.
- The common name (CN) for the certificate must match the machine/domain name where the SData service is running. To ensure that the CN is correct, generate the self-signed certificate on the machine where Sage 50 SData is running.
- The certificate must be added to the personal My certificate store for the Local Machine account.
Configuring the SData Service for TLS/SSL
You can then configure the SData service to use the certificate:
- Navigate to C:\Program Files (x86)\Common Files\Sage SData and open Sage.SData.Service.Config.UI.exe.
- Click the Advanced button. The SData Configuration window is displayed.
- Select the Enable HTTPS Access option and select the port desired.
- If you have any firewalls on your machine, make sure the ports specified are not blocked.
- Click the button next to the Certificate box.
- In the resulting dialog, select the certificate.
If you select a certificate and do not see the certificate name populated in the Certificate textbox, this is most likely due to missing extended properties within the certificate. The extended properties include thumbprint, thumbprint algorithm, key usage, and enhanced key usage.
Use IIS to avoid this issue: IIS automatically populates these fields when generating a self-signed certificate.
- Click OK to restart the server.
- Verify that the Enable HTTPS option is selected in the SData configuration window.
If the SData configuration window is closed and reopened but the Enable HTTPS option is not enabled, this is most likely caused by the Sage.SData.Service.exe.config file not being updated properly. Follow the steps below to use the alternate configuration file below.
You will need the certificate thumbprint. Note that the thumbprint data includes spaces. The thumbprint data can be obtained using Windows services. You can also access the thumbprint in the SData configuration window:
- If you have not already done so, open the Sage.SData.Service.Config.UI.exe application and open the advanced settings.
- Click the button next to the Certificate box.
- In the Windows security dialog, click "Click here to view certificate properties". The Certificate Details window is displayed.
- On the Details tab, copy the value in the Thumbprint field.
Customizing the SSL Configuration
By default, the provider attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats to do so.
Connecting Through a Firewall or Proxy
To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.
In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
Set the following properties:
- To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
- To tunnel the connection, set FirewallType to TUNNEL.
- To authenticate, specify FirewallUser and FirewallPassword.
- To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.
Troubleshooting the Connection
To show provider activity from query execution to network traffic, use Logfile and Verbosity. The examples of common connection errors below show how to use these properties to get more context. Contact the support team for help tracing the source of an error or circumventing a performance issue.
- Authentication errors: Typically, recording a Logfile at Verbosity 4 is necessary to get full details on an authentication error.
- Queries time out: A server that takes too long to respond will exceed the provider's client-side timeout. Often, setting the Timeout property to a higher value will avoid a connection error. Another option is to disable the timeout by setting the property to 0. Setting Verbosity to 2 will show where the time is being spent.
- The certificate presented by the server cannot be validated: This error indicates that the provider cannot validate the server's certificate through the chain of trust. If you are using a self-signed certificate, there is only one certificate in the chain.
To resolve this error, you must verify yourself that the certificate can be trusted and specify to the provider that you trust the certificate. One way you can specify that you trust a certificate is to add the certificate to the trusted system store; another is to set SSLServerCert.