Users (other than 'sa') unable to login after upgrade

A couple of nights ago, my friend Sivakumar Venkataraman (Siva) pinged me on Messenger about an issue he was having.  He was in the process of upgrading a customer's site from Microsoft Business Solutions - Great Plains 8.0 to Microsoft Dynamics GP 10.0, and after the upgrade none of the users were able to log into Dynamics GP. The only exception was the 'sa' user.

My first thought was ... Security.

The security model changed for v10.0 from the optimistic user and class based model to the pessimistic role and task based model.

If you do not use the Security Conversion Tool, then the users will not have any roles assigned to them and so have no access.  The exception would be 'sa' and 'DYNSA' as they would belong to the POWERUSER Security Role and so have access to everything. 

Note: My preference is to only use the Security Conversion Tool if you need to get the upgraded system running immediately and are not yet ready to implement the Security Role and Task based model.  In this case, I would suggest migrating to the Security Role and Task based model at a later stage and then removing the records created by the tool.

Anyhow, my first thought was ... wrong.  

Siva mentioned that the could not get passed the initial login window.  If this was an application level security issue, they would be able login but have no access to anything once logged in.

So then I thought about passwords and the encryption algorithm. 

This would make sense as 'sa' is not encrypted and was the only user still working.

The password encryption algorithm was changed with version 10.0.  This would not normally cause any problems as the v10.0 code would first try using v10.0 encryption, then try using v8.0/9.0 encryption and finally try with no encryption.  Once logged in, if the password was not using v10.0 encryption the user would be asked to reset their password, so the v10.0 encryption could be applied.  This is all good, so why was it not working.

Well, this method of checking for the different encryption methods had a side effect.  A single failed login by the user would actually register as 4 login attempts at the SQL Server level.  If the Password Account Lockout policy was being used with a common setting, such as 3 attempts.  A single typo while entering a password into Dynamics GP would see the user locked out.

So, to avoid this issue with the Account Lockout Threshold (without changing the setting to 12 attempts), Service Pack 2 of Microsoft Dynamics GP 10.0 was changed to only use v10.0 encryption by default.  For more information, see the Why does Microsoft Dynamics GP encrypt passwords? post.

So when Siva said that he was running v10.0 SP4, I knew what the issue was. 

The existing users had passwords encrypted using the v8.0 algorithm and the change in SP2 was no longer trying the v8.0 encryption.  If you want Dynamics GP to still try the legacy encryption methods, you will need to add the following Dex.ini setting:

SQLLoginCompatibilityMode=TRUE

Once this setting was in place, the users were able to login and reset their passwords.  Once all users have logged in once, I would suggest removing the setting so we can avoid the Account Lockout Threshold issue if using Password Policies.

For more information on the topic, please see Siva's blog post: Dynamics GP 10 Login Issue After Upgrade from Great Plains v8.

Also check out the Microsoft Dynamics GP Application Level Security Series of posts.

Hope this information will save you time and energy when doing upgrades.

David