Web Services for Microsoft Dynamics GP Fall 2020 Release Upgrade Error

Microsoft Dynamics GPWe've just upgraded another client to Microsoft Dynamics GP Fall 2020 Release; everything during the upgrade went smoothly until we came to upgrading the web services. The test environment was created by replicating the live servers into a sandbox environment.

I was pretty much expecting to encounter the problem we did as we were moving from using a SQL Server security store to an Active Directory security store. The error received was:

Security object error upgrading Web Services

A loader exception has occurred.
Loader Errors:
- Microsoft.Dynamics.Security.InvalidSecurityContextException: Microsoft.Dynamics.Security.NonExistentSecurityObjectException : The security object does not exist.  Key = 25cc1a21-2cc4-4b13-a1c8-eea186fb688a
   at Microsoft.Dynamics.Security.ConcreteValidator.ValidateContext(SecurityContext context)
   at Microsoft.Dynamics.Security.SecurityService.Get(SecurityContext context, Key key)
   at Microsoft.Dynamics.Security.AzManTaskServiceImplementation.GetTask(SecurityContext context, TaskKey taskKey)
   at Microsoft.Dynamics.GP.GPSecurityMetadataSystemLoader.PersistTaskForAction(Action action, Task task, Keyword keyword)
   at Microsoft.Dynamics.GP.GPSecurityMetadataSystemLoader.PerformActionOnKeywords(Action action)
   at Microsoft.Dynamics.InstallData.Loader.Process(String[] args)

As the error referred to a security object error, my thinking immediately went to the new security store as being the issue. Due to the change in method I opted to remove the web services and do a reinstall; I tried using the built-in removal process in the Web Services Configuration Wizard, but this took a very long time to run and then crashed, so I used a couple of Microsoft supplied scripts to remove all of the web services objects from the company databases first and then from the system.

Once the web services had been completely removed, I re-ran the Configuration Wizard and deployed the web services again. The deployment ran through without any issues and we were able to test web services without encountering any further issues.

The lesson, which confirmed what I expected, is that if you change the security store type, you need to remove the web services from all companies and do a redeploy.

Web Services for Microsoft Dynamics GP: How to Setup an Active Directory Security Store

Microsoft Dynamics GPTo use the Web Services for Microsoft Dynamics GP with TLS 1.2 you need to do two things:

  1. Upgrade to the Fall 2020 Release; you can't just apply the hotfix to an October 2019 or earlier release, but have to use the full download media.
  2. Create an Active Directory Security Store.

Upgrading Dynamics GP is not a problem, but Active Directory isn't my area of expertise. Fortunately, Microsoft have a set of instructions on the Dynamics GP Support and Services Blog which takes you through the creation of the security store.

Once the security store has been created, you need three pieces of information for the install of the web services:

  1. Name of the domain controller.
  2. Port 389.
  3. The partition string entered when creating the security store.

Recent ISC Software Webinar: Controls & Security in Microsoft Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at Controls & Security in Microsoft Dynamics GP. In this webinar, we covered how you can improve controls and security in Microsoft Dynamics GP using a mix of standard ad third party functionality. If you want to catch up on this, or any other, webinar, you can do so here.

In this blog post, I am going to recap the webinar and cover the salient points:

  1. Introduction
  2. Posting Controls
  3. Workflow Approvals
  4. Automation
  5. Security Setup
  6. Field Level Security
  7. Report Security
  8. Single Sign-on
  9. Audit
  10. Conclusion

Introduction ^

As always in this type of webinar, I try to use standard functionality as far as possible, but this is not always possible as not all of the areas being covered are included as standard. This was a deliberate design decision from the very creation of Microsoft Dynamics GP where they decided that supporting third parties would allow many more developers to work on modules for the system and encourage competition and improved standards from those third parties (known as Independent Software Vendors or ISVs for short).

There are three key areas where I am recommending third party modules and I will try to be clear when I am doing this.

Continue reading "Recent ISC Software Webinar: Controls & Security in Microsoft Dynamics GP"

Simple Audit for Microsoft Dynamics GP: Conclusion

Microsoft Dynamics GPThis post is part of a series on creating a simple audit for Microsoft Dynamics GP.

