Accessing the Dynamics GP Configuration File (Dynamics.exe.config) from a Visual Studio Tools for Dynamics GP Add-In – Part 1

This is Part 1 of a three part article on working with the Dynamics.exe.config .NET configuration file. with the following articles:

• Part 1 – Creating a configuration handler for a custom configuration section.
• Part 2 – Testing the configuration handler.
• Part 3 – Using the configuration handler in a Visual Studio Tools for Dynamics GP Add-In

[Click here for the Visual Studio solution used in these articles]

So one of the great things I love about .NET is the configuration file. A more flexible file format than the old .ini file and not the registry (need I say more?). That being said, I’ve tended to rely on the simple AppSettings mechanism making it fairly easy to create and manage your settings in C# or VB.NET projects.

I love using them for things like default import file paths, remembering user interface settings, connection strings, URLs and such. Now I know there are other ways to store settings, such as in a database table, but sometimes the configuration file is the best tool for the job.

However, .NET DLLs, for a number of reasons I won’t get into here, don’t inherently have their own configuration files. They must access their parent application’s file. Although you can, in fact, use AppSettings, as long as you modify the host application’s (in this case Dynamics’) configuration file, there are some great reasons to go custom.  And it’s easier than you may think.

Since a Dynamics GP Add-In is a .NET DLL, the configuration file we have access to is the Dynamics.exe.config file. So how do we use it to store our own settings that can be used by our Add-Ins?

Step one in this process is to understand the structure and handling of configuration files.

Configuration files are broken into sections (there are also section groups but we’ll ignore those for this project). A section is simply an XML node off the top level configuration node. There’s really no restrictions on its structure as long as it’s valid XML. In GP 10 there is a single section, shell.

Each section must have a section definition that includes the assembly that contains the configuration handler object that reads that section. Let’s take a look at the Dynamics.exe.config file.

[Click here to download the default Dynamics.exe.config configuration file]

Notice that the first node in the configuration file is the configurationsections node. This is where sections are defined. Notice that here, Dynamics is defining the shell section, which you will note is the next node after the configuration sections node. The section definition consists of the name, which is the name of the top level node for that section, followed by the type, which defines the assembly and handler that reads that section.

So to add our own section to the configuration file, we simply need to add a section definition and then the corresponding section…oh yeah, and write a configuration section handler assembly.

In this example, we’re going to create a fairly. It will have a single node, GPAddIn and two properties, HelloConfigurationCaption for our message box, and ImportFileName for our hypothetical file integration. The properties are going to be implemented as attributes of the GPAddIn node, leaving us without the need to handle sub-nodes.

When all is said and done our modified Dynamics.exe.config file will look like this:

[Click here to download the configuration file with the custom section]

Note that we’ve added a new section definition for the GPAddIn section and the section itself with our two settings as attributes. In the type attribute of the section definition, we have “GPAddInConfiguriation.GPAddInSectionHandler”, the actual class that reads the GPAddIn section, and “GPAddInConfiguration”, the name of the assembly itself.

So now that we can read the configuration file and define a custom section, how do we write the code to read it?

So a couple of basics. The section handler class itself is a class that derives from the System.Configuration.ConfigurationSection class. The actual assembly must be a .NET DLL and must be deployed to the same folder as the parent executable. Note that this means that it is in the Microsoft Dynamics\GP folder, not the AddIns folder that the add-in itself will be deployed to.

So let’s write some code! First things first. The source code for this solution can be downloaded from here.

We’re going to start with the configuration handler DLL project, GPAddInConfiguration. This project is a Class Library project and consists of a single C# class file containing a single class, GPAddInSectionHandler, derived from System.Configuration.ConfigurationSection. You will need to reference the System.Configuration assembly in the project.

The beginning of your class file will look as follows:

using System;
using System.Configuration;

