Sql-server – Good practices about Data Encryption

sql serversql-server-2012transparent-data-encryption

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.

Best Answer

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.

Related Question