Tableau Connector for Sage 50 UK

Build 24.0.9060

Configuring a Connection

After Installing the Connector you can connect and create a Data Source for data in Sage 50 UK.

Setting Up a Data Source

Complete the following steps to connect to the data:

  1. Under Connect | To a Server, click More....
  2. Select the data source called Sage 50 UK by CData.
  3. Enter the information required for the connection.
  4. Click Sign In.
  5. If necessary, select a Database and Schema to discover what tables and views are available.

Using the Connection Builder

The connector makes the most common connection properties available directly in Tableau. However, it can be difficult to use if you need to use more advanced settings or need to troubleshoot connection issues. The connector includes a separate connection builder that allows you to create and test connections outside of Tableau.

There are two ways to access the connection builder:

  • On Windows, use a shortcut called Connection Builder in the Start menu, under the CData Tableau Connector for Sage 50 UK folder.
  • You can also start the connection builder by going to the driver install directory and running the .jar file in the lib directory.

In the connection builder, you can set values for connection properties and click Test Connection to validate that they work. You can also use the Copy to Clipboard button to save the connection string. This connection string can be given to the Connection String option included in the connector connection window in Tableau.

Before You Connect

For v28 and later, you must install the Sdata adapter prior to using Sage 50 UK driver. See the Sage 50 UK documentation for more information.

Connecting to Sage 50 UK

The connector 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, connect with the below steps, the URL property should be set to the address of the company dataset desired. To obtain the address, do the following:

  1. If you have not already done so, open the Sage 50 UK software.
  2. Select Tools > Internet Options .
  3. Select the Sdatasettings tab.
  4. 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.
  5. Set the URL property to the value in the address field next to the company desired.

Authenticating to Sage 50 UK

The User and Password properties 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 to Digest. Otherwise, Basic AuthScheme will be used.

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.

Starting the SData Service

The CData Tableau Connector 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.

  1. If you have not already done so, open Sage 50.
  2. Navigate to Tools > Internet Options. The Internet Options window is displayed.
  3. Select the Sdatasettings tab. A list is displayed of Sage software applications that are currently available.
  4. To turn the SData service on for the application, select the On option.
  5. If the SData Service Status does not read "SData is currently running", click the Advanced button.
  6. In the dialog that is displayed, specify the Port Number desired when making the connection and click the Restart button.
  7. 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 connector 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.

See Advanced Features for more information on configuring the connector for HTTPS.

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:

  1. Navigate to C:\Program Files (x86)\Common Files\Sage SData and open Sage.SData.Service.Config.UI.exe.
  2. Click the Advanced button. The SData Configuration window is displayed.
  3. Select the Enable HTTPS Access option and select the port desired.
  4. If you have any firewalls on your machine, make sure the ports specified are not blocked.
  5. Click the button next to the Certificate box.
  6. 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.

  7. Click OK to restart the server.
  8. Verify that the Enable HTTPS option is selected in the SData configuration window.

Troubleshooting

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:

  1. If you have not already done so, open the Sage.SData.Service.Config.UI.exe application and open the advanced settings.
  2. Click the button next to the Certificate box.
  3. In the Windows security dialog, click "Click here to view certificate properties". The Certificate Details window is displayed.
  4. On the Details tab, copy the value in the Thumbprint field.
Use this value in the CertificateLookupValue setting in the configuration file. For example: 
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
<section name="Sage.SData.Service.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<section name="Sage.Integration.Server.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<section name="Sage.Common.Syndication.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</sectionGroup>
</configSections>
<applicationSettings>
<Sage.SData.Service.Properties.Settings>
<setting name="DigestTimeout" serializeAs="String">
<value>12000000000</value>
</setting>
<setting name="EnableBasicAuthentication" serializeAs="String">
<value>True</value>
</setting>
<setting name="WebAppPath" serializeAs="String">
<value />
</setting>
<setting name="EnableSSL" serializeAs="String">
<value>True</value>
</setting>
<setting name="Port" serializeAs="String">
<value>443</value>
</setting>
</Sage.SData.Service.Properties.Settings>
<Sage.Integration.Server.Properties.Settings>
<setting name="EnableBroadcast" serializeAs="String">
<value>False</value>
</setting>
</Sage.Integration.Server.Properties.Settings>
<Sage.Common.Syndication.Properties.Settings>
<setting name="IPAddress" serializeAs="String">
<value />
</setting>
<setting name="Server" serializeAs="String">
<value>sdata</value>
</setting>
<setting name="EnableSSLPort" serializeAs="String">
<value>True</value>
</setting>
<setting name="Port" serializeAs="String">
<value>5493</value>
</setting>
<setting name="SettingsProviderType" serializeAs="String">
<value>Sage.Common.Syndication.ConfigurationSyndicationSettings, Sage.Common.Syndication</value>
</setting>
<setting name="PathPrefix" serializeAs="String">
<value />
</setting>
<setting name="DoNotUseRegistry" serializeAs="String">
<value>False</value>
</setting>
<setting name="EnableStandardPort" serializeAs="String">
<value>True</value>
</setting>
<setting name="SSLPort" serializeAs="String">
<value>5494</value>
</setting>
<setting name="CertificateLookupValue" serializeAs="String">
<value>ENTER YOUR CERTIFICATE THUMBPRINT HERE</value>
</setting>
<setting name="CertificateLookupType" serializeAs="String">
<value>Thumbprint</value>
</setting>
</Sage.Common.Syndication.Properties.Settings>
</applicationSettings>
</configuration>

Next Step

See Using the Connector to create data visualizations.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060