Determining a best practice to encrypt data from Sybase ASE to SQL Server

• hoffy

  SSC-Addicted

  Points: 436

  More actions

  July 5, 2017 at 8:29 pm

  #324377

  Hi Folks,
  It appears that this is my first post here!  So Hi everyone!
  
  OK, 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 - apologies if this is in the wrong section......