TDE / SQL server Database / SSIS

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    Dear Everyone

    We have implemented a data warehouse on-premises using SQL server 2019 enterprise edition running on VM Ware.

    SSIS, Database Warehouse and SSAS all reside on different servers in the same VLAN and now I am working on implementing TDE on the servers but I wanted to know the following:

    1. Is TDE required for SSIS if the packages are encrypted?
    2. Will implementing TDE in SSIS affect the data warehouse when pushing data from the staging environment to the data warehouse?
    3. Will implementing TDE only in the data warehouse affect SSIS?

    I don't have a testing environment to check all the items above so any advise would be appreciated.

     

    Kal

  • anthony.green

    SSC Guru

    Points: 112378

    TDE is only at the SQL Engine level, so you can only encrypt the actual databases at rest.

    Depending how you have implemented SSIS you could enable TDE at the SSISCatalog, however, would you want to?  The SSIS Catalog uses in database encryption to encrypt sensitive information at a column level where needed, is there a need to put TDE on top?  What are you protecting against?

    SSAS doesn't have TDE, you cannot encrypt a cube.  Now depending how you interact with the cube there may be ways.  Encrypt the file system where the excel files live, anything using HTTP to interrogate switch to HTTPS etc.

     

    Will TDE interfere with data transfer, no, as the data is not encrypted in transit, it is only encrypted at rest.

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    Hi Anthony

    thanks for the reply

    were actually using powebi service to access the data warehouse via the data gateway accessing the SSAS cube

    Since we’re talking TDE if I enable it now can it be disabled later?

    Will disabling it be an issue?

    Kal

  • anthony.green

    SSC Guru

    Points: 112378

    Yes you can disable it later.  But what are you actually protecting against with trying to implement TDE?

     

    TDE only protects your data at rest, so your only protecting against people getting access to your backup files BAK/TRN or the actual MDF/NDF/LDF files.

     

    If you want to protect data in transit, then TDE is NOT the tool to use.  You want to look at always encrypted, or actual encryption inside the database itself.

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    anthony.green wrote:

    Yes you can disable it later.  But what are you actually protecting against with trying to implement TDE?

    Good question I’ll confirm with our CISO but my understanding is protecting data at rest and while in transit so TDE is needed for at rest.

    Powerbi protects data in transit via the data gateway so I don’t think I need to enable always on but again I’ll confirm with security.

    Kal

  • anthony.green

    SSC Guru

    Points: 112378

    Then how is SSIS encrypting in transit?  Are your connections between the servers using encrypted sessions?

    What about access direct to the database (not the cube) how are you protecting that data in transit?

    What about staging, what about production sources, seems your only concerned about the DW but what about everything which feeds the DW?

    10 production databases, all using SSIS to feed into 1 DW, to 1 or multiple cubes, what about all the encryption at rest, in transit, access outside of the SSIS transfer process etc.

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    Just outside the office.

    I’ll reply on Sunday with the details Anthony

     

    have a good weekend

    Kal

  • hurricaneDBA

    SSCarpal Tunnel

    Points: 4802

    Sorry for the delay

    I implemented TDE and I’m waiting to see if they want to implement Always encrypted and encrypted sessions.

    question about DW.

    what’s the best practice followed in securing data warehouses for people in different depts?

     

    Kal

  • anthony.green

    SSC Guru

    Points: 112378

    It depends.

    What are you protecting against?

    You've enabled TDE at the data warehouse level, but what about the source systems?  What's stopping someone going to source and getting all your data?

    You need to go back and work from the ground up, if the source systems don't need TDE why does the data warehouse.  Source is just as if not more so important, without it you have no DW, you have no business as you can't operate without the source.

     

    Things to look at securing the cube would be row level security.  Same at the engine layer.  Potentially dynamic data masking at the engine layer.  Using schemas and groups and views and everything else to create marketing views to see only marketing data, sales views to see only sales data etc then depending on groups you can select from the right views.

    But again "it depends" on what is actually needed, what you are actually protecting against as to the solution you need to follow.

     

     

     

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply