Friday, February 16, 2018

Microsoft Dynamics GP: Running System and Company Databases on Azure SQL - Part 1

Hi all,

Before you get too excited, please know that there is no immediate plans for Microsoft to support Dynamics GP on Azure SQL. This vlog is rather an attempt to demystify some Community forum claims to his effect, but rather than simply saying "It doesn't work!", what I want to do is walk through the motions of provisioning the Azure databases, configuring the SQL Server, defining the elastic pool container, migrating the databases, and attempting to run Microsoft Dynamics GP and GP Utilities, while highlighting the current limitations preventing this configuration from working.

For ease of consumption, I have divided this vlog in two parts. Please enjoy part 1. In part 2, we will work on migrating the databases and getting GP connected to them. If nothing else, you would have learned how easy it is to get Azure SQL up and running :)

Until next post,

Mariano Gomez, MVP

Friday, February 9, 2018

How to Manually Replace the Web Client SSL Certificates

I finally managed to get to this video! Every so often I come across Community forum posts requesting instructions on how to replace an expired certificate being used with the Microsoft Dynamics GP web client. The traditional response has always been to uninstall and reinstall the web client after replacing the certificate in IIS (single-machine installation), or to repair the installation (custom install). I frankly find those two processes a bit cumbersome and prone to errors, since system administrators often need to remember steps and entries that they may not even have initiated themselves.

The procedures I outline in this video make use of the netsh command prompt utility to accomplish this and I intend to soon publish a PowerShell script that would automate the entire process altogether. For now, enjoy this video and don't forget to provide your feedback.

Until next post,

Mariano Gomez, MVP

Friday, January 26, 2018

Microsoft Dynamics GP and TLS 1.0

Is not too often that you will hear me saying anything negative about my beloved Microsoft Dynamics GP, but it is quite disheartening, to say the least, to see Microsoft not addressing the industry move away from TLS 1.0 as it relates to Dynamics GP.


TLS or Transport Layer Security protocol provides privacy and data integrity between applications wishing to exchange data. TLS ensures that the connection is private, because it uses symmetric keys to encrypt the data between the parties; the identity of the communicating parties can be authenticated via some public key; and the connection ensures some level of message integrity, because there's a message integrity check via a message authentication code.

As you would expect, TLS 1.0, 1.1, 1.2, 1.3 (draft), etc., are simply, progressive implementations, albeit with substantial differences that in some cases preclude interoperability between versions, of the same protocol. What is key however to this discussion is the age of each. For example, TLS 1.0 has been around since 1999, so suffice to say, that's extremely Mesozoic in technology years.

Acknowledging the security risks faced by companies and business applications still relying on TLS 1.0, the PCI Council voted to end support for TLS 1.0 as June 30, 2016. However, on December 15, 2015, they backtracked and extended the deadline to June 30, 2018.

You can read the full details in this PCI Council blog article:

Date Change for Migrating from SSL and Early TLS

Microsoft Dynamics GP

At this point, it's safe to assume you see the impact this has on Microsoft Dynamics GP, but I will summarize the list of applications that are currently impacted by this:

  • Web Services for Microsoft Dynamics GP
  • Business Portal for Microsoft Dynamics GP (it relies on Web Services)
  • Web Client (both Silverlight and HTML5 clients)
  • Service Based Architecture

Edit: If you disable TLS 1.0 in IIS, for example, none of the above services will be able to authenticate and communicate to Microsoft Dynamics GP. 

This problem even affects the newly minted GP 2018.

See Also

Until next post,

Mariano Gomez, MVP

Saturday, December 9, 2017

Microsoft Dynamics GP 2018 installation - First Look

Hi everyone! Walk through Microsoft Dynamics GP 2018 installation with me and learn the basic installation steps. Know what to expect as you go through the process, including setting up the account framework, defining account framework segments, deploying SQL Server Reporting Services reports during the installation, setting up the sample company, Fabrikam; login in for the first time and setting up your home page profile.

I will be working on other videos around Microsoft Dynamics GP 2018, so all ideas are welcome in the comment section.

Until next post,

Mariano Gomez, MVP

Tuesday, November 28, 2017

Building Microsoft Dexterity Cross-Dictionary Applications with GP Power Tools - Part 2/2

In Part 1 of this two-part series, we saw how to use the Resource Information and the Runtime Execute Setup features in GP Power Tools to gather the name of a List View object and model pass-through sanScript code that would set the image state of a specific line to checked (marked), based on an entity value in the list. As a refresher, the following video shows where we are at this point:

