New Article on MSDynamicsWorld: "When It Comes to Customizations for Microsoft Dynamics GP, Which Tool Should You Rely On?"

"The term "customization" can mean different things to different people..."

My new article is out over at MSDynamicsWorld. This time, I go back to basics defining what is a customization and what tools are available to customize the Microsoft Dynamics GP user interface. This article is a good start if you are still trying to figure out your options for developing add-on solutions to Microsoft Dynamics GP. To read the full article, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

Enforcing Password Policy with Microsoft Dynamics GP

The ability to enforce password policies was introduced since version 9 of Microsot Dynamics GP (see Why does Microsoft Dynamics GP encrypt passwords? over at Developing for Dynamics GP for more information).




Surprisingly, still many system administrators are not taking advantage of this feature, because they have found it difficult to manage without certain reporting necessary to follow up on Microsoft Dynamics GP logins activity. The typical complaints revolve around the lack of visibility on when a user password will expire or whether the account has been locked or not.

To make administrative tasks even more difficult, Dynamics GP systems administrators must rely on database administrators and Windows Server administrators to resolve any issues arising from a user being locked out the system, typically working their way through a helpdesk on a relatively simple issue.

With that said, I set out to create a query that could provide systems administrators with an insight into Microsoft Dynamics GP logins and their password expiration settings:

LoginPolicies.sql

use master;
go
set nocount on;
go
declare @loginname varchar(200);

declare @logintbl table (
 LoginName varchar(20)
   ,IsLocked char(5)
   ,DaysUntilExpiration int
);

declare c_logins cursor for
  select [name] from sys.syslogins where name in
    (select USERID from DYNAMICS..SY01400);
open c_logins;

fetch next from c_logins into @loginname;
while @@FETCH_STATUS = 0
begin
 insert @logintbl(LoginName, IsLocked, DaysUntilExpiration)
 select
   @loginname
  ,case convert(smallint, LOGINPROPERTY(@loginname, 'IsLocked')) when 0 then 'No' when 1 then 'Yes' end
  ,convert(int, LOGINPROPERTY(@loginname, 'DaysUntilExpiration'));
 
 fetch next from c_logins into @loginname;
end

close c_logins;
deallocate c_logins;

select * from @logintbl;
go
set nocount off;
go

When the above query is executed in Microsoft SQL Server Management studio, it produces the following results:

LoginName            IsLocked DaysUntilExpiration
-------------------- -------- -------------------
sa                   No       NULL
DYNSA                No       NULL
LESSONUSER1          No       0
LESSONUSER2          No       NULL

Note that this query uses a table variable. If you are looking for a more permanent solution, you can replace the table variable for an actual table.

You may also use Support Debugging Tool's SQL Execute option to run the above query -- I have attached the configuration file for the script to be imported into Support Debugging Tool using the Configuration Import/Export option.




Many system administrators would also want to know when was the last time a user logged into GP, but unfortunately, SQL Server does not keep track of login activity, unless you enable some of the auditing functions. Another alternative is to enable Activity Tracking in GP and track all successful login attempts sorted from the most recent. You may then incorporate this information in the above query for a cohesive result.

Related Resources

Microsoft Dynamics GP Application Level Security Series @ Developing for Dynamics GP
The Scoope on Dynamics GP's Application Password System @ Inside Dynamics GP

Downloads

Support Debugging Tool XML configuration file - LoginPolicies.dbg.xml

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

DYNSA

There are a number of good blog posts in the community explaining the differences between sa, Microsoft SQL Server's defacto administrative user; and DYNSA, Microsoft Dynamics GP defacto administrative user. In reading a number of these articles I realized one thing: they address little about the DYNSA user.

So what's DYNSA?

You only have to read back a few lines to obtain this answer, however, I want take another direction. Let's start by saying that in Microsoft SQL Server, every database is created with a dbo user. Take for example this blank database created using SQL Server Management Studio:



The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role (like sa for example) who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

For example, if user DYNSA is a member of the sysadmin fixed server role and creates a table RM00101, RM00101 belongs to dbo and is qualified as dbo.RM00101, not as DYNSA.RM00101. Conversely, if DYNSA is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table RM00101, RM00101 belongs to DYNSA and is qualified as DYNSA.RM00101. The table belongs to DYNSA because he did not qualify the table as dbo.RM00101... and hear is the catch! Because DYNSA is not created as a member of the sysadmin fixed role (otherwise we would have a lot of angry database administrators), it must be associated to fixed database role db_owner, hence all object creation operations performed from Dynamics Utilities must qualify the object creation preceeded by "dbo.".

The dbo user cannot be deleted and is always present in every database.

Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

•Belong to the user creating the object, not dbo.

•Are qualified with the name of the user who created the object.

This explains two things:

1) Only sa can perform the first time installation of Microsoft Dynamics GP, since this is when all databases, database objects, and the DYNSA user are created. During installation, DYNSA is made a member of the db_owner fixed database role.

2) In lieu of the sa user, Microsoft Dynamics GP system administrators can use DYNSA to perform any upgrade or maintenance activities. Since DYNSA is the defacto database owner, then it can perform any activities related to that database. This is the reason why you should ensure that DYNSA remains the database owner prior to initiating any upgrade activity. The following script should help with this:


1. Run the following making sure that DYNSA is the database owner:

sp_helpdb DYNAMICS;
go
sp_helpdb %COMPDB%;
go

2. If the above returns anything different than DYNSA, use the following script to set the database owner to DYNSA:

