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

FULL DISCLOSURE – I HAVE UPDATED THE ORIGINAL POST AND HAVE ADDED COMMENTS IN ITALIC AND RETRACTED OTHERS WITH A STRIKETHROUGH

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 1 – Post 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