Microsoft Dynamics GP "Encrypted" Using Caesar Cipher 206
Posted
by
kdawson
from the no-safety-in-numbers dept.
from the no-safety-in-numbers dept.
scribblej writes "Many large companies use Microsoft's Dynamics GP product for accounting, and many of these companies use it to store credit card numbers for billing customers. Turns out these numbers (and anything else in GP) are encrypted only by means of a simple substitution cipher. This includes the master system password, which can be easily selected and decrypted from the GP database by any user. Quoting: '[Y]ou 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 a select statement on the table containing the "encrypted" GP System password. Not good.'"
Update: 05/22 02:57 GMT by T : The original linked post has been revised in a few places; significantly, the following has been added as a correction: "By default, GP gives the user access to the DYNAMICS database but the user CANNOT login to the SQL server using SQL Enterprise Manager."
Incredible. (Score:5, Informative)
So, this Microsoft product uses what amounts to the same "encryption" that the CVS pserver protocol uses. Hilarious.
Re:obligatory (Score:5, Informative)
You need to use the vocative case there, not the nominative.
If a wordpress site dies and noone is around? (Score:5, Informative)
Here's a text only cache [googleusercontent.com] of the page.
Full Article (Score:5, Informative)
Sorry... I didn't expect /. to pick this up, and didn't really warn Chris Kois that I'd submitted it. My fault.
Below is the original article:
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:
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 a select statement on the table containing the "encrypted" GP System password... Not good...
I created a
Re:But... (Score:5, Informative)
Ohg vg'f jnl zber frpher gung jnl
But it's way more secure that way
(mad cryptoquote skillz)
Re:obligatory (Score:2, Informative)
Yes, you are. http://www.imdb.com/title/tt0079470/ [imdb.com]
Whoosh
PCI-DSS (Score:4, Informative)
Re:obligatory (Score:4, Informative)
Re:Most ERP systems do not have the data encrypted (Score:4, Informative)
You should never give any direct access to your ERP database to anybody
That's slight overkill. I would encourage you to create proper database users, and grant them select/update/insert/delete rights only as appropriate. If you need per-column permissions, create views that hide those columns, and if they need read/write access, provide instead-of triggers on those views to support their needs.
The main reasons I would encourage you not to let users have direct access:
1) Users don't know what they're seeing, they don't know which lookup tables to join to, or they don't understand how the data's organized. They'll write their own reports, come to the wrong conclusions, convince management of their erroneous beliefs, and you'll have to clean up the mess. "I got my data from the database" shouldn't be good enough.
2) Most ERP products (really, most database-backed products) are not built to keep themselves truly logically consistent without the help of some outside application layer. There are lots of reasons for that: developers are taught that databases are just for storage, they don't want to learn procedural SQL, they're trying to be database-agnostic the only way they know how, ... Giving users write access means they can easily get all the data out of synch (I don't just mean foreign keys here, thank you) by performing only half of a complex operation the application layer would have guaranteed fully done.
Re:obligatory (Score:3, Informative)
What makes this scene even funnier to me is John Cleese was a teacher at one point.
Re:Most ERP systems do not have the data encrypted (Score:3, Informative)
Classical ciphers, in discussions about modern computing, can't reasonably be considered on the same footing as modern ciphers. Using a classical cipher is no better than not using a cipher at all, hence no encryption.
But hey, this is slashdot where pedantry passes for insightfulness, so what the hell.
Re:But... (Score:3, Informative)
Re:Most ERP systems do not have the data encrypted (Score:4, Informative)
Yes but this is GP we are talking about there really is no "Application Server" the clients all connect to the database! The users running the client therefore must have access to connect to the database and do DML queries on many objects. Any users that actually need to run the application and not some limited web front end you have built or something are SQL users. The only real workaround is to only allow database connections from selct hosts and have one of those hosts be a terminal server. The best part is the GP application has lots bugs when running under terminal services!
Re:My guess is ITAR, the market and standards (Score:3, Informative)
Not that long ago, competent security was a criminal offense to export. It still is, unless the code is Open Source (and we all know how Microsoft loves Open Source).
I'm sure as heck no Microsoft fan, but they've been exporting strong cryptographic components for a long time now, and not in an open source format. Please reference the following materials for further guidance on this topic:
Export of cryptography in the United States [wikipedia.org]
International Traffic in Arms Regulations 2009 [state.gov]
Sure, you can't export this stuff to Iran, North Korea, etc, but there are very few real obstacles aside from that. This is pure and simple failure on Microsoft's part, on the most basic level imaginable concerning data protection.
Re:::gasp:: (Score:4, Informative)
Microsoft Dynamics GP used to be Great Plains Software. It was purchased by Microsoft in 2001.
The security is a relic of the program originally created by Great Plains Software. Although Microsoft should have fixed this, it was never Microsoft's idea in the first place.
MS is working on integrating GP with Active Directory.
I'm all for MS Bashing, but seriously...
Who do people blame for Flash? Adobe...but it was Macromedia (or SmartSketch if you want to go way back) that unleashed the plague upon the human race...
Re:obligatory (Score:2, Informative)
Re:Microsoft's latest encryption (Score:3, Informative)
I disagree with your implementation of the Igpay Atinlay algorithm as described in RFC PL.
"They" is properly encrypted as "Ey-thay", as "th" is a single phoneme.
Of course, if you're sticking to the MICROSOFT implementation of going simply with orthographic characters, and you want to be non-standard with proper implementations, then go ahead.
Re:obligatory (Score:2, Informative)
Re:Not a substitution cipher (Score:3, Informative)
Re:But... (Score:3, Informative)
If you're using any sort of skill to decipher that, you're doing it wrong.
http://www.rot13.com/ [rot13.com]
Re:andnothingofvaluewaslost (Score:5, Informative)
I have the displeasure of working with Great Plains regularly, and this isn't surprising at all.
A couple of points for the panic stricken:
1. Great Plains uses SQL logins and it hashes the passwords of users created from within GP. Since 9.0, it salts this hash using the sql server name. A GP user other than sa can NOT login to SQL Enterprise Manager with their GP credentials. That encryption has NOT been broken (yet). (That WOULD be a real problem.)
2. The ability to decrypt the System password is useless if you can't query the system password from the database. If your users have the ability to query any table in the database directly, then you have a bigger problem than weak encryption.
3. GP overlays role and task based security on top of the SQL login mechanism. Having the decrypted System Password is less useful if your application user doesn't have the ability to reach the User Setup or Security Options menus. These menus should be turned off for everyone not in the GP PowerUser role.
Is this great for GP? No. Neither is it the harbinger of the apocalypse.
-ellie
Re:PCI-DSS (Score:4, Informative)
I don't believe that PCI mandates "good" encryption, just encryption.
As of 1.1 (the only document I have handy), the requirement is "Strong cryptography, such as Triple-DES 128-bit or AES 256-bit". I'm sure it only got more stringent after that.
Re:Not a substitution cipher (Score:2, Informative)
The software in question was bought from a company called Great Plains (hence the "GP" in the name) out of Fargo, North Dakota. Likely this system of encraption was already present in the software before Microsoft bought it. So it's probably not really a matter of incompetent Microsoft developers (for once), but a matter of management refusing to allow the developers to reimplement it.
I presume the author of the article was using 9.0 and not the recently released 10.0. Even so, this software was purchased by Microsoft in 2001 and version 9.0 was released in 2005. There's no excuse for it not being addressed in the interim.
Re:*** Irony, Microsoft did use XOR *** (Score:3, Informative)
Learn something everyday (Score:2, Informative)
In emacs you can use M-x rot13-region (or rot13-other-window)
Or for those of you who want to make fun of emacs:
C-SPC C-e M-x rot13-region
Re:Full Article (Score:4, Informative)
About your AD comment, it's been brought up, but AD isn't the be-all-end-all of security.
From:
http://blogs.msdn.com/developingfordynamicsgp/archive/2009/12/09/do-we-really-want-windows-authentication-for-microsoft-dynamics-gp.aspx [msdn.com]
"One major drawback to switching authentication modes is audit trails. AX and SL have made the move from SQL auth to Windows Auth. In doing so, they destroyed the audit trail at the database level. All DB updates are made by one user making it a massive challenge to determine who made a change to financial data. Many of the AX and SL companies we work with get dinged for this in each audit."