use DYNAMICS;
go
sp_changedbowner 'DYNSA';
go
use %COMPDB%;
go
sp_chagedbowner 'DYNSA';

In the above scripts, replace %COMPDB% for your company database name on SQL Server.

Related Articles:

Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

eConnect Integration Service for Microsoft Dynamics GP 2010

One of the biggest improvements in eConnect 2010 is the addition of the new eConnect Integration Service. The eConnect installer now creates a new Windows Service application named eConnect for Microsoft Dynamics GP 2010 integration Service. The eConnect Integration Service is a Windows Communication Foundation (WCF) service that replaces the (very unstable) eConnect COM+ object available in previous versions of eConnect.

The eConnect Integration Service supports the operations of the eConnect .NET assemblies, the BizTalk adapter, and MSMQ interfaces. In addition, you can use the service directly from an application that adds a service reference to the eConnect Integration Service.




If you add a service reference, you do not need to add the Microsoft.Dynamics.GP.eConnect assembly and namespace to your development project.



To add the eConnect Integration Service to an application, you must first add a service reference to the Visual Studio project for that application.

To add a service reference to a Visual Studio project, the properties for the project must specify the target framework as .NET Framework 3.5. The following is a typical URL for the eConnect Integration service.

net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/


The interface provided by the service reference includes the same methods you find in the Microsoft.Dynamics.GP.eConnect assembly and namespace. Before you use a service reference to access eConnect Integration Service, you should become familiar with WCF development concepts.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

From the Newsgroups: What happened to Integration Manager "Save As" function?

In previous versions of Integration Manager you had the ability to save an integration with another name, but v10 onwards this feature was removed. Here is what Beth Gardner from the Microsoft Dynamics GP Online Partner Technical Community had to say:

"We removed the Save As functionality in one of the IM 10.0 service packs. The reason why, was the Save As feature had a bug with it for many years. It would copy the integration source file instead of creating a new integration source file in the new integration. This meant that if you were to change the source file on the second integration to browse to a different file, the original integration source file would also change.

This caused a large amount of confusion and problems for customers. Due to this, we removed it from the menu. What you will need to do instead is use the File Import Integrations process. This will allow you to browse out the the IM.mdb file you currently have open and select integrations. You will then be prompted to change the integration name and integration source names. This is a true copy of the original integration but has no ties to it and everything is unique.



This is the process you will need to follow going forward."

I hope you found this information useful.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

Microsoft Dynamics GP 2010 and Office Web Components

A recurring question throughout these days of upgrades to Microsoft Dynamics GP 2010 is what version of Microsoft Office Web Components (OWC) is compatible with Microsoft Dynamics GP 2010. In this article, I will address this question by explaining what is OWC and how it is used.

OWC are a collection of Component Object Model (COM) controls for publishing spreadsheets, charts, and databases to the Web and are required to support the display of metrics on a Microsoft Dynamics GP home page. These components have been released with various versions of Microsoft Office, as shown below.







Source: OfficeBlogs.net

The latter is important to understand, because the Microsoft Dynamics GP home page is an HTML rendering done from Dexterity code with the aid of some XSL and cascading stylesheets. The HTML code embeds calls to OWC functions via JScript to display the metrics.

The current supported version of OWC is Microsoft Office 2003 Web Components for versions 9, 10, and 2010.

So now that we have gotten past this point, Office Web Components have been discontinued in Office 2007 (so don't expect to see them in Office 2010 either), and are not included, except as a part of Microsoft Office Project Server 2007. However, they will still be available for download from Microsoft's website. Microsoft has not yet offered a complete replacement for the Office Web Components. However, developers can use a combination of third party products, Excel Services or Visual Studio Tools for Office to provide similar functionality.

My guess is that Fargo will continue to use OWC until there is a new roadmap in place for the product. We may have started to see some of this with the use of the Dundas Chart Controls, featured in Microsoft Dynamics GP 2010 if using SSRS metrics.

Please let me know what you think.

Related Articles

Microsoft Office 2003 Web Components - download

KB Article 916673 - Error message when you view the home page in Microsoft Dynamics GP: "Metrics are not available because Microsoft Office Chart..."

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Read More

Third year as a Microsoft MVP

This time of the year brings some stress for those of us MVPs in the July renewal cycle and holders of this prestigious Microsoft award. The bottom line is, every July 1st I find out whether I continue to keep my MVP stripes or... not! The good folks at Microsoft have felt once more that my contributions to the Microsoft Dynamics GP community in general have made me worthy of such honor, so it's with great joy that I announce I get to keep my award for one more year starting today, making it the 3rd consecutive year of such privilege.

NOTE: The record for consecutive years in our track is currently held by MVP Victoria Yudin, with 6 consecutive years.

Also, I must congratulate MVP Frank Hamelly for being reawarded. Frank's contributions have also earned him the right of passage.

So you ask, "what does a Microsoft MVP do?" Here is just a few things I did since last July:

• Published over 160 blog articles on my site with replies to more than 100 comments
• Answered over 2500 questions on the various user newsgroups and forums
• Answered over 1300 questions on the Partner Technical Forum
• Wrote over 20 articles for various outlets
• Presented at the Microsoft Dynamics GP Technical Conference 2009
• Presented at the Microsoft Convergence 2010 in Atlanta
• Presented at the Decisions 2010 Spring virtual conference
• Continue to provide beta testing support for Support Debugging Tool and Menus for Visual Studio Tools.

Ah, and more importantly, continue to earn a living for the family while doing all the above. For more information on the Microsoft MVP Award Program, visit the MVP Award Program Blog site.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More