How to Update to the New Version of Fidesic Vendor Sync for GP

Overview

This guide is for companies that already have Fidesic Vendor Sync installed and are looking to update to the latest version. This new version has greater reliability, reporting and new features to make the sync the best version yet.

Steps:

Uninstall the Old Version
Install the new version as SA
Validate Configuration Settings
ONLY REQUIRED IF YOU HAVE A Custom SQL Login Setups, modify the SQL Role to access new tables.

Uninstalling the Old Version

To run this update you will need to uninstall the old version of vendor sync, which will require locating the machine in your server environment where vendor sync is currently installed. This module is typically installed on either the SQL server itself, or the application server. The file you are looking for is located in this directory of the local machine: C:\Program Files (x86)\Fidesic\Fidesic Service Monitor.exe.

Once you've located the proper machine you can use the standard windows Add and Remove Programs function to uninstall the Fidesic Vendor Sync.

Note: Make sure to uninstall the Vendor Sync Module and NOT The Fidesic for GP Module.

Installing this old version will not remove your configuration files, making setup after the new installation a bit easier.

Preparation for the New Module

User Log out and GP shutdown NOT required for this module.
You should install the vendor sync module in the same location as the previous version to minimize setup.
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.
Download The Latest Fidesic Sync Module here.

Installation

Open “Fidesic Vendor Sync Service.exe” you downloaded using the windows “Run as Administrator” option.
Follow prompts for standard installation.
Service should Auto-Open upon completion.
Default install location for manual open: C:\Program Files\Fidesic\Fidesic Service Monitor.exe if you need to manually open it.

Verify Configuration

Fidesic Vendor Sync runs in the task bar as a Green Dot. Double clicking will open configuration window.
Select Setup:Configure

Validate that you Vendor Sync Settings did come over from the previous version by validating a valid GP database connection and Fidesic Authentication connection.

If all settings have come across (which is the expected behavior), you're all set. Just make sure the service has been started.

ONLY REQUIRED FOR CUSTOM SQL ROLE SETUPS: modifying the SQL Role to access new tables.

Using a dedicated SQL login instead of sa Download FIDESYNC_TABLE_AND_ROLE.sql and run it against your DYNAMICS database and against every company database Vendor Sync connects to. The script is safe to run more than once. Run it again whenever you:

install or update Fidesic Vendor Sync
upgrade Dynamics GP
add a new company to sync

To have the script add your service login to FIDESYNC_ROLE for you, set @LoginName at the top. Otherwise, add the login as a user in each database and assign it FIDESYNC_ROLE yourself.

User Mapping Settings will need the "FIDESYNC_ROLE" role that was creating using the previous script.

Make sure to select DYNAMICS and any company databases that will need access to this service.
Select the "FIDESYNC_ROLE" as the database role membership.

Run the following script against the DYNAMICS database and any added company databases.

FIDESYNC_TABLE_AND_ROLE.sql Code Block

/*
*    FIDESYNC Installation / Update script  (Fidesic Vendor Sync for GP 2.2+)
*
*    Safe to run more than once. Use it for a new install, after updating Vendor Sync,
*    after a Dynamics GP upgrade, and whenever you add a company to sync.
*
*    What it does:
*      System database (DYNAMICS):  creates FIDESYNC_ROLE and grants SELECT on the
*                                   GP version (DB_Upgrade) and company list (SY01500) tables.
*      Each company database:       creates or updates the FIDESYNC tracking table,
*                                   creates FIDESYNC_ROLE, grants SELECT on the dbo schema
*                                   (every GP table and view, including ones added later),
*                                   and grants read/write on FIDESYNC.
*
*    Instructions:
*      1. Create a SQL Server login for the service (Security > Logins), if you have not already.
*      2. Optional: set @LoginName below to that login. The script then adds the login as a user
*         in this database and assigns it to FIDESYNC_ROLE. Leave it blank to do that by hand.
*      3. Run this script against the DYNAMICS database and against EVERY company database
*         that Vendor Sync connects to.
*/

DECLARE @LoginName sysname = N'';   -- e.g. N'fidesic_sync'

