Why does Microsoft Dynamics GP encrypt passwords?

David Meego - Click for blog homepageThis 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.


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:


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):


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.


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.

49 thoughts on “Why does Microsoft Dynamics GP encrypt passwords?

  1. This was very helpful. Thanks for the informaiton.
    I’m working on an application that will work with the GP Database. I joined the Empower Program, but I can’t find anything that shows how to implement the encryption that GP 9 & 10 uses.
    Is there an SDK or something that I need to get that contains the methods I need?
    Thanks in advance.


  2. “For v8.00 and v9.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.”
    I think this is semi-correct for version 9. When I change a users password in SQL, I’m then able to use that userid and password from other applications. However, once that user logs back into GP, it prompts them to change the password and then, presumably, re-encrypts the new password. After that login, you are unable to use a login for the external application.


  3. David,
    This is a very useful article for me at least. Thank you so much for covering almost all major points related to Login and Encryption.
    I would like to highlight one such point, which I faced even today on my test environment:
    “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.”
    For some reason, I was able to find the solution, of changing the User’s Logon Info by logging on to GP as SA and edit the User Information. But I never got the reason why this would happen.
    Now it’s clear to the core.
    Moreover, I would like to put forth a query, with respect to Windows Authentication: Is there any important reason why GP is not yet converted to Win Auth method of logging in? Not all can afford to get solutions like FastPath’s and also if this is possible from within GP, that would be even more great.


  4. David,
    Thanks for a detailed post on this.  
    The one problem with this that GPConnNet.dll is now totally useless.  If you are developing integrations, I suggest you create a integrations user  who is a member of DYNGRP and run your queries under that user’s context.
    Problem is what to do about that user’s password.  
    Hard-coding those credentials into your addin is a bad, lousy idea but storing it anywhere just makes it accessible to anybody who wants it.
    I’m considering a complicated system , similar to a registration key.  The credentials are stored as two registration keys as clear text.
    one of those keys is a username and the other a password.
    The administrator is given the unencrypted username and password to set up the account in SQL server.
    For additional security the user id and or password can change periodically, but that may be overkill for a lot of users.
    Comments, anyone?


  5. David,
    Thanks for a detailed post on this.
    eConnect Connection is totally diff from GP authentication.
    eConnect not supporting SQL Authentication but GP not supporting the Windows authentication.
    Can you give some more about these statement?


  6. Thanks for the article…i am wondering if users are able to log into the Database usering their GP username/password and therefore, would they be granted access to all tables with full rights, through the DYNGRP role?


  7. Hi Darren
    You are correct. Once a user is logged in they will have full access to all tables at the SQL level. However, as they can only log in via the Microsoft Dynamics GP application, their access is now controlled by the application level security.
    Because of the encryption, they cannot log in from another application.


  8. David,
    Thanks for the posting! I get some catch up in security changes in concept from different version. I have some VB 6.0(Not VBA) applications using GP Version 6.0 security model. I have upgraded GP from 6.0 to 10 in dev server. I think that your posting gives me some direction to start to work on those VB Apps changes.


  9. Hi David
    Pre GP10 we had T-SQL scripts that allowed the transfer of logins and passwords so that we could restore from a Production GP environment to a Development environment. We used a similar script as the CaptureLogins script that is in PartnerSource for moving from one db server to another. i.e. we would restore DYNAMICS and all company database and then run the script. Then users would be able to access the Dev system using their same login and password.
    Now, in GP10, the password doesn’t work. And I can see why, given your comments that the server name is now included in the GP10 encryption algorithm.
    Is there any way to copy the GP10 logins and password now through a Transact-SQL script?


  10. Hi Konrad
    I don’t believe there is anyway to achieve this now with Transact-SQL only… without needing the passwords to be entered again.
    You could set the passwords to a chosen value(s) with T-SQL. As the passwords are not encrypted you will need to use the Dex.ini setting (v10.0 SP2 or later).
    Also, because they are not encrypted, the users will be asked to re-enter their passwords.
    Once all users have completed this you can remove the Dex.ini setting.


  11. kindly provide the GPConnNet.dll, i cannot find it anywhere on the mbs PartnerSource.
    i need to create login with the encrypted password so that MGP doesnt ask for change password at next login, from my .Net application.


  12. Hi Asad
    As mentioned in the blog post and in KB 912960, you will need to log a support case to request a registration key for the GPConnNet.dll. The case will not be charged.
    The GPConn.dll and GPConnNet.dll are already installed in this folder and its subfolders.
    C:Program FilesCommon Filesmicrosoft sharedDexterity


  13. Hi, i am using ILM (identity Lifecycle management) to synchronize users between Active Directory and MGP 9.0, we are able to create users for MGP as of now but the only problem we are facing is in password synchronization.
    When an ILM created user logs into MGP, the systems asks him to change his password which disconnects its password from Active Directory, we dont want MGP to ask for password change.
    Any solution?


  14. Hi Asad
    Have a look at the FastPath Solutions product.
    If that does not help I suggest logging a support case to get assistance from the US Tools team.
    Comments are not the best medium to solve technical issues.


  15. Hi,
    I’m on an issue on GP V9.00. When a user failed one time his login due to a bad password, his account lockout.
    I’m not the administrator of Dynamics GP, she’s pregnant and this issue has a critical impact.
    Please Help!
    JD Anderson


  16. Hi Jean-David
    This sounds like you have your account lockout thresheld set too low and are not using v10.00 SP2 or later.  As mentioned in the post, a single failure can generate 4 login attempts which will cause a lockout after one failure at the GP level when the threshold is set to 3.
    If you need support assistance to re-activate the login, please log a support incident… especially if this issue is critical.  Blog comments are not a good medium for handling support.


  17. Hi David,
    Thank for your answer, I have just need to know how I can set this setting. You’ve right i’m under version 9.00.
    I know how to re-activate login but when you have a dozen account lockout by day…


  18. Jean-David,
    Under 9.0 you cannot set the Dynamics GP setting.  
    This setting was added only for 10.0 SP2.  To change the lockout threshhold policy, you’d have to make a change to the policy of your Windows domain so that SQL uses that policy.  In your case, you’d probably want to set it to a larger value than you have now if you are getting a lot of user lockouts daily.
    For assistance on setting this policy, you’d want to contact Microsoft Customer Central under Windows support.


  19. Once again, thanks David.  We had instituted the new security features at numerous large clients and not had any issues.  One client strictly logged on using citrix for 25+ users and another logged in strictly by SQL Native Client.
    Now we have a client that when on site uses ODBC and when offsite users Citrix and the first day they went offsite and used Citrix and then came into the office.
    The next day they were locked out and the only way to resolve was to delete them out of SQL and then re-create them and then have them login and immediately change their password.  Of course this happened at month end and finally we just turned off their security.  The only thing we can think of is to create another userid for them when they are using Citrix.


  20. Hi Sue
    This sounds like the issue I described in this post where the name of the server is not the same on both ODBC setups.  It is very important that the server name is the same (including case) on both the LAN and Citrix clients.
    If the name differs, the encryption of the password will differ and so passwords will work from one but not the other.  If you fix it for the "broken" one, now the password will not work on the original workstation.
    Make sure they are the same and this problem goes away.


  21. Hi David,
    You saved my day :-)… the problem was bugging me since I joigned a new company that was using GP10 and SLQ2005 via Citrix TS. I had no issue with a local client, but all the new users on Citrix TS couldn’t change their password at the first login. Now I know why and changed the ODBC driver on the TS server side.
    Thanks as always and have a great time.
    Forensic Technology


  22. 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.


  23. 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.


  24. 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.


  25. 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:windowssystem32cliconfig.exe
    3) Setup an Alias that points to the name of the old, missing server.
    4) Login to GP as usual and enjoy!


  26. 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.


  27. 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.


  28. 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.


  29. 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.


Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.