4

I would like to know (better ideas/best practices...) what can I use to encrypt my data.

I've used TDE in the past and I really don't like it. because if something happens with the certificate, and the servers, I will not be able to restore the backups.

I'm doing tests after tests and I just don't fell safe about it.

What are some other options to secure my database/data/backup files?

I tried masked data too, but 1) its for sql server 2016+ (and for now we are using 2012).. and 2) it just creates another column with the data masked, with the original still there.

What is a good option for this task?

i want to protect my backups so nobody can "steal and restore" it in another server, and I would like to prevent data steal.

I'm already using a good secutity measure with logins and ETC. only me have the sa pass, I created a function where it changes the pass everymonth, etc etc.

2
  • 3
    " if something happens with the certificate, and the servers, I will not be able to restore the backups." True...this is why good management of certificates and keys is a MUST for any type of encryption
    – Kevin3NF
    Nov 27 '20 at 18:13
  • If you can recover the data without encryption keys, then that means a bad actor can recover the data without the encryption keys.
    – AMtwo
    Nov 28 '20 at 12:56
5

i want to protect my backups so nobody can "steal and restore" it in another server, and I would like to prevent data steal.

TDE would be the best built-in solution here, however if you solely care about backups and not your .mdf/.ndf/.ldf files then you could look into backup encryption to encrypt your backups. But this will also need a: Certificate or Asymmetric Key. Third party backup solutions could also be an option.

Source

A solution that works together with TDE encryption is bitlocker:

With TDE alone, the database files themselves are encrypted (as well as backup files of the TDE-encrypted databases). Therefore, if either file is copied or stolen via a network, as in the above example, the attacker cannot read it. But if the attacker gains access to the whole drive, including SQL Server, he can start SQL Server and read the data using SQL, because SQL Server is doing the decryption for him. Now if both BitLocker and TDE are used concurrently, both data theft vectors are mitigated. Both technologies complement each other, and it is recommended that you use BitLocker together with TDE for defense in depth.

Source

I tried masked data too, but 1) its for sql server 2016+ (and for now we are using 2012).. and 2) it just creates another column with the data masked, with the original still there.

Data masking is not an encryption feature, it is a masking feature based on your role assignments. It works together with restricting users and their rights to query objects such as ad-hoc querying.


Another solution to look into would be Always Encrypted but that restricts certain querying of that data such as rich computation when using deterministic or (randomized encryption before SQL Server 2019).

Source


I'm already using a good secutity measure with logins and ETC. only me have the sa pass, I created a function where it changes the pass everymonth, etc etc.

The better practice is to rename and disable the sa login.

4
  • .MDF/.LDF files can only be protected by using TDE right?
    – Racer SQL
    Nov 27 '20 at 15:31
  • 1
    For live datafiles/logfiles that are being used by the SQL Server process as a transparent action that would be correct. See my notes on bitlocker for extra protection. Nov 27 '20 at 15:49
  • 1
    A last question. Is TDE commonly used? I've used in the past, but I was really new to sql server and I had no fear (and knowledge ( 6 years ago))...but Now I see how everything needs to be TOTALLY connected in the case of a disaster. you can EASILY lose your database.
    – Racer SQL
    Nov 27 '20 at 17:39
  • @RacerSQL Yeah TDE is a common solution for encryption at rest. Also, apart from on prem solutions the docs state that is enabled by default for new Azure SQL Databases: By default, TDE is enabled for all newly deployed SQL Databases and must be manually enabled for older databases of Azure SQL Database, Azure SQL Managed Instance. TDE must be manually enabled for Azure Synapse Analytics. Source Nov 27 '20 at 22:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.