I use the term “encryption” loosely in this article. As you read on, you’ll realize why…

I’ve been doing some work on a plugin for Microsoft Dynamics GP, which is an accounting system aimed at Medium sized to Large businesses. To give you an idea of what type of application this is: There are companies that pay somewhere around $10,000-$15,000 to consultants or VARS (Value Added Resellers) to implement a Microsoft Dynamics GP solution for their business. Many of the VARs have their own plugins and solutions for Microsoft Dynamics GP, usually written in .NET or Dexterity. The process of installing and maintaining GP is an industry all it’s own and it’s not cheap for a company to maintain this accounting system.

I’ve been searching for the “encryption algorithm” or at least some way other way to “encrypt” data in GP in some other way than within Dexterity code. I was really hoping that there would be some .NET library that would do this for me, but I was never able to find anything that would help me do this. So, I became interested in what type of “encryption” this is. Somewhere (I can’t remember where) I found something that indicated that the it’s a symmetric key encryption algorithm. The message boards were not much help either. Anywhere I went, I basically saw this same type of statement, “the encryption algorithm is a closely guarded secret”.

Today, while doing some testing, I noticed something with data that we were saving to a field which utilizes the GP “encryption”. The plugin I was testing puts data in an encrypted field (not that it needs to because it’s not sensitive in nature), and I was testing with the same values each time. As I would expect, I saw the same data stored in the field in the database for each row in the table. However, I noticed that one of the entries was different, by 2 characters. That seemed very odd to me. After looking at it some more and conducting some more tests, it looks like I simply miskeyed my test data, but it prompted me to take another look at this.

After trying a couple different combinations of test data, it became very obvious that changing only one character in the test data appeared to only alter 2 characters of the encrypted data. So I ran through a battery of tests, and came up with this:

