How MS SQL Server Manages Data at Rest and Data in Motion

Overview: 

• Why data security is crucial
• Data at Rest
• Data in Motion
• Conclusion

Why data security is crucial: 
 

In today's digital age, data is one of the most valuable assets for any organization. It exists in two main states: data at rest and data in motion. Protecting both is crucial for maintaining security and compliance in an ever-evolving cyber landscape. This blog explores the differences between these two data states, how to protect them, and their importance in SQL Server environments.

Data at rest:
 

Data at rest refers to data that is stored and not actively moving through networks or systems. This includes databases, backups, files stored on disk drives, and cloud storage. Because this data is not in transit, it is typically static, making it an attractive target for unauthorized access or theft.

For Example:

• Database files (.mdf, .ldf, .ndf).
• Backup files.
• Files stored on hard drives, servers, or cloud services.

SQL provided TDE(Transparent Data Encryption) is a way to protect the data at rest.

About TDE: 

• To help secure a user database, you can take precautions like:
  • Designing a Secure System.
  • Encrypting confidential assets.
  • Building a firewall around the database servers.
• One solution is to encrypt sensitive data in a database and use a certificate to protect the keys that encrypt the data. This solution prevents anyone without the keys from using the data.
• TDE isn't available for system databases.

Configuring TDE: 

Step 1: Create a master key in master database.

You can check if the key successfully in below query.

Step 2: Create a certificate which protected by the master key.

Step 3: Create a database encryption key which protected by the certificate.

Step 4: Enable transparent data encryption for particular database.

Step 5: You can check the TDE status of the database using below query.

Note: Also we can check the encryption status from database properties through SSMS as well. 

Tried to take backup and restore in another instance . But facing the below error due to encryption. 
 

To restore the database to another server, you need to create encryption on that server with the same password. 
 

Data in motion: 

Data in motion refers to data actively being transferred between systems, networks, or applications. This state occurs when data is being accessed, queried, or transmitted from one location to another over a network, such as when users query SQL Server databases remotely or transfer backups to a cloud storage service.
 

For Example: 

• Queries sent from an application to a database.
• Data replication between servers.
• Backups transferred to offsite storage.
• API calls that access data remotely.

SQL provided SSL/TLS to protect the data in motion. (Secure Sockets Layer/Transport Layer Security).

About TLS/SSL:
 

• TLS: Encrypts data transmitted between SQL Server and client applications. SQL Server supports TLS 1.0, 1.1, and 1.2 on both Windows and Linux.
• SSL: Discontinued in SQL Server 2016 (13.x).
• TLS is the successor to SSL and fixes the security flaws in the older technology. SSL was replaced in 2015 after being compromised by vulnerabilities.
• The most commonly used versions of TLS are TLS 1.0, TLS 1.1, TLS 1.2, and TLS 1.3. TLS 1.3 is the latest version and offers the most robust encryption. 

Configuring TLS/SSL: 

Generate a self-signed certificate with PowerShell.

Now the Thumbprint is generated.

Step 1: Once you have the installed the certificate, you need to bind it to the SQL Server instance, so open the SQL Server Configuration Manager.

Step 2: Click SQL Server Network Configuration, right-click Protocols for your instance, and select Properties from the context menu, as shown in the following screenshot.

Step 3: Click the Certificate tab, and choose the TLS certificate you installed from the dropdown list.

Step 4: Now click the Flags tab, select Yes under the Force Encryption field, and click OK. If you skip this step, the SQL Server will allow both encrypted and unencrypted connections from clients.

Step 5: Click OK to confirm.

Note: Any changes will not apply until you will take a service restart.

Step 6: Then, we need to export and import the certificate. Click Start > Run, enter certlm.msc, and right-click the certificate on under personal folder. Select All Tasks > Export.

Step 7: The below page will open, Select the first option and click next.

Step 8: Then give the password and click next.

Step 9: Browse the file and give the name of the file. Then click Next.

Step 10:  Then click next and  the file was succesfully exported.
 

Step 11: Once the certificate exported, then expand the Trusted Root Certification Authorities > right click the Certificate > All Tasks > import.

Step 12: Then select the files > click next. 

Step 13: Give the password >click next>finish, your certificate was successfully imported.

Step 14: Now to connect the server in SSMS and now check all incoming connections to the server are encrypted.

Conclusion: 

Protecting data is crucial for every organization. Data at rest needs strong encryption and secure storage to prevent unauthorized access, while data in transit must be protected with secure transmission methods like SSL/TLS. By securing data in both states, businesses can reduce risks, comply with regulations, and build trust with their users. Simple and effective security measures make a big difference in keeping sensitive information safe.