Are there any considerations I should have in mind when I patch or upgrade a TDE encrypted database? In the context of separation of duties, does the person executing the patch or upgrade process need access to the Keys or certificates of a TDE encrypted database or can the database be patched without the keys. The answer is important because we are a legal shop and would like an application administrator to encrypt the database and outsource the db maintenance functions to a dba to patch and upgrade the database without having access to the “application data”. Could you throw some light on this please?
-
I'm not sure what you're referring to when you say patch a database. You mean patching on the instance, for example applying CU4 or patching as in the end user application has a new version or patch? In either case, the encryption keys should at a minimum be backed up (whether you're patching or not) for recoverability and also be encrypted with limited high level employees that can allow access to them.– Sean GallardyCommented Mar 15, 2015 at 0:36
Add a comment
|
2 Answers
TDE only encrypts the data at rest. If they have the access to connect to the server and query the database then they can get to the data.
But I don't think the person running the patch (CU or Service Pack) needs permissions to the database proper, just permissions on the server.
The only way to separate the job functions as you seem to want would be to use the application to encrypt the data, that way it's actually encrypted in the database. Then even if someone has select rights to the database they can't read the data without the key. But that adds a lot of overhead and makes a lot of things harder.
answered Mar 17, 2015 at 20:39
I've used TDE in production many times and it in no way affects Windows/SQL Server patching unless there's some horrific bug or something of the sort; which has not yet happened.
TDE simply uses the Windows DPAPIs to generate a cert in Master that then is used to make other certs which can encrypt/decrypt the remaining DB data at rest.
Heads up though, if your DBA has SA rights, your DBA has all the keys and can back them up, then restore your backups or copy mdf/ndf/ldf files anywhere. What is the purpose of separation of duties in this scenario? Is it to give another team a 'backup operator' role and only let them deal with backups?
answered Mar 17, 2015 at 23:56
