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.
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.
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.
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 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)
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)
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)
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.
Posting from The Dynamics GP Blogster
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.
Posting from Mohammad Daoud
Posting from DynamicAccounting.net
"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.
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.
You are correct, I will update the post accordingly.
Amazing collection of information - thank you for putting this together!
Posting for Vaidy Mohan's Blog
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.
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?
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?
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.
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.
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?
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.