Sensitive Data Security within TX

Hi ​@kerry_bespoke ,

There are four SQL Server  / Azure SQL features working in this terrain:

• Dynamic Data Masking: https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver17 and for Azure: https://learn.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview?view=azuresql . This masks data for unprivileged users
• Transparent Data Encryption: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver17 and for Azure: https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview?view=azuresql&tabs=azure-portal . This encrypts data at REST, data files for SQL etc.
• Always Encrypted: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver17 encrypt data inside the SQL Engine, aimed at allowing client applications to store sensitive data inside SQL without SQL administration having access.
• Always Encrypted with Secure Enclaves: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sql-server-ver17 alleviates some of the restrictions under Always Encrypted.

the tricky thing is that many operations in TimeXtender require dw_owner or higher rights in the databases you are working on. With dw_owner, you can bypass one of the ways to secure data in SQL Server: Dynamic Data Masking. You can also brute-force your way around the masking if you really want to. You may be able to set something up to remove the unmask rights from db_owner roles, but that may have unwanted side-effects.

Transparent Data Encryption only addresses the situation where someone gets access to the data files and tries to read those.

Always Encrypted sounds like the “perfect” solution, but comes with great restrictions on what is actually possible. I have never attempted to run TimeXtender against any variant of Always Encrypted, so I cannot say if it will work at all. If it does, you will run into pretty onerous limitations and need to be aware of all the consequences for situations like restoring databases. The use-case here is more: a service vendor hosts databases for a company that develops a software solution on those databases. Think a company developing accounting software on Azure SQL DB hosted by a different service vendor. They don't want IT / DB admins being able to touch the sensitive data and don't want Microsoft to be able to touch the sensistive data. The side-effects are acceptable due to the fact they are developing custom software and the stakes are high.

I usually approach this problem in the human space: developers work against test data, a restricted set of developers are able to promote to production or deploy there. This limits the exposure of real data without adding very heavy restrictions on what can be done. Everything depends on the compliancy context of course. Another solution is to carve out the really sensitive data and handle that in other systems: generate surrogate keys to identify the sensitive entities in a more secure environment that also contains the sensitive attributes and manage the less sensitive data in a TimeXtender-managed system. You could use Supernatural keys in this context. This does become very complex very quickly though and is only feasible if there is only a small set of sensitive data (some HR tables for instance).

If your metadata is also sensitive, then having access to the repository also becomes a problem. But then you are a really special case that would perhaps require a custom solution. In general, repository access is not a problem but data access is.