Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Why does Microsoft Dynamics GP encrypt passwords?

Why does Microsoft Dynamics GP encrypt passwords?

Rate This
  • Comments 44

David MeegoWhy 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) Secure Link

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) Secure Link

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) Secure Link

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 Secure Link

The GPConn.dll and GPConnNet.dll are already installed in this folder and its subfolders: C:\Program Files\Common Files\microsoft shared\Dexterity.

 

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) Secure Link

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

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) Secure Link

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

There is also a thread on the getting a connection from Visual Studio Tools on VSToolsForum.com:

How to get connection data from VS Tools 

 

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) Secure Link

 

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.

  • i have problem that GP not working with such user on vista but before it was working fine

  • Hi Mansour

    If your issue is related to this post and the password has recently been changed, make sure the user ID is entered in the same case (UPPER & lower) as it was when the password was changed.

    If your issue is not related to this post, may I suggest using the newsgroups and forums for assistance or to log a support incident.

    Thanks

    David

  • What kind of encryption have GP10 passwords 256 bits, 128 bits, etc..?

    Thanks.

  • Carlos,

    Development has advised me "it's a secret" so I won't be able to tell you.

    patrick

    dev support

  • Patrick, we are implementing GP in a Bank and they want to know what is the kind of encryption (maybe some standar like 128 bits or other), they don't ask me about the algorithm. They want to know if it is easy to break.

    Thanks in advance.

    Carlos.

  • Carlos,

    I'm taking a guess at 256 but that is only a guess.  The reason I'd guess 256 is that the old method wasn't complex at all and we wanted to make the login much more secure.  So "much more secure" would lead me to believe 256 bit.

    patrick

  • A workaround for those who MUST change the ODBC server name.

    After recovering a Dynamics GP database server this weekend, I was in the position of needing to rename the database server to a different name due to the way that Active Directory was originally set up.

    So, of course, this breaks the GP encryption and would normally require me to reset every single user account's password.

    However, I found that you can create an SQL Server alias with the same name as the old server and GP doesn't know the difference.

    Here's how to do it.

    1) You'll need some version of Microsoft Data Access Components installed (MDAC).

    2) Run c:\windows\system32\cliconfig.exe

    3) Setup an Alias that points to the name of the old, missing server.

    4) Login to GP as usual and enjoy!

  • Hi Steven

    Thanks for your great tip.

    David

  • Hi David,

    I'm trying to determine the best way to accomplish the following:

    Complex passwords: 8chr, num & letter, cap & small

    password expires after 90days

    users reset own pw on expire

    can't reuse previous 4 passwords

    passwords diff from AD

    lock out after 4 failed attempts

    This is so easy in AD why not in GP?  We're on GP9.0 going to 10.0 soon.

    Thanks,

    Josh

  • On 10.0, you can set your users to enforce the SQL Password policy which inherits from your windows password policy.

    I think that would get most of what you are looking for.

    If not, then either you would have to code it yourself (vstools is probably the best approach) or purchase something already written.

    I think Omni Security from Rockton Software does this kind of thing.

    patrick

  • Posting from Ian Stewart on DynamicsGP.ie

    dynamicsgp.wordpress.com/.../gp-passwords-in-sql-really-great-post-from-david-musgrave

  • Posting from Mariano Gomez, The Dynamics GP Blogster:

    dynamicsgpblogster.blogspot.com/.../microsoft-sql-server-dsn-configuration.html

  • I am unable to log in to GP with my  SQL credentials when I am logged on to my AD account. The sa account can log in and so can any other user. If I use another machine or AD account I am able to login to GP fine.

    I need to know how to fix this without having to reinstall windows every time as that is the only thing that seems to get it working again. The fault seems to lie in the AD profile on the computer itself, but it doesn't occur straight away. It worked for a day and then this morning the error appeared again of not being able to log in. I switched to a different login on the pc and then I was able to access GP again but I cannot work from a different pc profile all the time.

  • Hi Rene

    I would suggest checking your 32 bit ODBC System DSN settings. Make sure that the SQL Server name is the same (case sensitive) as the other working machines.

    Also, make sure that the User ID is entered the same as on the working machines. It is also case sensitive as far as the password encryption algorithm is concerned.

    David

Page 3 of 3 (44 items) 123
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post