Why does Microsoft Dynamics GP encrypt passwords?

This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Why doesn’t the password I use for Microsoft Dynamics GP work for me to access the data in SQL from <insert application name here>?

Why does my password sometimes fail to work from one workstation when it works from another?

Why does the password policy feature only allow one failed attempt before locking me out?

Why is the User ID case sensitive on Microsoft Dynamics GP v10.00?

What is the Password field in the SY_Users_MSTR (SY01400) table used for?

Can I use my Windows or Active Directory Login to access Microsoft Dynamics GP?

I hope to answer all these questions and more by explaining the password encryption that is used by Microsoft Dynamics GP for all users except ‘sa’.

So, let’s start with some basics.

The Password Field

In the days before SQL Server we used either Pervasive SQL (Btrieve) or Ctree formats for storing our data.  Access to the system was controlled by a password which was stored using a simple encryption in the Password field of the SY_Users_MSTR (SY01400) table.

Once we started using SQL Server, we no longer needed the password stored in the table as SQL Server stores the user’s credentials in its own system when used with SQL Server (Mixed mode) authentication.  The Password field in the the SY_Users_MSTR (SY01400) table is no longer used on a SQL Server system.

Access via DYNGRP

The method used to allow Dexterity to work with SQL Server and access all the required tables, views and stored procedures is based on the granting a user access to a database and adding that user as a member of the DYNGRP Security Database Role. All the access to the table, views and stored procedures has been granted to DYNGRP and so inherited by the user. So now the user has access to all the resources in the DYNAMICS System Database and to each of the company databases they have been granted access to.

Application Level Security

How do we control a user’s access to areas of the data?  This is all controlled via the application level security.  For v8.00 and v9.00 this was an optimistic class based model where users had access to everything unless it was specifically denied to them.  The interface was provided by Standard or Advanced Security and selection of customisations was controlled at the same time as security access.  For v10.00 this is now a pessimistic task and role based model where a user is denied access to everything unless it was specifically granted to them. The interface is spread over a few windows and the selection of customisations is now controlled separately from security access.

The application level security is mainly at the user interface (forms, smartlists, tools, posting permissions) level.  So access to data can be protected by denying access to the areas of the user interface that could be used to view or edit that data.  If the user has access to the Report Writer tool, you can use table level security to prevent access to tables.  A report will only be printed if all the tables used on the report have access granted for the current user.  It is normally easier to deny access to the Report Writer than to set up table level security.

Encryption

Based on what we understand to date, once a user has access to a database, as far as SQL Server is concerned, they have access to everything in that database. The Microsoft Dynamics GP application itself can restrict what a user has access to.  So, if a user was able to log into SQL Server via another application (such as MS Query or Access), they would be able to bypass the Microsoft Dynamics GP application level security and get access to everything.  That is not good….. so we use encrypted passwords.

By encrypting the password, it means that what is actually entered by the user as the password is not the password sent to SQL Server by Microsoft Dynamics GP. So if another application is used which does not understand the encryption, the password will not be encrypted and access will be denied.  Thus by encrypting the password, we prevent access by other applications and so prevent the Microsoft Dynamics GP application level security from being bypassed.

The v10.00 encryption algorithm

For v8.00 it was possible to use SQL Enterprise Manager (SQL 2000) or SQL Management Studio (SQL 2005) to change the user’s password and so have an un-encrypted password which can be used with other applications.  For v9.00 and v10.00 this is no longer possible as the application will request that the password be changed so that it is stored using encryption.  For v10.00, the encryption algorithm was strengthened as part of Microsoft’s Trustworthy Computing initiative.  This new algorithm does introduce some interesting side effects.

The algorithm includes the User ID (case sensitive) and the Server name from the ODBC DSN (not case sensitive) in the encryption key.  What this means is that if the Server name is changed or if you swap from using a Machine Name to an IP address, the old password will no longer work.  It also means that once a password has been created for a specific User ID, the case used for that User ID must remain the same.  For example: a password created for JoeBloggs will not work if the User ID is entered as joebloggs.  The reason is that if the encryption key is different, then the password sent to the SQL Server will not match regardless of what is entered by the user. So if you swap to a different workstation, make sure that the ODBC DSN Server Name is the same and that the User ID has been entered the same case.

Password Lockout

From v9.00 onwards it is possible to use SQL Server 2005 and the SQL Native Client with Windows Server 2003 and Active Directory to enforce the password policies from Active Directory including the Account Lockout Threshold.

Question 5 in the following Knowledge Base (KB) article discusses the Account Lockout Threshold and recommends it is set to at least 12 to allow for 3 failed password attempts.

Frequently asked questions about the advanced SQL Server options in the User Setup window in Microsoft Dynamics GP (KB 922456)

