Question TDE and autostarting DBs

Hi all

Its been a while hope the community is well...

I'm looking into TDE encryption and wondered if anybody specifically had advice regarding using it and autostarting the DBs in a nice / but secure way.

I don't want to set to set the DB itself to autostart - as this clearly makes TDE pointless (or have I missed something?)
If using manual then the passphrase has to be passed on a command line or prompted for.

Most of the customers I work with are small / medium size businesses and the IT staff broadly speaking have little interest in learning Progress, and for customers with 3rd party hosting this is magnified.

So ideally I'd still like to achieve a way of when a VM is rebooted that the DBs and systems etc startup themselves. But obviously we have to encrypt and protect against said DBs (or even the whole VM) being copied / pasted and looked at elsewhere.

Happy for any points of view or experience you have.



Rob Fitzpatrick Sponsor
Encryption isn't security.

That sounds self-evident. But sometimes people get lost in the weeds debating the how, e.g. ciphers and modes and key lengths etc., and lose sight of why they are encrypting data, i.e. how it serves the cause of security. I have seen systems where lots of time and money was spent to implement encryption, only to leave the keys unprotected. The result was zero increase in real data security and people left with a false impression that meaningful security was in place.

When making the decision to use encryption, whether it be in an application client, a database, an OS, a network path, etc., it is a good idea (and should be a business requirement) to formally define why it is being used. In other words, to define the threat model and how this use of encryption will mitigate some threat. And also to define the policies and procedures that will mitigate the threats posed by the use of encryption, e.g. performance impacts, data loss, key compromise, etc. It is very important for anyone dealing with symmetric encryption to understand best practices in key life-cycle management.

The model should also define what data is sensitive and what is not. If your database has 500 tables and 100 of them contain configuration info or meta-data or other things that are not sensitive (however you define that), should you encrypt all 500? Probably not. What if your application has multiple databases, and one or more contain no sensitive data at all? Should you impose the risk and overhead of encryption on them because you're encrypting the other databases?

But obviously we have to encrypt and protect against said DBs (or even the whole VM) being copied / pasted and looked at elsewhere.
This is part of a threat model and it is a valid use-case for TDE. It is also worth stating that the concern shouldn't be just database encryption, but rather data encryption. In other words, for data defined as sensitive, think about all the places where it exists: in records, maybe in index keys, in BI notes, in AI notes, in database backup files, in binary dump files. It's not just in data extents. So take that into account when deciding how to implement optional features (e.g. binary dump encryption, AI and BI encryption, etc.).

I don't want to set to set the DB itself to autostart - as this clearly makes TDE pointless (or have I missed something?)
As pointed out above, I don't think so. If someone breaks into your data centre and steals your servers or your backup media, you're in much better shape if the database is TDE-enabled. Those files are useless to them if the blocks are encrypted and they don't know one of the passphrases. Whereas a with stolen unencrypted database or backup, even without application or database credentials, a determined attacker can extract a lot of data; maybe all of it.

I don't know if autostart is obviously worse than manual start, in terms of practical security. They have different trade-offs, different places where security boundaries exist. Manual start might require you to alter your application to prompt for a passphrase, depending on how you start your database. Even if database start is done manually, it still requires the person issuing the proserve to enter the passphrase. That means potentially many more people (e.g. operations staff on rotating shifts) might need to know at least the user passphrase, as opposed to using autostart. How will they handle that? Will they memorize it? Very doubtful, if the passphrase is suitably strong. Will they write it down? Store it in a file? If so, where is that file stored and how is it protected from unauthorized access or copying? Can you prove to an auditor that its contents don't exist elsewhere? Does your employee-termination policy call for changing the user and admin passphrases? If the function is outsourced, do you even know when there is employee turnover?

The passphrases are the keys to the kingdom. If the passphrases are compromised, any security provided by TDE is lost. If the passphrases are lost, the impact depends on keystore configuration. With manual start, you can never open your database again. With autostart, you can at least open your database and read the data to be able to create a new database from it.

So ideally I'd still like to achieve a way of when a VM is rebooted that the DBs and systems etc startup themselves.
Different situations have different needs. Some people run a 24/7 application and have 24/7 operations staff, either working or on call. Or both staff and application are business hours-only. In those cases, manually starting databases is feasible and, perhaps, preferable. I like to see my databases come up and know that they aren't throwing unexpected errors. But of course, this approach isn't always feasible. You might have no after-hours staff. Or be working at such scale that manual intervention isn't cost-effective. In these cases you may need to automate to meet an SLA.

So you can use cron jobs in Unix or the OE Admin server on Windows (or Unix). If you intend to use Admin server to autostart your managed TDE-enabled database, you cannot configure it for manual start of the keystore. If you intend to use scripts, again you have to store your passphrase and that has its own issues.

Another area to consider is implementation and maintenance. You know how to deploy and configure your application databases, and how to do tasks like backup and dump & load; you may have scripts to automate those things. Take time to consider how the use of TDE should change your logic, if applicable. For example, does your D&L process dump and recreate encryption policies; does your database backup script also back up the .ks file; do you know when and how to rebind the keystore to the database; etc.

Rob Fitzpatrick Sponsor
Happy to help. For the most part, the docs are pretty good for TDE, at least for theory and syntax, so I encourage a full read-through. I'd say they are somewhat lacking in operational details. I suggest practicing with sports and go through your deployment and maintenance tasks. Try a D&L, backup, restore, procopy, etc., to feel confident you know how TDE impacts everything you do. Good luck!
Yes - that's just it. I've read a lot of the docs - found a great old powerpoint from PUG 2011 or thereabouts - but as you say nothing about operational.

I get the feeling docs like this are aimed at dedicated DBAs etc. Whereas the company I work for most customers run sub 10GB databases on small VMs and want the software to look after itself :)