What to do if in case someone gain access of datafiles or underlying drive where datafiles resides and attempts to attach the database files to a SQL Server instance and accesses data?

Protecting sensitive data is the topmost priority for businesses of all shapes and sizes. Either it's a user’s banking details, medical records, and other sensitive data it must be stored securely and accessible by authorized individuals only. Transparent Data Encryption encrypts the data that’s stored within the database’s files and any backup files created from the backup operation. This encryption is known as encrypting data at rest. SQL Server encrypts data pages before writing them to disc when we enable encryption at the database level. Data pages read from the disc, on the other hand, are decrypted and stored in memory. The user's experience remains unaltered. They are completely unaware that the data is encrypted but TDE does not encrypt data passes as traffic between the database and the programme, nor does it encrypt data within the application. When TDE is enabled on database, there is a modest performance hit. The exact amount is determined by server hardware and a variety of other factors.

Follow below steps to encrypt a database using TDE:

1. Create a Database Master Key
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TorData##123'

2. Create a Certificate
CREATE CERTIFICATE ConfigTED WITH SUBJECT = 'Datafile Encryption'

3. Backup CERTIFICATE key with private key
BACKUP CERTIFICATE ConfigTED
TO FILE = 'C:\User\ConfigTED.cer'
WITH PRIVATE KEY
(
FILE = 'SQLPrivateKey',
ENCRYPTION BY PASSWORD = 'TorData##123'
);

4. Create encryption key
USE TorDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ConfigTED

5. Enable the TDE on DB
ALTER DATABASE TorDB SET ENCRYPTION ON