As I've shown over the last few posts, it is relatively easy to create a simple audit on a table in Dynamics GP, but this approach is not vert scalable and requires someone to manually create the SQL triggers. For a client on a shoestring budget and who wanted to audit one table, this approach sufficed.

However, for a larger client who would want to audit more tables in more than one company and who would want to have non-technical users maintaining the audited information, I would recommend a solution like Assure Suite from Fastpath which I have implemented with a umber of clients previously.

What brought this approach to mind was a client had an issue with some incorrect data and we could not determine who or what was changing some data, so I amended this custom audit for the tables we needed to record for that issue. In this instance, there was no need for a full audit solution as it will only be in place temporarily while investigating a specific issue.

Simple Audit for Microsoft Dynamics GP: SQL View for Reporting

Microsoft Dynamics GPThis post is part of a series on creating a simple audit for Microsoft Dynamics GP.

Once the audit table and triggers have been deployed, any changes made through the audit will be recorded and available for reviewing later to see who has been making changes and, more significantly, what was changed.

The easiest way of making this available to the client was to create a SmartList for them using SmartList Designer to select data from the new custom audit table. SmartList Designer can see either Dexterity tables or SQL views, but not custom SQL tables, I created a SQL view on the custom audit table, joining it to the Users Master (SY01400) table in the DYNAMICS table to get the username:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE VIEW uv_AZRCRV_Audit AS SELECT ['Audit'].RecordType AS 'Record Type' ,['Audit'].RecordID AS 'Record ID' ,['Audit'].UpdateType AS 'Update Type' ,['Audit'].USERID AS 'User ID' ,['User Master'].Username AS 'Username' ,FORMAT(['Audit'].ChangeDateTime, 'yyyy-MM-dd') AS 'Change Date' ,FORMAT(['Audit'].ChangeDateTime, 'HH:ss') AS 'Change Time' ,['Audit'].OldData AS 'Old Data' ,['Audit'].NewData AS 'New Data' FROM ut_AZRCRV_Audit ['Audit'] LEFT JOIN DYNAMICS..SY01400 AS ['User Master'] ON ['User Master'].USERID = ['Audit'].USERID GO GRANT SELECT ON uv_AZRCRV_Audit TO DYNGRP GO

Simple Audit for Microsoft Dynamics GP: Create Triggers

Microsoft Dynamics GPThis post is part of a series on creating a simple audit for Microsoft Dynamics GP.

With the table created to store the audited information, the second step is to create the required triggers on the Address Electronic Funds Transfer Master (SY06000) table. For an Vendor EFT audit there are three triggers required:

  1. INSERT
  2. UPDATE
  3. DELETE

These triggers will record all new Vendor EFT information added as well as that which is amended or deleted. The client for which this audit was created only dealt with vendors in the UK and only ever set three fields in the EFT Bank window:

  1. Bank Name
  2. EFT Bank Code
  3. EFT Bank Account

Additional fields can be added to the audit if other fields need to be stored.

As the Address Electronic Funds Transfer Master (SY06000) table holds EFT Bank information for customers as well as vendors, the Record ID has been set to include the Series. Strictly speaking, this was not necessary as the client did not store bank details for their customers in Dynamics GP as none were making direct debit payments.

The audited data is being trimmed and cast as varchar so the extra whitespace held by Dynamics GP due to the columns being chars are removed.

The first trigger creates the trigger which runs when data is inserted:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE TRIGGER utr_AZRCRV_SY06000_AuditInsert ON SY06000 AFTER INSERT AS INSERT INTO ut_AZRCRV_Audit --VALUES SELECT 'Vendor EFT' ,CAST(i.SERIES AS VARCHAR(100)) + ' | ' + CAST(RTRIM(i.CustomerVendor_ID) AS VARCHAR(100)) + ' | ' + CAST(RTRIM(i.ADRSCODE) AS VARCHAR(100)) ,'Insert' ,SYSTEM_USER ,GETDATE_USER() ,'' ,'BANKNAME = ' + CAST(RTRIM(i.BANKNAME) AS VARCHAR(100)) + ' | ' + 'EFTBankCode = ' + CAST(RTRIM(i.EFTBankCode) AS VARCHAR(100)) + ' | ' + 'EFTBankAcct = ' + CAST(RTRIM(i.EFTBankAcct) AS VARCHAR(100)) FROM inserted i GO

