RE: Encryption best practice for a Staging to Data store solution.

SSC-Addicted

Points: 436

July 27, 2017 at 7:20 pm

#1953136

jasona.work - Wednesday, July 26, 2017 11:28 AM
Sounds like you're taking the "at-rest" not entirely correctly when it comes to TDE.
When Microsoft states TDE encrypts your data "at rest," they're talking about the *entire* database file (both mdf, ndfs if you have them, and ldfs) What this means for protecting your data is, if I walk into the office, stop SQL Server, take a copy of the database files and run off, I *will not* be able to open those database files with SQL (I won't be able to attach the DB.) The same thing happens with a backup of the database. In both cases, without the certificate used to encrypt the database, it *can not* be attached / restored by anyone.
TDE also does not prevent anyone from being able to view the data in a database if they are able to login to the SQL instance and have access to the database and tables. For instance, if you have a TDE protected payroll database, to which I have db_datareader, I *will* be able to view people's salary information, it will not be encrypted.
Now, as for problems with databases that aren't TDE-protected on the same instance, I believe the issue has to do with TempDB being encrypted as well as any DBs you enable TDE on. This can give a performance hit, although how bad would largely depend on how TempDB is used in your environment.
I am not familiar with Always Encrypted (still working to get on SQL2014,) so I can't comment there. But, in general, column encryption should give you the protection you're looking for, something to prevent people from viewing data they shouldn't, as well as protecting it in the event of a data breach.

Thanks for the replies!

Before anyone twigs, this is a system that needs to be PCI DSS compliant. TDE was suggested by one of our DBA's (I'm a developer) as an option, but I am not sure whether this will tick all the boxes all the way.

In regards to the staging database, I could potentially lock it down to only SA and a required service account(s). The Staging database is simply that - I need to get the data into SQL Server some how and this would be the first point. Other processes would then consume and convert the data. The plan was to drop all tables in the staging database as soon as the data has been loaded further down stream. TDE was to be implemented, purely if there is a failure or a delay and to tick a box for the auditors that says "at rest"....... I would dearly love to implement always encrypted between the BCP process out of Sybase and the SQL Server database, but I am not sure whether that is at all possible (this is where I drop in some rubbish about ASE being 5 versions behind SQL Server 😉 )

In regards to the ODS, from the way I understand it, TDE would cover off "encrypted", but as rightly stated above, the data would still be freely available to those who can log in and have access to the tables, whether they need it or not, which most certainly doesn't tick any boxes at all. I am wondering whether applying dynamic data masking to restricted fields to all users would cover that off? Or do I need to go down the path of applying Column Encryption WITH dynamic data masking?