Nowadays security breaches happen way to frequently. As DBA’s we should all take extra care with sensitive data and ensure we are encrypting correctly. But some of us forget to take it a step farther and don’t encrypt our backups. For those working in an ultra data sensitive environment I highly recommend you go this extra step. As of SQL 2014, SQL Server now has the ability to encrypt your data during the backup process. Not only that but they have made the process simple. Prior to 2014, the only way to encrypt backups was Transparent Data Encryption (TDE), and that requires Enterprise Edition which made this out of reach for most. First…
-
-
A few weeks ago, I wrote Part 1, 2 and 3 of this series, which was a beginner’s guide to Always Encrypted. In part 4, I am going to talk about the changes that are coming in vNext of SQL Server. As I discussed in part 3 there are many roads blocks the can stop the implementation of Always Encrypted (AE). In the current available versions of SQL Server 2016 and 2017, along with Azure SQL Database, the cost of using AE was way too high for many companies. There are so many code changes needed to implement AE that moving to it is not cost effective for them. Microsoft recognizes this and has…
-
A few weeks ago, I wrote Part 1 and 2 of this series, which was a beginner’s guide to Always Encrypted. In part 3, I am going to go over what road blocks I ran into when trying to implement this solution for a client. The goal of the project was to prevent the DBA from being able to view salary information, while still allowing the application to function without issue. We were able to encrypt the data easily, but the entire process was not without issues. We had to remove default constraint values to get it to implemented. Always Encrypted does not currently support constraints. Fortunately, the defaulted zero…
-
In this post we will pick up where we left off in Part 1, if you haven’t read that please go back and do so. Now that we have encrypted our columns, it’s time to take a look at how we decrypt them inside SQL Server Management Studio or through our applications. You’ll be surprised to see how easy it is. Verify Your Setup First, let’s verify that the table is still encrypted, and nothing changed after you ran through the Part 1 examples. To confirm, simply query sys.columns, script out the table, or query the data to check that the Birthdate column is still encrypted. USE [AdventureWorks2016CTP3] GO CREATE…
-
Encryption has always been intriguing to me but seemed like it could be a very complex process to set up. However, SQL Server has made it very simple when they introduced Always Encrypted (AE) into SQL Server 2016 and Azure SQL Database. Unlike Transparent Data Encryption (TDE) which only encrypts data files and backups at rest, AE is configured on a column level and not database level. Additionally, Always Encrypted is available in Standard (and Express) Edition, starting with SQL Server 2016 SP1. You can easily encrypt a social security number (SSN) which is considered very sensitive within the United States or Salary column in a table with just a…