Now this is all good, but technically speaking, this code should be parametrized to allow us to evaluate and mark a number of entities that will be coming from a setup table in my Dexterity application. We would want to iterate through that table, pass in the configured entities as parameters to this code, so they can be evaluated and marked if exists in the list.

In this article, I want to show 2 more GP Power Tools features that will make your cross-dictionary code usable and transferable from GP Power Tools directly into your Dexterity application.

SQL Execute Setup

In the Runtime Execute Setup code we added (shown in the video), we hard-coded the value of the Multi-Entity Management entity to have it marked when it matched a value in the list view. However, hard-coded values of any kind are evil and hardly represent the universe of values that could be selected by a user. Remember also that the primary intent is to transfer our code with as little edits as possible.

First, and to simulate this condition, we would want to build a simple SQL query to return a list of entities that we could use as part of a parametrized list:

1) Click on Options > Scripting > SQL Execute Setup

SQL Execute Setup

It is worth noting at this point that all these options can be accessed from the GP Power Tools navigation bar.

2) In the SQL Execute Setup window, we will want to setup a Script ID, Script Name and the SQL database we want the query to be executed again.

SQL Execute Setup window

3) The list we want is made up of account segment number 3 of our chart of accounts. We will need unique values for this. The following query should get the job done:

SQL Execute Setup window

We need a third column with the key value to be returned. The first two columns will be used as display values for a parametrized list.

Parameter Lists

Parameter Lists are a very interesting concept in GP Power Tools. They allow you to create everything from custom lookups to range values, or discrete values to be passed to anything from Power Tools' Triggers, Runtime Execute  and .NET Execute scripts, and even other SQL Execute scripts. For this particular case, we need to create a parameter list that will display a custom lookup with the SQL Execute script above. This in turn will allow users to interact with our cross-dictionary code, but will also provide absolute validation that our code is working as intended.

1. Click on Options > Scripting > Parameter Lists

Parameter Lists option

2. In the Parameter List Maintenance window, fill out the required fields. Parameter Title and Parameter Instructions will be presented to the user when the custom lookup is activated.

Parameter List setup

3. In setting up the actual parameter, we will need to define a prompt, set the type to Lookup, the mode to Single Field, the Options to Custom Lookup (SQL), Length/Decimal to 5, as shown below.

Parameter configuration

Once you have set the Options to Custom Lookup (SQL), an expansion button becomes enabled to allow you to select a specific SQL Script. Here we will choose the SQL Script created in our previous section.

Parameter List Lookup SQL Script window

4. Click OK to save the selected lookup SQL script and Save to save the new parameter.

5. Back in the Runtime Execute Setup window, we can reload our script then select the newly created parameter list as a Parameter ID to the script.

Parameter ID field selected

6. Now, it's time to insert the parameter instead of the hard-coded string. Highlight the hard-coded string, "20", then click the Parameters button and select the available parameter. The following video shows the full action:

The insert will create a moniker space for the parameter, {%01%}.

7.  The following video shows the script being tested with the parameter.

8. Finally, we will generate the sanScript pass-through code that will be placed in our Dexterity application. This is done from the Script button drop-down list on the toolbar. This final video shows the results of this process.

If you noticed, the cross-dictionary code includes a parameter being passed to the Dexterity execute() function and an inout variable in the pass-through code to use as parameter to the execution. This is definitely a time saver! Now, all is left is to put this code into a function in my dictionary and we are good to go.

I hope you, as a Dexterity developer, consider incorporating GP Power Tools in your arsenal of development tools. Having the ability to model your cross-dictionary code once, test it out, and incorporate it safely and confidently within your dictionary goes a long way and saves a lot of frustrations in the process of developing your solution.

Until next post!

Mariano Gomez, MVP

Sunday, November 26, 2017

Building Microsoft Dexterity Cross-Dictionary Applications with GP Power Tools - Part 1/2

As part of an existing project I am currently working on, we need the ability to programmatically select the entities with the Binary Steam's Multi-Entity Management Select Payables Checks alternate window, when running a Decentralize process. In our software, users have the ability to pre-configure and save those settings, and our code needs to be able to set the values within the entity list view and drive the processing by clicking the Build Batch button.

Binary Stream's Select Payables Checks alternate window

Dexterity developers are constantly challenged by the difficulties of creating cross-dictionary code to interact with resource objects in third-party dictionaries, so I thought I would show some really cool features available in GP Power Tools, written by Microsoft MVP, David Musgrave, over at Winthrop Development Consultants, that can help developers test and package their cross-dictionary code more effectively, and without second guessing whether it will work once deployed.

