July 25, 2017 at 7:12 pm
Hi Folks,
I am after some advice. I am working on a solution where I need to BCP data out of a Sybase ASE data source, BCP into a SQL Server 2016 staging database and finally into an operational data store (there will be further warehousing further down the track, but I am not currently looking at this). The staging database and the ODS will both reside on the same SQL Server instance.
In the above data flow, I have a requirement to encrypt certain fields at rest, to only be exposed to staff or systems that explicitly need this data.
My plan was to fully encrypt the Staging Database using TDE (which will be fully truncated or dropped after its processed into the ODS data store) and then apply Always Encrypted to the columns that need it in the ODS. Considering some of the articles that I have read, TDE can cause issues where other databases on the same instance don’t have it applied, is this a good idea? Or is this something that works better in SQL server 2016?
Any suggestions on how to deal with this would be appreciated.
Cheers
PS - I posted this a few weeks ago in another section, but didn't get any response. I was wondering whether this might be a better section to post this question in, so apologies if you have already read the above question.
July 26, 2017 at 11:02 am
ash.j.hoff - Tuesday, July 25, 2017 7:12 PMHi Folks,I am after some advice. I am working on a solution where I need to BCP data out of a Sybase ASE data source, BCP into a SQL Server 2016 staging database and finally into an operational data store (there will be further warehousing further down the track, but I am not currently looking at this). The staging database and the ODS will both reside on the same SQL Server instance.
In the above data flow, I have a requirement to encrypt certain fields at rest, to only be exposed to staff or systems that explicitly need this data.
My plan was to fully encrypt the Staging Database using TDE (which will be fully truncated or dropped after its processed into the ODS data store) and then apply Always Encrypted to the columns that need it in the ODS. Considering some of the articles that I have read, TDE can cause issues where other databases on the same instance don’t have it applied, is this a good idea? Or is this something that works better in SQL server 2016?
Any suggestions on how to deal with this would be appreciated.
Cheers
PS - I posted this a few weeks ago in another section, but didn't get any response. I was wondering whether this might be a better section to post this question in, so apologies if you have already read the above question.
I suspect you haven't had any answers because SQL 2016 is still relatively new and TDE may not be all that well known a "thing", as yet. There may simply not be many people that have messed with it much as yet, and thus there might not be many experts with it as yet. Things like encryption usually involve a fair amount of design effort, and thus even the request for help might go beyond the scope of what a free forum can provide in the short term, if for no other reason than a relatively low number of folks with expertise.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 26, 2017 at 11:16 am
sgmunson - Wednesday, July 26, 2017 11:02 AMI suspect you haven't had any answers because SQL 2016 is still relatively new and TDE may not be all that well known a "thing", as yet.
Guessing you mean Always Encrypted there? TDE's been around for a while 🙂
Cheers!
July 26, 2017 at 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.
July 27, 2017 at 7:01 am
Jacob Wilkins - Wednesday, July 26, 2017 11:16 AMsgmunson - Wednesday, July 26, 2017 11:02 AMI suspect you haven't had any answers because SQL 2016 is still relatively new and TDE may not be all that well known a "thing", as yet.
Guessing you mean Always Encrypted there? TDE's been around for a while 🙂
Cheers!
Goes to show you, wtf do I know... I've never spent much time on the "administrative" side of SQL Server, and it shows. Thanks for straightening me out.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2017 at 7:20 pm
jasona.work - Wednesday, July 26, 2017 11:28 AMSounds 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?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply