Services‎ > ‎

Securing Data in SQL Server

Introduction

There are a number of different mechanisms for securing data held in SQL Server databases including various types of encryption as well as data masking techniques, auditing and not forgetting database backups. This page will give  an overview of what is available but first it may be helpful to explain some underlying issues.

There are three places that data may need to be secured: ‘At Rest’, ‘In Transit’ and ‘In Use’. I.e. inside the database, being transferred over a network and on a PC using the data.

Implementing encryption may cause issues with many options requiring database columns to be modified, for example by changing their data types. Searching for complete strings can usually be made to work with most encryption methods but may will need changes at either database or application level. Searching for partial string matches, eg search on ‘Nott’ to find matches with Nottingham, is not practical with many encryption methods. Sorting and ordering of encrypted data on the server can also cause issues but can be done on the client.

 

Database Backups

Arguably THE most important process to secure your SQL Server database is proper backups. However this is not as simple as it sounds. You need to decide on the appropriate Recovery Model, configure and schedule the database backups and set up Transaction Log backups depending upon which Recovery model you are using. You probably want to compress your backups as well as encrypting them (SQL Server 14 onward; Standard and Enterprise Editions but not Web or Express). SQL Server Express does not include the SQL Server Agent scheduling tool so if using this Edition you will need to script out the backups and trigger them, either manually or with a Windows Job or by some other mechanism. Finally you will need to devise a mechanism to copy the backups to a different physical location than the original server.

TDE (Transparent Data Encryption)

A built-in SQL Server encryption mechanism introduced with SQL Server 2008 and only available in Enterprise Edition. It encrypts pages of data on the fly and requires no changes in the application or in your SQL Server database. As such it is relatively easy to implement and has virtually no impact on your applications. However, data is only protected ‘at rest’ i.e. whilst in the database and thus TDE is mainly of use should a copy of your database be stolen. It does not stop authorised users inside your organisation from accessing data as it has no granularity – it’s an all or nothing implementation. It is an expensive option since it is only available in Enterprise Edition.

Always Encrypted

A built-in SQL Server encryption mechanism introduced with SQL Server 2016 Enterprise Edition and available in all editions, including Standard and Express, as of SQL Server 2016 SP1. Selected data is encrypted at the application level and is transmitted to the database in encrypted form. It is stored in encrypted form and transmitted back to the client in encrypted form and is then decrypted at the client. This provides a lot of security since there is no way of unencrypting the data from within SQL Server. You only need to encrypt columns (fields) that you want to protect rather than the whole database. It does require .NET Frameworks 4.6 or later. Drawbacks are some changes to the database (data types), and potential issues with searching and sorting.

Roll Your Own

An alternative to the Always Encrypted method and broadly similar. However, instead of using the automated processes built into the SQL Client data transfer mechanisms you create your own encryption within the client application using the .NET Security framework. It requires slightly more work at the client end but gives you more control and flexibility and will work with all versions and editions of SQL Server as well as with other databases, text files, etc. We have developed versions for both Triple DES and AES encryption which can be retro-fitted to existing .NET client applications.

Row Level Security

Row Level Security allows you to filter data based upon a user, their department their site or similar. For example an account manager could only see information relating to their own accounts, or their department’s accounts, and not those from another user or department. Although only available as of SQL Server 2016 it is also possible to create your own version of Row Level Security for earlier versions. May need some changes to the application depending upon how your tables are designed but has no impact on searching or sorting.

Dynamic Data Masking

Not an encryption method but has the effect of looking like one. Introduced with SQL Server 2016 Enterprise Edition and available in all editions as of SQL Server 2016 SP1.

DDM does not physically encrypt your data but it optionally applies a mask at the SQL Server level when the data is extracted from the database. Hence an unauthorised user might see rXXX@XXX.com whereas an authorised user would see robertperson@gmail.com. It is not as secure as proper encryption but is very easy to apply and does not require any application changes and only minor database changes.

Auditing

Now available in all editions as of SQL Server 2016 SP1, SQL Server Audit allows you to log a wide variety of events such as: User logons, Changes to the database, Adding a new user, Altering user permissions, Selecting data from a specific table, etc., etc. It does not secure data as such but allows you to find out more about any data or security breaches.