Resource Information window

One of the first challenges when working with third-party dictionaries is to be able to identify the name of the resource we want to target. In this particular case, the resource in question is a list view on the Select Payables Checks alternate window. 

To identify this resource, we will use the Resource Information window in GP Power Tools. Follow these steps:

1. Click on Options > Resource and Security > Resource Information

Resource Information option

2. In the Resource Information window, click the Show currently selected Window and Field Information check mark.

Resource Information window

By selecting this option, Power Tools will now "listen" for any new window that is open and as you click through the fields on the window, it will identify what you are clicking through and display the corresponding field information.
3) Open the Select Checks window (Purchasing > Transactions > Select Checks) and navigate to the entity List View. 

Select Payables Checks and Resource Information windows side-by-side

Now, you can see that the List View object I am interested in is called '(L) MainList'.

Runtime Execute Setup

Since we now know the name of the List View object, we can proceed to create some cross-dictionary code that will allow us to navigate the list and set a state image when the entity is value is, tentatively for this example, "20". As a general rule, it is good to add some extra code to capture the index of the state images associated to objects such as list views and tree views.

For this example, the checked image index value is 2, which I have previously researched to be the correct value. Here are the steps to setup the script.

1. Open the Runtime Execute Setup window. Go to Options > Scripting > Runtime Execute Setup.

Runtime Execute Setup

2. For this script, we will setup a Script ID, which we will call MEM_LV and for script name we will set Multi-Entity Management List View. We will want this script to execute in the context of the Multi-Entity Management dictionary, as shown below.

Runtime Execute Setup

Runtime Execute has the ability to setup scripts that can be rolled out to end-users (Published to Executer Window checkmark) or grouped in projects for easy access and export (similar to a Solution is Visual Studio). We can also drive scripts via custom parameters, but I will leave this topic for the second part of this article.

3. Now that we have filled out the required header information, we just need to add some sanScript code that will change the image state to a checkmark for entity "20". That code looks something like this:

Runtime Execute Setup

Note that we are running the change script on the List View object, however, this particular List View does not have any code running behind the change event. It's always good to manually perform a task in the window in question for the object you are interested in, capturing a script log in the process to see if there's any specific code you need to worry about. 

We can now test this code.

But the pass-through code that I need from my Dexterity application needs to parametrize the entity ID since I will be supplying these values from a table to be marked by the code you saw above. Tomorrow I will show you how to leverage both the SQL Execute Setup and the Parameters Lists features in GP Power Tools to take the headache away from this process.

Until next post!

Mariano Gomez, MVP

Friday, November 24, 2017

Retrieving dictionary build numbers outside of Dynamics GP - Revisited

Back in June of 2009 - I know, right! Time flies - I wrote an article, Retrieving dictionary build numbers outside of Dynamics GP, in which I described two methods to retrieving the build number of a particular Dexterity dictionary, none of which involved opening Dynamics GP to do so. The first method involved hovering over the dictionary itself, which would display a summary of the properties, including the build number; the second method involved right-clicking on the dictionary, then selecting Properties, then clicking on the Dictionary tab to obtain the Dictionary Version, Name, ID, associated forms and reports dictionary, compatibility ID, and compatibility message.

Sample Dictionary properties tab

Shortly after the publication of this article, I started seeing messages on the forums and discussion boards that the Dictionary tab was not available in certain environments, but I never really paid attention enough to find the reasons why.

Just recently I was checking the Microsoft Dynamics GP Community forum and stumbled upon someone who had ran into this problem in 2010, ensued by some answers asking to check a few registry entries. It wasn't until 2011 that forum user Nathan Hayden provided a complete answer to restore the Dictionary tab in the File Properties window, which I tested and found it to work like a charm.

So here's the solution:

1. Open the Windows Registry (regedit.exe).

2. Under HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\FileExts\.DIC\OpenWithList,  remove all entries, leaving just (Default) and MRUList.

3. Under HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\FileExts\.DIC\OpenWithProgids,  remove all entries, but (Default) and DIC_auto_file.

3. Finally, delete HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\FileExts\.DIC\User Option, if it exists

NOTE: In Windows 10, the key is UserChoice

Disclaimer: Edit the Registry at your own risk. Inappropriate changes to Windows Registry can disable the operating system! Backup your Windows Registry prior to making any changes.

Until next post!

Mariano Gomez, MVP