namespace GPAddInConfiguration
{
public class GPAddInSectionHandler : ConfigurationSection
{

Next we’ll define the constructors:

// CustomSection constructor.
public GPAddInSectionHandler()
{
}

public GPAddInSectionHandler(string helloConfigurationCaption, string importFileName)
{
HelloConfigurationCaption = helloConfigurationCaption;
ImportFileName = importFileName;
}

After that we define the configuration attributes. We’re using the declarative method so we don’t need to worry about the configuration property collection or private variables:

[ConfigurationProperty("HelloConfigurationCaption", DefaultValue = "Hello Dynamics GP Configuration", IsRequired = true)]
[StringValidator(MinLength = 1, MaxLength = 60)]
public string HelloConfigurationCaption
{
get
{
return (string)this["HelloConfigurationCaption"];
}
set
{
this["HelloConfigurationCaption"] = value;
}
}

[ConfigurationProperty("ImportFileName", DefaultValue = "importfile.txt", IsRequired = true)]
[StringValidator(InvalidCharacters = " ~!@#$%^&*()[]{}/;'\"\\",
MinLength = 1, MaxLength = 255)]
public string ImportFileName
{
get
{
return (string)this["ImportFileName"];
}
set
{
this["ImportFileName"] = value;
}
}

Here we’ve defined the two configuration properties, HelloConfigurationCaption and ImportFileName. Using the ConfigurationProperty attribute we’ve defined their type, default value and whether or not their required without having to write any of the handling code.

Additionally, using the StringValidator attribute we can define what constitutes a valid value, such as maximum length, and in the case of the ImportFileName, invalid characters.

And that’s it. Configuration handler completed. Obviously there’s a lot more that you can do such as child elements and even complete custom handling of the section XML, but for a basic replacement of AppSettings, this works quite nicely, and I would argue, makes for a more readable configuration file.

Hopefully this has demystified the configuration file and will allow you to move beyond the simple AppSettings object. In Part 2, well take our section handler and run it through its paces in a test application.

Error When Running Dynamics GP SQL Reporting Services Trial Balance Summary or Detail Reports

This post deals with the following error message:

The EXECUTE permission was denied on the object 'smGetMsgString', database 'DYNAMICS', schema 'dbo'.

We recently deployed the Dynamics GP SQL reports to a client.  I think this must be the first client who actually went through all of them because we came across a bit of a bug in the security in the Trial Balance Summary and Detail Reports.

Now admittedly, we diverted somewhat from the standard install by modifying the shared data sources to use a fixed SQL login rather than integrated security but we dutifully added that login to the rpt_poweruser role in each of the company databases and the DYNAMICS database.

When the client went to run the Trial Balance Detail report they got the following error:

An error occurred during client rendering.

An error has occurred during report processing.

Query execution failed for dataset 'dsProc'.

The EXECUTE permission was denied on the object 'smGetMsgString', database 'DYNAMICS', schema 'dbo'.

The same error occurred on the Trial Balance Summary report.  In reviewing the permissions on the smGetMsgString stored procedure, I found that only the DYNGRP role int he DYNAMICS database had EXECUTE permissions on it.  After giving the three “rpt_” roles EXECUTE permissions, the report ran fine.

Has anyone else run across this or similar issues on other reports?  Has Microsoft fixed this in a service pack?

Dynamics GP/SQL Database Login Disconnect

This article deals with the following error messages in Dynamics GP:

• “A get/change first operation on table 'coProcess' failed accessing SQL data”
• “[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER2" is not able to access the database "TWO" under the current security context.”
• “The user could not be added to one or more databases.”
• “A get/change first operation on table 'SY_Users_MSTR' failed accessing SQL data”
• “[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER1" is not able to access the database "DYNAMICS" under the current security context.”
• “[Microsoft][SQL Native Client][SQL Server]EXECUTE permission denied on object 'zDP_SY01400SS_1', database 'DYNAMICS', schema 'dbo'.”

Dynamics GP tracks users separately from SQL Server.  However, there is a one to one relationship between a Dynamics GP user and a SQL Server login.  Both the Dynamics GP user and corresponding SQL Server login have the same username.  Additionally, when a user is granted access to a company, their SQL login is added as a SQL user in that company’s database as part of the DYNGRP role.  The SQL logins associated with Dynamics GP users also have an associated SQL database user in the DYNAMICS database also a member of the DYNGRP role.

Problems happen when the Dynamics GP users and SQL logins get out sync.  There are five situations when these get out of sync:

• The Dynamics GP user has been given access to a company in GP but the corresponding SQL login doesn’t have access to that company.
• The Dynamics GP user doesn’t have access to a company in GP but the corresponding SQL login does have access to the company.
• The Dynamics GP user doesn’t have access to the DYNAMICS database.
• The Dynamics GP user’s corresponding SQL login doesn’t have access to the Dynamics database.
• The Dynamics GP user’s corresponding SQL login isn’t a member of the DYNGRP role in the databases it is supposed to have access to.

First we’ll look at each of these situations individually and how to individually resolve them.  Then we’ll look at a script that will clean up all of them at once.

Dynamics User w/ Missing SQL User

This is the case where a Dynamics GP user has been given access to a company in GP but the corresponding SQL login doesn’t have access to that company.  In this case, the user will receive the following error message when logging into Dynamics GP:

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER2" is not able to access the database "TWO" under the current security context.”

To resolve this error, go into SQL Management Studio and add the corresponding SQL login to the company database as a member of the DYNGRP role.

SQL User in Company Database w/o Corresponding Dynamics GP User Access

In this case, the Dynamics GP user does not have access to a particular company but the corresponding SQL login does have access.  This causes an error in the User Access form if you attempt to add the user to the offending company as follows:

“The user could not be added to one or more databases.”

In this case, going into SQL Management Studio and removing the SQL login’s access to the company database will resolve the error and allow the Dynamics GP user to be given access to the company.

SQL User Doesn’t Have Access to the DYNAMICS Database

In this case, the Dynamics GP user has access to the company database, but doesn’t have access to the DYNAMICS database.  If this happens, the user will see the following error when attempting to login to Dynamics GP.

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]The server principal "LESSONUSER1" is not able to access the database "DYNAMICS" under the current security context.”

To resolve this error, go into SQL Management Studio and add the corresponding SQL login to the DYNAMICS database as a member of the DYNGRP role.

SQL User in Company Database w/o Membership in DYNGRP SQL Database Role

In this case, the Dynamics GP user has access to a company and their corresponding SQL login has access to the company database, however, they are not a member of the DYNGRP role in the company database.  In this case the user will see the following error when trying to login to the company in question:

 

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]EXECUTE permission denied on object 'zDP_SY01300F_1', database 'TWO', schema 'dbo'.”

To resolve this, go into SQL Management Studio and add the corresponding SQL login to the DYNGRP in the company database in question.

SQL User Is Not a Member of the DYNGRP Role in the DYNAMICS Database.

In this case, the SQL user is properly setup in the company database and has access to the DYNAMICS database, but is not a member of the DYNGRP role in the DYNAMICS database.  When logging in in this situation, the user will receive the following error:

Clicking on “More Info” returns the following:

“[Microsoft][SQL Native Client][SQL Server]EXECUTE permission denied on object 'zDP_SY01400SS_1', database 'DYNAMICS', schema 'dbo'.”

To resolve this, go into SQL Management Studio and add the corresponding SQL login to the DYNGRP in the DYNAMICS database.

A Script to Fix it All

So the individual fixes are all well and good, but we recently ran a Reformatter project, and in the process, moving from one SQL server to another, at a client with 50+ users and 230+ companies.  In addition, we were reconfiguring all of their security to take advantage of GP 10’s role based security.  Something we hadn’t done during the original upgrade.

When all was said and done, we ended up with a number of situations where there were various disconnects in the users, mostly SQL logins missing from companies when they should have been there and in companies they shouldn’t.  Fixing all this by hand would have been a nightmare, so we decided to write a script that would fix it.  In this case we had to write a script to write a script (see prior article related to this and how to setup SQL Management Studio to do this).  Here, we not only had to write a script that created a script against each company, but also against each user as follows:

--  Don't forget to set Results to Text in your
--  SQL Management Studio query window.
--
--  Set NOCOUNT on so that row count doesn't show
--  up in the text results.
SET NOCOUNT ON;

--  Common table expression that returns every
--  possible combination of user and company.
WITH UserCompanyCombinations
AS (
    SELECT
            SY01400.USERID
            ,SY01500.CMPANYID
            ,SY01500.INTERID
        FROM SY01400 -- User Master table
            CROSS JOIN SY01500 -- Company Master table
        WHERE SY01400.USERID NOT IN ('sa', 'DYNSA')
)
--  Script to add all users to DYNAMICS database
SELECT
        SQLStatement =
            REPLACE(
'
USE DYNAMICS
GO

PRINT ''ADDING USER {USERNAME} to DYNAMICS database.''

CREATE USER [{USERNAME}] FROM LOGIN [{USERNAME}]
GO

EXEC sp_addrolemember ''DYNGRP'', ''{USERNAME}''
GO

----------

',
            '{USERNAME}', USERID)
    FROM SY01400 -- User Master table
    WHERE USERID NOT IN ('sa', 'DYNSA')

UNION ALL

--  Script to remove users from company databases they shouldn't
--  have access to.

SELECT 
        SQLStatement =
            REPLACE(REPLACE(
'
USE {COMPANYDB}
GO

PRINT ''REMOVING USER {USERNAME} from {COMPANYDB} database.''

DROP USER [{USERNAME}]
GO

----------

',
            '{COMPANYDB}', UserCompanyCombinations.INTERID)
            , '{USERNAME}', UserCompanyCombinations.USERID)
    FROM UserCompanyCombinations
        LEFT JOIN SY60100 -- User Access table
            ON UserCompanyCombinations.USERID = SY60100.USERID
                AND UserCompanyCombinations.CMPANYID = SY60100.CMPANYID
    WHERE SY60100.CMPANYID IS NULL

UNION ALL

--  Script to add users to company databases they should have access to
SELECT
        SQLStatement =
            REPLACE(REPLACE(
'
USE {COMPANYDB}
GO

PRINT ''ADDING USER {USERNAME} to database {COMPANYDB}''

CREATE USER [{USERNAME}] FROM LOGIN [{USERNAME}]
GO

EXEC sp_addrolemember ''DYNGRP'', ''{USERNAME}''
GO

----------

',
            '{COMPANYDB}', SY01500.INTERID)
            , '{USERNAME}', SY60100.USERID)
    FROM SY01500 -- Company Master table
        INNER JOIN SY60100 -- User Access table
            ON SY01500.CMPANYID = SY60100.CMPANYID
    WHERE SY60100.USERID NOT IN ('sa', 'DYNSA')

As you can see this is a union of three separate queries as follows:

• The first query creates a SQL statement for each user, adds them to the DYNAMICS database and then makes them a member of the DYNGRP role. 
• The second query creates a SQL statement for each user and company that the Dynamics GP user doesn’t have access to a company as defined by the SY60100 user access table and removes their SQL user from the associated company database.
• The final query creates a SQL statement for each user and company database that the Dynamics GP users do have access to as defined by the SY60100 user access table, adding the corresponding SQL login to the database as a member of the DYNGRP role.

You’ll note that in all of the queries we’re excluding the “sa” and “DYNSA” users as these are treated as special users in Dynamics GP.  “sa” should always be in the “sysadmin” server role, giving it full access to all databases.  The “DYNSA” user maps to the “dbo” (database owner) user in each database rather than a user of the same name.  This gives the “DYNSA” user special privileges in the database, such as the ability to modify database objects.

When you run the script above you should receive something like the following in the results pane:

USE DYNAMICS
GO

PRINT 'ADDING USER LESSONUSER1 to DYNAMICS database.'

CREATE USER [LESSONUSER1] FROM LOGIN [LESSONUSER1]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER1'
GO

----------

USE DYNAMICS
GO

PRINT 'ADDING USER LESSONUSER2 to DYNAMICS database.'

CREATE USER [LESSONUSER2] FROM LOGIN [LESSONUSER2]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER2'
GO

----------

USE FOUR
GO

PRINT 'REMOVING USER LESSONUSER2 from FOUR database.'

DROP USER [LESSONUSER2]
GO

----------

USE TWO
GO

PRINT 'ADDING USER LESSONUSER1 to database TWO'

CREATE USER [LESSONUSER1] FROM LOGIN [LESSONUSER1]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER1'
GO

----------

USE TWO
GO

PRINT 'ADDING USER LESSONUSER2 to database TWO'

CREATE USER [LESSONUSER2] FROM LOGIN [LESSONUSER2]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER2'
GO

----------

USE FOUR
GO

PRINT 'ADDING USER LESSONUSER1 to database FOUR'

CREATE USER [LESSONUSER1] FROM LOGIN [LESSONUSER1]
GO

EXEC sp_addrolemember 'DYNGRP', 'LESSONUSER1'
GO

----------

Note that this is a little quick and dirty and running this script will cause errors when it tries to create users that already exist or drop users that don’t.  Adding some extra code to check for a user’s existence or not wouldn’t be that difficult.