The reason for this is because when Microsoft Dynamics GP logs in it actually attempts to login 4 times and so a single failed attempt at the application level will use up 4 attempts at the SQL level.

• Attempt 1: Login using v10.00 encryption algorithm.
• Attempt 2: Login using v9.00 encryption algorithm.
• Attempt 3: Login using un-encrypted password.
• Attempt 4: Re-Login using v10.00 encryption algorithm to obtain error codes from SQL Server.

To allow administrators to set the setting for Account Lockout Threshold to 3 and have it behave as expected, a change was made to v10.00 Service Pack 2 which means that only a single attempt with the v10.00 encryption algorithm will be made by default.  If you want support for the legacy login encryption methods, you can add the following setting to your Dex.ini file:

SQLLoginCompatibilityMode=TRUE

If you want password policy and expiry without the infrastructure metioned above, for another version of Microsoft Dynamics GP or with more options, you could also look at the Omni Password module that is part of the Omni Tools suite from Rockton Software.  Disclaimer: This is a product that I originally developed as Winthrop Dexterity Consultants before I joined Microsoft.

Windows Authentication

The last topic I would like to discuss is integration with Active Directory and Windows Authentication.

At this stage Microsoft Dynamics GP does not support Windows Authentication and integration with Active Directory for logging into the core application.  It is on the “wish list” and may be added at some stage in the future but its not in any current release or scheduled for the next release.  So we need to stick with SQL Server or Mixed Mode authentication.

Unless… you are willing to look at a very cool product from FastPath Solutions.  They have created a tool called Configurator AD which can integrate Microsoft Dynamics GP logins with Active Directory. Please see their demo for more info.  Another option for a single sign on is the generic tool SecureLogin.

[Edit] Another option is to allow Microsoft Dynamics GP to remember the user name and password, so it does not have to be entered again.  This feature is available now for GP 2010 and also for previous versions as part of Omni Tools from Rockton Software.

Developing with Encryption

If you are a VBA developer working with Microsoft Dynamics GP you can use RetrieveGlobals.dll (v8.00), RetrieveGlobals9.dll (v9.00) or the UserInfoGet object (v10.00) to create a connection to SQL Server via ActiveX Data Objects (ADO).  The KB article below explains the method needed for each version:

How to use ActiveX Data Object (ADO) with VBA on a window with Microsoft Dynamics GP and with Microsoft Business Solutions – Great Plains 8.0 (KB 942327)

Please note that the v10.00 UserInfoGet object had a fault which meant it did not work properly until v10.00 Service Pack 1.  The KB article below has the details.

Error message when you run the UserInfoGet.CreateADOConnection VBA function in Microsoft Dynamics GP 10.0: “Login failed for user ‘[User Name]'” (KB 941457)

If you are working with Visual Studio using VB, C or C++ you can use the GPConn.dll or if using VB.Net or C# you can use the GPConnNet.dll to access SQL Server. To obtain instructions on how to use these objects along with Registration Keys, please log a Dexterity support incident via the link below (you will not be charged for the case):

https://mbs.microsoft.com/support

The GPConn.dll and GPConnNet.dll are already installed in this folder and its subfolders: C:Program FilesCommon Filesmicrosoft sharedDexterity.

 

The KB articles below provide more information about connections in v9.00:

How to upgrade applications to use the GPConn.dll file or the GPConnNet.dll file in Microsoft Dynamics GP 9.0 (KB 912960)

How to use the new RetrieveGlobals9.dll file in Integration Manager and in Microsoft Dynamics GP 9.0 (KB 913341)

The KB articles below provide more information about connections in v10.00:

Information about the new connection objects in VBA that replace the RetrieveGlobals.dll and RetrieveGlobals9.dll files in Microsoft Dynamics GP 10.0 (KB 936115)

Information about the new connection object in Integration Manager for Microsoft Dynamics GP 10.0 (KB 939371)

The following KB article is also useful reference for login issues:

Error message when you try to log on to Microsoft Dynamics GP: “This login failed. Attempt to log in again or contact your system administrator” (KB 919345)

 

Well, I think I have exhausted everything I can think of about passwords and encryption.

Post a comment to let me know if this information is helpful.

David

07-Oct-2008: Added section on Developing with Encryption.

29-Jan-2009: Added link to KB 919345.

16-Apr-2009: Added more information about using GPConn.dll and GPConnNet.dll.

09-Dec-2009: Follow up post: Do we really want Windows Authentication for Microsoft Dynamics GP?

15-Jun-2010: Added info on new Microsoft Dynamics GP 2010 feature to remember user name and password.

16-Jun-2010: Related post: Users (other than ‘sa’) unable to login after upgrade.

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.