Data Security / Encryption

I know that MySQL has a feature called Transparent Data Encryption (TDE). I don't have first had experience with using it so I'm not sure how "transparent" it would work in practice with Sugar but the docs claim that it should work without any changes to coding or schema modifications.

MySQL :: MySQL Enterprise Transparent Data Encryption (TDE) 

Sugar definitely still works if you encrypt the file system. That works totally transparently and helps address concerns that data is encrypted at rest. Using that in conjunction with HTTPS ensures that data is encrypted in transit as well.

Some things to remember about encrypting a database.  

1. Encrypting the file system is almost a useless endeavor.  It will only protect your data if the server is stolen and booted by someone who does not have the passphrase.  In order to use an encrypted volume, you must supply the correct passphrase when you mount it. Once mounted its contents are then visible to you as unencrypted data.  So anyone who hacks in, more than likely, won't be stopped.
2. TDE is only available on the commercial version of MySQL.  Not a complete deal breaker but something to remember.
3. I have no first hand experience with TDE either so I may be wrong here but TDE seems to have the same basic issue as file system encryption.  If they steal the actual files you are protected.  If they hack into your server while its active, then they can simply access the data the same way SugarCRM does.  
4. Encryption is great, but unless a passphrase or secondary key is required at every access then anyone with at least as much access as the web user will have access to the data.
5. The above things being said nothing is as important as actively maintained network security.  
   1. up to date patches on SugarCRM and the OS
   2. Firewalls, VPNs, Tripwires
   3. Aggressive password management rules (changed frequently and hard to guess) both on SugarCRM and the OS it runs on including the MySQL password which on most systems is changed once and never again.
   4. Audit, Audit, Audit and then audit the auditor.

Assuming it's on site, the best thing you can do is make it so that the database and web server are 2 different boxes.  Make the web server public, but firewall the crap out of the SQL server only accepting connections on port 3306 from the web server.  Even better, change the default 3306 port to something else.  Disable remote login as root and set a new user in MySQL that is only accepted from the IP of your webserver (which should be on the same network).  Then only grant it privilege for the sugar database it needs.  Dont give that user full access.  

Also set the SQL server firewall to only accept SSH connections from the IP you choose, or even better, disable SSH entirely and require a KVM to it or at the very least, dont leave it at port 22.  If you must leave SSH open, use a local ip or jump box of some sort.  This prevents file system access where an attacker could just download the MySQL folders locally and brute force it offsite.  

This does 2 things.  It hides your SQL server from the public and only allows Sugars web server to talk to it from a specific IP and ONLY to the table that holds sugar data. That keeps it out of the users table etc. There's not much need in my opinion to enrypt the SQL data after that.  Sugar's web server at that point should be the only vulnerability point and if Sugar is vulnerable, well, there's not a ton you can do about that except stay on the most recent version.

Just remember, your security is only as strong as the weakest link.  If someone can get on your web server then they can get to you DB server no matter where it is or how hardened it is.  It might be that obfuscation would be more helpful than encryption at the web server.

Agreed.  Pretty much everything I said about hardening the SQL server and keeping it private would apply to the web server as well.  HTTPS and only accept port 443.  Block port 80.  Same with SSH/FTP.  Only allow SSH/FTP on the private network  and get it off of port 22.  Pretty much one of the first, easiest and best steps you can take is restricting access by IP and changing the default ports on everything. Unless you are under a direct attack, just changing the port numbers bypasses the global port scanners that move from one IP to the next.  Changing port 22 to say, 2357 will thwart automated port scanners and make you less of a target.  I blocked everything about my instance except port 443 on the incoming.  I cant even SSH into it.  I have to VPN to a specific box on my companies LAN as a jump box to get to my web server, and the port isnt 22.    

Dear ted moriello, Matt Marum, Kenneth Brill,

Many thanks for your feedbacks ; I'm glad to see that we already manage security in a correct way.

But you also provide a lot of tips that we will explore.

One more time, many thanks guys !!! 

Fred from Switzerland

Good point Ken. This statement is off topic but Elasticsearch would be easier pickings if someone got shelled in.

for sake of completeness, Sugar self hosting tips include the recommendation to host Elasticsearch on separate server and apply restrictions so only CRM server can access it.

Also I believe AWS at least offer encryption at rest for databases?

I'm looking for "encryption at source" with respect to protecting PII from a cyber security insurance perspective. I completely agree with the recommendations already given regarding best practices, but I'm specifically being asked if my PII is encrypted at source by the insurance company. 

Is TDE my best option? Does SugarCRM not directly support some way to do this by flagging specific fields that contain sensitive data that I'm not aware of? 

Thanks in advance for any help on this. 

Hi Brian Jones,

Have you looked into our Data Privacy guide? We do provide some guidance on PII.

It's a very good starting point other than all the other, more technically advanced suggestion by the community.

Some more info about our security is here.

rafa