Will TDE encryption protect my SQL database from hacker in this scenario I'm a bit understand that TDE encryption protect the database from being stolen and read by a hacker. However, I don't understand if it can help me in this scenario: If a hacker got into my server and he got the Administrator privilege. Now he want to access the database to stolen information. He won't need to copy the entire database file which being encrypted. But he can reset the SQL SA password or read the connection string from a website .config file , and use tool like SQL Server Management Studio to access the database from that user and password, finally he use few SQL query to view the data or export it to disk. Is that possible?
[…]Is that possible?


Yes. The scenario where a server is compromised is not what is being targeted by TDE. TDE is targeting data at rest scenarios such as lost/stolen backups, hard drives that were discarded and not scrubbed, etc., but not entire server compromises.

In this case I'd advocate for you to upgrade off of 2008R2 (which only has &lt;6 months of support left) and onto something much newer. There you have options such as column level encryption through HSMs and Always Encrypted (which can also use HSMs) which would be two main scenarios that can potentially (depending on external factors) stop the attack you've specified from being able to steal any extra information not already on the server and unencrypted.