’1′=’DF’, ’2′=’DC’, ’3′=’DD’, ’4′=’DA’, ’5′=’DB’, ’6′=’D8′, ’7′=’D9′, ’8′=’D6′,
’9′=’D7′, ‘A’='AF’, ‘B’='AC’, ‘C’='AD’, ‘D’='AA’, ‘E’='AB’, ‘F’='A8′, ‘G’='A9′,
‘H’='A6′, ‘I’='A7′, ‘J’='A4′, ‘K’='A5′, ‘L’='A2′, ‘M’='A3′, ‘N’='A0′, ‘O’='A1′,
‘P’='BE’, ‘Q’='BF’, ‘R’='BC’, ‘S’='BD’, ‘T’='BA’, ‘U’='BB’, ‘V’='B8′, ‘W’='B9′,
‘X’='B6′, ‘Y’='B7′, ‘Z’='B4′, ‘a’=’8F’, ‘b’=’8C’, ‘c’=’8D’, ‘d’=’8A’, ‘e’=’8B’,
‘f’=’88′, ‘g’=’89′, ‘h’=’86′, ‘i’=’87′, ‘j’=’84′, ‘k’=’85′, ‘l’=’82′, ‘m’=’83′,
‘n’=’80′, ‘o’=’81′, ‘p’=’9E’, ‘q’=’9F’, ‘r’=’9C’, ‘s’=’9D’, ‘t’=’9A’, ‘u’=’9B’,
‘v’=’98′, ‘w’=’99′, ‘x’=’96′, ‘y’=’97′, ‘z’=’94′, ‘!’='CF’, ‘@’='AE’, ‘#’='CD’,
‘$’='CA’, ‘%’='CB’, ‘^’='B0′, ‘&’='C8′, ‘*’='C4′, ‘(‘=’C6′, ‘)’='C7′, ‘ ‘=’CE’,
”=’20′, ”=’00′,’-'=’C3′, ‘_’='B1′, ‘=’='D3′, ‘+’='C5′, ‘['='B5', ']‘=’B3′,
‘{‘=’95′, ‘}’=’93′, ‘\’='B2′, ‘|’=’92′, ‘;’='D5′, ‘:’='D4′, ””=’C9′, ‘”‘=’CC’,
‘=’='C2′, ‘<’='D2′, ‘.’='C0′, ‘>’='D0′, ‘/’='C1′, ‘?’='D1′, ‘`’=’8E’, ‘~’=’90′

Yep, it’s basically your run-of-the-mill Substitution cipher. The worst part, there’s evidence all over the place that this was a VERY weak encryption algorithm for awhile, but nobody seemed to pay any attention to it when people were asking how they could reset passwords of users in the database (Post 1Post 2)

I did some more searching, because there is ABSOLUTELY NO WAY THAT I AM THE ONLY ONE THAT SAW THIS… I found a good write up on the MSDN blogs that explains pretty well how the GP encryption was used (here).

The article is evidence to support a theory that I have, which is after GP moved to SQL server authentication, the encryption method didn’t seem to be needed any longer so they never replaced. I don’t know if the word was released to developers and integrators that the “encryption algorithm” wasn’t ideal for storage of sensitive information, but I don’t know how many plugins or customizations use it either.

EXCEPT…. Microsoft still uses it for their GP system password, which is the password needed to get to the Security Roles/Tasks and all the User Security related forms while in GP. What’s even worse, if you create a new user, you have to give the user explicit rights to the company or companies you want the user to access, but you DON’T HAVE TO GIVE ACCESS TO THE DYNAMICS DATABASE. What that means is if you create a base user in GP, that user can log into the SQL server and run select AND INSERT statements on tables containing the “encrypted” GP System password and Security Roles… Not good… I must correct this and clarify. By default, GP gives the user access to the DYNAMICS database but the user CANNOT login to the SQL server using SQL Enterprise Manager. Here’s what happened: I reset the LESSONUSER’s passwords with SQL Enterprise Manager and afterward I was able to login to SQL Enterprise Manager with the LESSONUSER’s credentials. Some flag most have been updated when I reset the password – I need to investigate this further (this was all done in a Test environment). This was a BIG oversight on my part and I apologize for this. I really should have tested this out more before posting that statement. (Thank you Mark and others that pointed this out to me).

I created a function that you can use to decrypt GP “encrypted” data. You can find it here (link is working again). If you create the function on the SQL server, you can then retrieve the Master password by running this query:

select dbo.[fn_Decrypt_Value] (PASSWORD, 16) from DYNAMICS.dbo.SY02400

Another point I need to clarify, as Mark pointed out, this master password is just a secondary password that is OPTIONAL. THIS PASSWORD DOES NOT GIVE YOU ACCESS TO GP. If you have this password set, the user attempting to access the Security Roles/User Forms will need to enter it. This is not a very secure way to store this password, but it is not the primary form of authentication to GP.

I did ALOT of searching to see if anyone had reported this in the past, but I found no indication of this ever being found. I don’t know if this is a “REAL” discovery, but I would think it’s worth knowing. I hope you found this informative!

This was originally meant to be a disclosure that the Microsoft Dynamics GP Dexterity Encryption algorithm was weak and has been broken. The retracted statement was an oversight on my part and I’m sorry for this.

Other References:

Microsoft Dynamics GP Table Structure Overview

Tagged with:

10 Responses to Breaking the “encryption algorithm” for Microsoft Dynamics GP – Dexterity Encryption

  1. Mark Polino says:


    You didn’t do that much research. You’ve confused the SYSTEM password with the SYSTEM ADMINISTRATOR. The SYSTEM password is an OPTIONAL component of security. If users don’t have permission to a resource the SYSTEM password won’t get them there. It was designed as a secondary defense, not a primary one.

    Secondly, you’ve confused access to GP companies with access to SQL tables. Security in GP provides access to company databases via the interface. That’s why you don’t have to grant access to the Dynamics databases, it’s shared among companies. None of this grants SQL level access to the tables.Your statement that a base GP user has direct access to SQL tables is simply false. It’s flat out wrong. Even a basic GP user could have told you that. You could have used the GP demo site to test that without installing even installing anything. I expect to see that statement publicly corrected.

    Your research examples are a mixture of the SYSTEM password and user login passwords. They are not the same thing and they don’t use the same encryption. A number of people including myself have publicly shown how to reset the system password. It’s not a big secret.

    There are probably a dozen well know Dynamics GP blogs, forums and newsgroups that you could have asked this question on before publicly making incorrect statements. Next time, drop me an email, I’ll help you out.

    Mark Polino, CPA, CITP, CFF
    Microsoft MVP – Dynamics GP
    Microsoft Certified Business Management Professional

  2. Popbus says:

    I saw and responded to your article on Slashdot as well, but I’ll expand on here, because I think you’re misunderstanding the overall design of Dynamics GP.

    1. You cannot create a user within Dynamics GP or grant company access unless you are logged in as ‘sa’ or ‘DYNSA’. Anyone else with access to the user-creation window will see the ‘Save” button greyed out. When users are set up within the system, they always have access to DYNAMICS (they need it for the system to function)

    2. You can set up a SQL login for a user, and grant that user access to company databases and give that user the DYNGRP role. If you have security access to the SQL server, then you can do this easily enough. If you work for me and you do such a thing, you will be fired. End of story.

    3. Users set up within GP are given access to the Dynamics databases and are granted the DYNGRP role, so yes, they technically have access to the whole system. It doesn’t do them much good outside GP, though, because their password is encrypted. The encryption used is a very strong encryption method. Microsoft does not release information on the key used, as there are other tools for customizations, reports, etc, that help deal with this.

    4. Security within Dynamics GP is much more granular than is available within SQL server, this is why nothing is encrypted and why the system is setup the way it is. You’re following a pink elephant trying to work out a decryption mechanism for something as benign as the System Password. I’ve seen many MANY secure GP isntallations where everyone and their dog knows the system password (hell, the vast majority still use the default password of ‘ACCESS’). The systems are secure, nonetheless, because the proper security roles and tasks have been put in place by those who implemented it.

    I’m very curious to hear what kind of plug-ins you are trying to develop for Dynamics GP, what the business use for those are, and your experience with Dynamics. From the sound of your article, you may be wise to do some research in the system and the recommended tools available (ie eConnect) for integrating data into the system, as you could very easily hose it. Accounting systems are not something you want to be messing around with if you don’t understand how they work.

  3. It’s LANMAN all over again from MS, lol

  4. Chris says:

    Thank you for the comments. I was wrong with regards to the user having direct access to the SQL Server using SQL Enterprise Manager. I apologize for this oversight. I thank you for correcting me.

    Despite that retraction, this still doesn’t change the fact that the Microsoft Dynamics GP Dexterity Encryption is VERY weak and should not be used to keep any sensitive information a secret.

  5. Chris says:

    I submitted a correction to Slashdot in hopes that they will remove the misleading statements and the false statement that was keyed in on when the article was submitted.

    I am hoping they will update the post.

  6. Oh my, oh my, what have you gotten yourself into… I am not here to rant, but rather to provide some council as you clearly have bargained for more than you could handle. Even my 15-year old has grasped the concept of “read before you post”. C’mon, I answer close to 50 questions a day on 4 forums, 2 public, 2 private and run one of the most widely read blogs on the subject of Microsoft Dynamics GP. You could have simply dropped me an email (like the many I receive a day asking for a free consultation) on how the passwords in GP work…but no! I guess you had to discover it all by yourself AND make it public. I guess I don’t blame you, as this is just sometimes human nature. But judgement… now, that’s a whole different ballgame.

    I have posted 7 facts you should at least try to read… again, try to read… they will help you in uncovering some more things about GP.

    Mariano Gomez, MVP, MIS, MCP, PMP

  7. Chris says:

    First off, thank you everyone who has posted comments and contacted me about this post. I never intended to post any false information and I’m glad any false or misleading statements have been pointed out and subsequently corrected.

    I think there’s a BIG misunderstanding here regarding encryption of GP user passwords. I NEVER stated that I broke the encryption used for passwords of GP users. In fact, I stated that I didn’t know how much the built-in Dexterity encryption was used anymore and that I found it used in only one place, the system password. I should have clarified what the system password did at the time of first post, which is to provide secondary authorization and does NOT allow you to login to GP (this was an oversight on my part and I have since I apologized, corrected my post, and contacted anyone who keyed in on those statements as an issue when they picked up this article and posted it to their own site). The false statement regarding the GP user having the ability to login directly to the SQL server has been retracted (I’m very sorry about this and I should have done more testing prior to disclosure). Slashdot has since updated their post.

    The ORIGINAL title of the article still stands, the built-in Dexterity encryption has been broken. If anyone is using this encryption to store sensitive information, they should re-evaluate their solution. As I originally stated, I don’t know if this is a “REAL” discovery (it sounds like many in the GP community knew it was weak already, but have not directed me to evidence that suggests it had been completely broken in the past). If you have a link to an article that shows it was broken in the past, I will link it into my post and claim that I really didn’t find anything. However, I haven’t found such an article.

  8. For those who want the full scoop on how Dynamics GP handles the application system password, please refer to our official blog here: http://blogs.msdn.com/gp/archive/2010/05/22/the-scoop-on-dynamics-gp-s-application-password-system.aspx


    The Dynamics GP Team

  9. [...] to earlier reports which have since been corrected on this Blog there is not a security vulnerability in Microsoft Dynamics GP. Further detail can be found [...]

  10. [...] Christopher Kois, a blogger, has posted details of another lousy encryption technique, used by Microsoft’s Dynamics GP business accounting [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Set your Twitter account name in your settings to use the TwitterBar Section.