Installing Fidesic Vendor Sync

Preparation
  1. User Log out and GP shutdown NOT required for this module.
  2. Fidesic Vendor Sync Module can be installed on any machine with GP Database Access. The module will run as a service on this machine and sync any changes made to the vendor lists, or chart of accounts within GP Automatically.
  3. Implementation must be done as a WINDOWS ADMINISTRATOR during all steps. Whenever possible to “run application as administrator”, please do so to avoid any issues.
  4. Instructions are as if the module is installed on the SQL server host. Installation can be done on a different machine with network access, just make sure use the correct machine name or IP of the machine hosting the SQL database
  5. Download Fidesic Sync Module here. 
Installation
  1. Uninstall all previous version using standard windows Control Panel “Programs and Features”
  2. Open “Fidesic Vendor Sync Service.exe” using the windows “Run as Administrator” option.  
  3. Follow prompts for standard installation.
  4. Service should Auto-Open upon completion.
  5. Default install location for manual open:  C:\Program Files (x86)\Fidesic\Fidesic Service Monitor.exe
Configuration
  • Fidesic Vendor Sync runs in the task bar as a Green Dot. Double clicking will open configuration window.
  • Select Setup:Configure

mceclip4.png

GP Database Setup
  1. Connection: Create New
  2. Server: if installed on SQL server host machine, use “localhost”. If not, use local IP address or Name of the machine hosting SQL Server
  3. Username: your ‘sa’ user name
  4. Password: ‘sa’ password
  5. Catalog: Select your “GP Company Database”mceclip0.png
Fidesic Authentication

In this window, the "Username" is the API key, and the "Password" is the API password. Click here to learn how to get this information from Fidesic.

mceclip2.png

If you provide the proper login info and receive a "cannot authenticate" message, make sure that the machine you're on is able to ping api.fidesic.com and that our software has writes to log files to the server. 

Advanced Settings
  • First Sync: Will be auto-selected after install, and auto-deselected after initial sync. This check all vendor records instead of “new” vendor records
  • Sync Chart of Accounts: When selected the sync service will also upload GL lists, and vendor based GL defaults
  • Sync Temporary Vendors: When selected this option sync temporary vendors. Default is not to sync temporary vendors
  • Vendor Classes to Exclude: Enter any vendor classes you would like to exclude from the sync process.
  • Log Errors to File: This will create a log file in the c:\ folder on the local machine. This file will contain all connection attempts, and any errors encountered. Do not enable permanently, it is intended for debugging purposes only. This setting will create one log file per day, which will gradually take up space on your local harddrive. 

mceclip3.png

Setting up Non SA SQL  user for connecting our vendor sync to your company Database

 As an alternative to using the 'sa' login, you can create a SQL role specific to our Vendor sync modules requirements.

 

  • Run the attached script to Create our tables, create a security role, and set permissions for that user. 
    • FIDESYNC_TABLE_AND_ROLE.sql (Note: This needs to be run against each COMPANY database using sync service)
  • Create Login in Sql Management Studio- 
    • screen_shot_2020-03-05_at_1.48.20_pm.png 
  • Set User Name, Password, and Set the Default Database. 
    • screen_shot_2020-03-05_at_1.49.04_pm.png
  • User Mapping Settings will need the "FIDESYNC_ROLE" role that was creating using the previous script. 
    • Make sure to select any company databases that will need access to this service.  
    • Select the "FIDESYNC_ROLE" as the database role membership. 
    • screen_shot_2020-03-05_at_1.49.24_pm.png

 

Table References: 
  • 'Select' Access to the following tables (select access preferred to all GP company tables for future compatibility):  
    • "PM00200"
    • "PM00300"
    • "SY01200"
    • "GL00100"
    • "GL00105"
    • "PM00203"
    • "SY03300"
  • 'Select', 'Insert', 'Update', 'Delete', 'Alter' Access to the following table:
    • "FIDESYNC" -  To Be Created on First Connect or manually.  

 

 

FIDESYNC_TABLE_AND_ROLE.sql Code Block

/*
*    FIDESYNC Manual Installation script.
*
*    This script will create the necessary table (FIDESYNC) for our sync service to run.
*    It will create a new database ROLE that can be used specifically for our service.
*    It will grant SELECT permissions to this role for all GP Company tables and views.
*    It will grant SELECT, INSERT, DELETE, AND ALTER permissions to this role for the FIDESYNC table
*
*    Instructions:    Run this script against each COMPANY database.
*                    Add a database Login that the service will use.
*                    Add the Login as a user on each company database that you want sync to run on
*                    Assign the FIDESYNC_ROLE to the Login

*/
/****** CREATE FIDESYNC SYNC TABLE ******/
IF OBJECT_ID(N'dbo.FIDESYNC', N'U') IS NULL 
BEGIN
    CREATE TABLE FIDESYNC( 
    ID INT IDENTITY PRIMARY KEY, 
    TableName NVARCHAR(50) NOT NULL, 
    TableKey NVARCHAR(50) NOT NULL, 
    LAST_SYNC DATETIME NOT NULL,
    FAILED_ATTEMPT DATETIME,
    FAILED_COUNT INT NOT NULL DEFAULT 0) 
    PRINT 'Added FIDESYNC table'
END
ELSE
    PRINT 'FIDESYNC table already exists'
GO
/****** ADD MISSING FIDESYNC COLUMNS ******/
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID(N'[dbo].[FIDESYNC]') AND name = 'FAILED_ATTEMPT') 
    ALTER TABLE dbo.FIDESYNC ADD FAILED_ATTEMPT DATETIME 
IF NOT EXISTS(SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID(N'[dbo].[FIDESYNC]') AND name = 'FAILED_COUNT') 
    ALTER TABLE dbo.FIDESYNC ADD FAILED_COUNT INT NOT NULL DEFAULT 0
GO
/****** CREATE FIDESYNC ROLE ******/
IF DATABASE_PRINCIPAL_ID('FIDESYNC_ROLE') IS NULL
BEGIN
    CREATE ROLE [FIDESYNC_ROLE]
    PRINT 'Created FIDESYNC_ROLE Role'
END
GO
/****** GRANT FIDESYNC ROLE PERMISSION TO VIEW GP COMPANY TABLES AND VIEWS ******/
declare @cStatement varchar(255)
declare G_cursor CURSOR for select 'grant select on [' + convert(varchar(128),name) + '] to FIDESYNC_ROLE' from sysobjects 
    where (type = 'U' or type = 'V') and uid = 1
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement 
WHILE (@@FETCH_STATUS <> -1)
begin
    EXEC (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement 
end
DEALLOCATE G_cursor
PRINT 'Added SELECT permission to the FIDESYNC_ROLE role for all Company Tables and Views'
GO
/****** GRANT FIDESYNC ROLE PERMISSION TO VIEW GP COMPANY TABLES ******/
grant select, insert, update, delete, alter on [FIDESYNC] to FIDESYNC_ROLE
PRINT 'Added SELECT, INSERT, DELETE, ALTER permission to the FIDESYNC_ROLE role for the FIDESYNC table'
PRINT ''
PRINT ''
PRINT 'Add a database Login that the service will use.'
PRINT 'Add the Login as a user on each company database that you want sync to run on'
PRINT 'Assign the FIDESYNC_ROLE to the Login'