Continue reading "Simple Audit for Microsoft Dynamics GP: Create Triggers"

Simple Audit for Microsoft Dynamics GP: Create Table

Microsoft Dynamics GPThis post is part of a series on creating a simple audit for Microsoft Dynamics GP.

The first step in creating the audit is to create a table in the company database to hold the audited information. From the user point of view there was five pieces of information required:

  1. Key for the vendor EFT being amended
  2. User ID
  3. Date/Time
  4. Old Data
  5. New Data

To make reporting easier and to add an element of future proofing, I also added two other pieces of information:

  1. Record Type to record the type of information being audit; in this case Vendor EFT.
  2. UpdateType to explicitly record whether the change was an INSERT, UPDATE or DELETE

The following SQL will create a table with the table with the seven columns mentioned above:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE TABLE ut_AZRCRV_Audit ( RecordType VARCHAR(100) ,RecordID VARCHAR(100) ,UpdateType VARCHAR(10) ,USERID VARCHAR(150) ,ChangeDateTime DATETIME ,OldData NVARCHAR(MAX) ,NewData NVARCHAR(MAX) ) GO

Upcoming Microsoft Dynamics GP Webinars from ISC Software

ISC Software SolutionsEvery month at ISC Software I present a webinar on Microsoft Dynamics GP and related products. We typically have the next three upcoming monthly webinars I'll be delivering scheduled.

We run these webinars on a monthly basis, with occasional extra webinars added to the schedule so it is worth checking the Webinar Schedule page every so often.

The upcoming webinars are:

Controls and Security in Microsoft Dynamics GP
In March is Controls and Security in Microsoft Dynamics GP; see how to improve controls and security in Dynamics GP using a mix of standard and third party functionality..

Tue, March 16th, 2021 4:00 PM - 4:45 PM BST

Register Here

Powerful Document Generation for Dynamics GP
In April is Powerful Document Generation for Dynamics GP; watch in this webinar with a live dox42 demo and we will show you how to design attractive document templates in MS Office and integrate data from MS Dynamics GP and many other data sources such as Microsoft 365, SharePoint or SAP.

Tue, April 20th, 2021 4:00 PM - 4:45 PM BST

Register Here

Controls and Security in Microsoft Dynamics GP
In May is Controls and Security in Microsoft Dynamics GP; Discover how Business Intelligence can improve visibility and help you make better, more timely, decisions..

Tue, May 18th, 2021 4:00 PM - 4:45 PM BST

Register Here

Simple Audit for Microsoft Dynamics GP: Introduction

Microsoft Dynamics GPThis post is part of a series on creating a simple audit for Microsoft Dynamics GP.

I created a simple audit a while ago when a client had a requiremnet to audit the change of EFT details onvendors, but did not have sufficient budget for a full audit product like the Assure Suite from Fastpath. This is not to say that the Assure Suite is expenseive, but the client in question was a very small client with only two users of Microsoft Dynamics GP.

The requirement for the original user was very simple; an audit on changes to vendor EFT in the single company they had in Dynamics GP.

Many of the audit tools available for Dynamics GP work by creating triggers in the database, and this is the same approach I took. The first step in creating the audit was to create a table to hold the audit table, then to create the triggers on the table to be audited and then to provide a method of reporting for users to use.

Over the course of this series, each of these steps will be covered.

Simple Audit for Microsoft Dynamics GP: Series Index

Microsoft Dynamics GPI created a simple audit a while ago when a client had a requirement to audit the change of EFT details on vendors, but did not have sufficient budget for a full audit product like the Assure Suite from Fastpath. This is not to say that the Assure Suite is expensive, but the client in question was a very small client with only two users of Microsoft Dynamics GP.

In this series of posts, I will step through the process of creating the custom audit, using the vendor EFT one as the example. The index, below, will automatically update as the posts are published, unless you're reading the post via syndication in which case check the original post.

Simple Audit for Microsoft Dynamics GP
Introduction
Create Table
Create Triggers
SQL View for Reporting
Conclusion