January 23, 2020 at 4:47 am
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:
I don't have a testing environment to check all the items above so any advise would be appreciated.
Kal
January 23, 2020 at 10:20 am
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.
January 23, 2020 at 11:02 am
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
January 23, 2020 at 11:50 am
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.
January 23, 2020 at 11:55 am
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
January 23, 2020 at 12:10 pm
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.
January 24, 2020 at 6:30 am
Just outside the office.
I’ll reply on Sunday with the details Anthony
have a good weekend
Kal
January 29, 2020 at 1:08 pm
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
January 29, 2020 at 1:38 pm
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply