Encrypting data in SQL Server

Protecting database data with encryption  is a common requirement for business , financial regulatory and legislative rules.

Common scenarios include: protect personal data such as names, financial details, credit cards, medical results, insurance policies, protecting the data if the database is stolen.

Before deciding on which techniques to apply, decide on the level of security required.

Encrypting data is one method. But before you apply Encryption have you covered the basics ? There is security functionality which can be applied today.

Basics of applying SQL Server Security Policy

1)      Define a Security Policy across the different  access points

•Windows  OS

 •SQL Server Security

 •Database

2)      Server hardening process – Is there a certification process which applies consistent and auditable security levels. ?

3)      Regular security scans

4)      SQL Views – They are easy to implement. The DBA can define logins limiting the SELECT privileges to certain columns.

5)      Assign column level permissions  - This offers an extra layer of granularity.

6)      Encrypting data

How can you protect a database from being grabbed and restored as a clear copy somewhere else?

Transparent Data Encryption (TDE).  TDE doesn’t require application changes, i.e transparent to the application. TDE can be viewed as an encryption tunnel , where all data must pass to move back\forth from disk and memory.

Note: TDE encrypts at the disk level -  in comparison to column level encryption which keeps the data encrypted until there is an explicit function call.

1) Implementing TDE doesn’t stop you from adding column level security.  

2) TDE doesn’t protect data in memory.

3) TDE doesn’t protect the communication layer between the client and the SQL Server

4) More levels of security layers added requires increased management of encryption keys.

 Benefits of TDE database encryption

The major benefit of  TDE database encryption  is that if a database or backup is stolen, it can’t be attached or restored to another server without the original encryption certificate and master key.

When a backup is made of an encrypted database, it cannot be restored unless the DBA has access to the certificate and master key that was used to encrypt the database. This prevents anyone from stealing a backup and restoring it on a different SQL Server. Similar process applies to  detach and reattach a database to a different SQL Server.

In summary: TDE encrypts what is on disk , the data is returned as decrypted . If you want encrypted data , where even the SELECT statement returns encrypted data , you’ll need column level security.

Symmetric  AES key encryption –  is an in-built SQL Server method and can be applied a column level.   It’s a combination of encryption ,  secured by certificates to manage the master key .

Read more on Performance impact of TDE

Limitations of Symmetric  AES key encryption

 1) Minor  impact on SQL performance in SQL transactions - 5-6 percent on average- slower on accessing/updating an encrypted column versus plaintext column in typical transactions. The greater the number of columns encrypted, the greater level of negative performance impact.

2) Encrypted columns require  the varbinary data type; this means columns must be changed from their original data type to varbinary.

 How to decide which level of encryption is required?

 Consider these options and assess the specific requirement.

Is there?

1)A table with one column where the column needs to be encrypted completely

2) A table with one column where only some of the data needs to be encrypted

3)A table with more than one column where all columns need to be encrypted completely

 4)A table with more than one column where some cells in each column need to be encrypted

5) Do you need the DBA\ sa locked out of the data?

Based on the response to these questions you can decide on the encryption method to apply

Read More on SQL Server security related topics

Forgotten sa password - SQL Server DBA

Find Weak passwords in SQL Server - SQL Server DBA

Powershell sql server security audit - SQL Server DBA