Why does Microsoft Dynamics GP encrypt passwords?
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.
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:
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.
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 Files\Common Files\microsoft shared\Dexterity.
The KB articles below provide more information about connections in v9.00:
The KB articles below provide more information about connections in v10.00:
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:
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.
Comments
Anonymous
October 06, 2008
Posting from The Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2008/10/microsoft-dynamics-gp-password.htmlAnonymous
October 06, 2008
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.Anonymous
October 06, 2008
The comment has been removedAnonymous
October 06, 2008
Posting from DynamicAccounting.net http://msdynamicsgp.blogspot.com/2008/10/dynamics-gp-encrypted-passwords.htmlAnonymous
October 06, 2008
"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.Anonymous
October 06, 2008
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. Vaidy http://vmdyngp.blogspot.comAnonymous
October 06, 2008
Thanks Steven You are correct, I will update the post accordingly. DavidAnonymous
October 07, 2008
David, Amazing collection of information - thank you for putting this together! VictoriaAnonymous
October 07, 2008
Posting for Vaidy Mohan's Blog http://vmdyngp.blogspot.com/2008/10/gp-password-encryption-details.htmlAnonymous
October 08, 2008
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? HSAnonymous
October 09, 2008
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? Regards JeganeedhiAnonymous
October 27, 2008
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?Anonymous
October 27, 2008
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. DavidAnonymous
December 03, 2008
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. Regards JieAnonymous
February 05, 2009
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? Regards KonradAnonymous
February 05, 2009
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). SQLLoginCompatibilityMode=TRUE 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. DavidAnonymous
April 16, 2009
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.Anonymous
April 16, 2009
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 DavidAnonymous
April 16, 2009
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?Anonymous
April 17, 2009
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. Thanks DavidAnonymous
April 18, 2009
Posting from the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2009/04/microsoft-dynamics-gp-10-poweruser-role.htmlAnonymous
May 27, 2009
Many people who have not been working with Microsoft Dynamics GP since the early non-SQL versions ofAnonymous
June 26, 2009
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! Regards JD AndersonAnonymous
June 28, 2009
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. Thanks DavidAnonymous
June 30, 2009
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...Anonymous
June 30, 2009
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.Anonymous
July 07, 2009
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.Anonymous
July 07, 2009
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. DavidAnonymous
August 03, 2009
This is excellent information. Thank you very much.Anonymous
August 18, 2009
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. Beat Forensic TechnologyAnonymous
January 18, 2010
i have problem that GP not working with such user on vista but before it was working fineAnonymous
January 18, 2010
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 DavidAnonymous
February 16, 2010
What kind of encryption have GP10 passwords 256 bits, 128 bits, etc..? Thanks.Anonymous
February 16, 2010
Carlos, Development has advised me "it's a secret" so I won't be able to tell you. patrick dev supportAnonymous
February 16, 2010
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.Anonymous
February 17, 2010
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. patrickAnonymous
March 01, 2010
The comment has been removedAnonymous
March 02, 2010
Hi Steven Thanks for your great tip. DavidAnonymous
May 02, 2010
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, JoshAnonymous
May 03, 2010
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. patrickAnonymous
October 04, 2010
Posting from Ian Stewart on DynamicsGP.ie dynamicsgp.wordpress.com/.../gp-passwords-in-sql-really-great-post-from-david-musgraveAnonymous
February 14, 2011
Posting from Mariano Gomez, The Dynamics GP Blogster: dynamicsgpblogster.blogspot.com/.../microsoft-sql-server-dsn-configuration.htmlAnonymous
April 12, 2012
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.Anonymous
April 12, 2012
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