SET NOCOUNT ON;

/****** CREATE FIDESYNC ROLE ******/
IF DATABASE_PRINCIPAL_ID('FIDESYNC_ROLE') IS NULL
BEGIN
    CREATE ROLE [FIDESYNC_ROLE];
    PRINT 'Created FIDESYNC_ROLE role';
END

/****** OPTIONAL: ADD THE SERVICE LOGIN TO THIS DATABASE AND THE ROLE ******/
IF LEN(@LoginName) > 0
BEGIN
    IF SUSER_ID(@LoginName) IS NULL
    BEGIN
        RAISERROR('Login %s does not exist on this server. Create it first, then rerun.', 16, 1, @LoginName);
        RETURN;
    END

    DECLARE @sql nvarchar(max);
    -- The login may already be mapped to this database under a different user name.
    DECLARE @UserName sysname = (SELECT name FROM sys.database_principals WHERE sid = SUSER_SID(@LoginName));

    IF @UserName IS NULL
    BEGIN
        SET @UserName = @LoginName;
        SET @sql = N'CREATE USER ' + QUOTENAME(@UserName) + N' FOR LOGIN ' + QUOTENAME(@LoginName);
        EXEC sp_executesql @sql;
        PRINT 'Added user ' + @UserName + ' to ' + DB_NAME();
    END

    IF IS_ROLEMEMBER('FIDESYNC_ROLE', @UserName) = 0
    BEGIN
        SET @sql = N'ALTER ROLE [FIDESYNC_ROLE] ADD MEMBER ' + QUOTENAME(@UserName);
        EXEC sp_executesql @sql;
        PRINT 'Added ' + @UserName + ' to FIDESYNC_ROLE';
    END
END

/****** SYSTEM DATABASE: GP version and company name lookups ******/
-- SY01500 (company master) exists only in the GP system database.
IF OBJECT_ID(N'dbo.SY01500', N'U') IS NOT NULL
BEGIN
    GRANT SELECT ON dbo.SY01500 TO [FIDESYNC_ROLE];
    IF OBJECT_ID(N'dbo.DB_Upgrade', N'U') IS NOT NULL
        GRANT SELECT ON dbo.DB_Upgrade TO [FIDESYNC_ROLE];
    PRINT 'Granted SELECT on DB_Upgrade and SY01500 to FIDESYNC_ROLE';
    PRINT 'Finished system database ' + DB_NAME() + '. Now run this script against each company database.';
    RETURN;
END

/****** COMPANY DATABASE: FIDESYNC TRACKING TABLE ******/
IF OBJECT_ID(N'dbo.FIDESYNC', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.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 'Created FIDESYNC table';
END
ELSE
    PRINT 'FIDESYNC table already exists';

IF COL_LENGTH(N'dbo.FIDESYNC', N'FAILED_ATTEMPT') IS NULL
    ALTER TABLE dbo.FIDESYNC ADD FAILED_ATTEMPT DATETIME;
IF COL_LENGTH(N'dbo.FIDESYNC', N'FAILED_COUNT') IS NULL
    ALTER TABLE dbo.FIDESYNC ADD FAILED_COUNT INT NOT NULL DEFAULT 0;

/****** COMPANY DATABASE: PERMISSIONS ******/
-- A schema-level grant covers every current GP table and view, and also tables created later
-- (a GP upgrade rebuilds tables, and Analytical Accounting can be installed later). Grants on
-- individual tables are lost when a table is rebuilt.
GRANT SELECT ON SCHEMA::dbo TO [FIDESYNC_ROLE];
PRINT 'Granted SELECT on all GP tables and views (dbo schema) to FIDESYNC_ROLE';

GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON dbo.FIDESYNC TO [FIDESYNC_ROLE];
PRINT 'Granted SELECT, INSERT, UPDATE, DELETE, ALTER on FIDESYNC to FIDESYNC_ROLE';

PRINT '';
PRINT 'Finished company database ' + DB_NAME() + '.';
IF LEN(@LoginName) = 0
    PRINT 'Next: add the service login as a user in this database and assign it to FIDESYNC_ROLE.';