For those of you who use Azure today, the security discussion must have been a thing on some occasion. Explaining to managers (and possibly colleagues) that Azure is a lot more secure than a(n) (on-premise) data center, and that Azure is easier to maintain and scalable. Trust me, we’ve all been there!

But besides the physical security, there’s also the digital security. In the world of today it’s easier to find a data-breach on the news, then it is to find an item about a bank robbery. So how can you secure your data in Azure in an easy but solid way, without the hassle of changing your applications?

Encryption could be one of your tools to achieve a secure infrastructure and/or applications. But encryption is a challenge for pretty much everyone. Almost every day we hear about companies not doing it right, or not doing it at all. But luckily, Azure helps us with setting this up with just the click of a button.

Okay, okay, you got me. Maybe a few button clicks…

 
Databases
For your Azure SQL databases, there’s a feature called “Transparent Data Encryption”, or TDE for short. This encrypts your data at rest with “FIPS 140-2 validated 256 bit AES encryption”. Or, in normal words: you encrypt your data with an AES-256 encryption key.

So how do you enable it? There are 2 ways to do so, but I’ll only show you the route via the Azure portal. Information on how to do this via T-SQL can be found here.

First, login to the Azure portal, and navigate to the database you want to encrypt. Click on “Transparent Data Encryption”, and just with a click of a button you can encrypt your data:

 
This will start the encryption process and, depending on the size of the database, after a while you’ll see that the data is encrypted:

 
This feature will allow you to encrypt your database, without any application changes. This is because the encryption and decryption is being handled in an “intermediate layer” by Azure. The data will be decrypted before returning it to the client, and the other way around it will be encrypted before it’s stored. So your applications will continue to work without any changes in the application-code or connectionstring(s) to the database(s).

 
Storage Accounts encryption
There is also an option to encrypt your Storage Accounts in the same way as TDE works for Azure SQL databases (without any application changes). When you enable this on your Storage Account, please remember that only the new data will be encrypted, and that the existing data won’t be encrypted until it changes. For more information on this, please read this article, and this MSDN thread.

When you’re creating a new Storage Account, you can choose to encrypt it right away:

 
But when you want to encrypt an existing Storage Account with data in it, you need to do it on 2 different levels (it’s a separate setting for BLOB and files):

 
This will encrypt your data with the same algrorithm as TDE for SQL Server will do: “All data is encrypted using 256-bit AES encryption, one of the strongest block ciphers available.” (source).

 
Conclusion
For us as a company, enabling this features means that all of our data is encrypted. We’re only sending and receiving data from within Azure, so the communication is also secure. And even though the majority of our data is public data (publicly available such as flight information, etc.), it’s a safe feeling to know that all our data is encrypted when stored.

In this blog I’m not going to tell you how to secure your data, because honestly I’m not an expert at that. I do want to introduce you to a new way to obfuscate your data, that was introduced in SQL Server 2016. According to Wikipedia obfuscation means: “(…)the obscuring of intended meaning in communication, making the message confusing, willfully ambiguous, or harder to understand”. Or in easy terms: make it harder to read for your users and/or employees. Let’s take a look at an example.

If I would give you an email address like “[email protected]”, it’s really easy to guess the name of the person. Let’s be honest, it’s not rocket science right?! But what if I would give you the email address “Jo*****@d*****.com”, would you still guess the name correctly? Yes I agree with you there’s a small chance you might guess the name right, but those chances are slim. And instead of writing your own code to obfuscate data this way, with SQL Server 2016 you’ll get it out of the box.

Creating resources
The following is tested on SQL Server CTP 2.1. If you’re not running that version or higher, this might mean the scripts or test cases might not work the same as in my case.

In order to test this new feature, let’s create a new database first:

USE master
GO

CREATE DATABASE DynamicDataMasking ON PRIMARY 
(NAME = N'DynamicDataMasking', FILENAME = N'C:\Database\DynamicDataMasking.mdf')
LOG ON
(NAME = N'DynamicDataMasking_log', FILENAME = N'C:\Database\DynamicDataMasking_log.ldf')
GO

 
Now let’s create a table called Users:

CREATE TABLE dbo.Users
	(UserID INT IDENTITY(1,1),
	 FirstName VARCHAR(100),
	 LastName VARCHAR(100),
	 Address VARCHAR(100),
	 DateOfBirth DATE,
	 SocialSecurityNumber VARCHAR(15),
	 Email VARCHAR(100))

 
This table contains some basic information about the users of our application. And of course we need some user data in the table. I’ve used Redgate’s SQL Data Generator to generate data for my table. Because I’ve generated a full table, I’ve made the script available here.

 
Now that we have a table with sensitive data, let’s look at the ways of obfuscating this data. From SQL Server 2016 onwards you get to use the Dynamic Data Masking feature. This means you can add a “mask” on top of your data. This is done by altering the columns in your table:

ALTER TABLE <TableName>
ALTER COLUMN <ColumnName> ADD MASKED WITH (FUNCTION = '<FunctionName>')

 
This takes care of the data obfuscation for you, and you can determine the level of obfuscation by using specific functions:

Default: Completely replaces the value in the column with “xxxx”
Email: Replaces the email address with X’s, except for the first character, and the Top-level domain
Custom: The most dynamic of the 3 functions. This allows you to configure a prefix and suffix, and replaces the rest of the values with X’s

 
Let’s add masks to the columns in our table:

ALTER TABLE dbo.Users ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION = 'default()')
ALTER TABLE dbo.Users ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXXXXX",1)')
ALTER TABLE dbo.Users ALTER COLUMN Address ADD MASKED WITH (FUNCTION = 'partial(10, "XXXXX", 0)')
ALTER TABLE dbo.Users ALTER COLUMN City ADD MASKED WITH (FUNCTION = 'partial(3, "XXXXX", 0)')
ALTER TABLE dbo.Users ALTER COLUMN State ADD MASKED WITH (FUNCTION = 'partial(4, "XXXXX", 0)')
--Country no masking
ALTER TABLE dbo.Users ALTER COLUMN DateOfBirth ADD MASKED WITH (FUNCTION = 'default()')
ALTER TABLE dbo.Users ALTER COLUMN SocialSecurityNumber ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)')
ALTER TABLE dbo.Users ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

 
Testing data masking
Now that we have a table with masked data, how does that look from different perspectives? Let’s create 2 users: an application login with just read permissions, and an admin login with db_owner permissions:

USE master
GO

CREATE LOGIN [AppLogin]
	WITH PASSWORD = 'TestPass',
	--SID = 0xDB9ED49EF06F3A40B5024B36F5CDD658,
	DEFAULT_DATABASE = [master],
	CHECK_POLICY = OFF,
	CHECK_EXPIRATION = OFF
GO

CREATE LOGIN [AdminLogin]
	WITH PASSWORD = 'TestPass',
	--SID = 0xDB9ED49EF06F3A40B5024B36F5CDD658,
	DEFAULT_DATABASE = [master],
	CHECK_POLICY = OFF,
	CHECK_EXPIRATION = OFF
GO


USE DynamicDataMasking
GO

CREATE USER AppLogin FOR LOGIN AppLogin WITH DEFAULT_SCHEMA = dbo
GO

ALTER ROLE db_datareader ADD MEMBER AppLogin
GO

CREATE USER AdminLogin FOR LOGIN AdminLogin WITH DEFAULT_SCHEMA = dbo
GO

ALTER ROLE db_owner ADD MEMBER AdminLogin
GO

 
Now open up 2 new SSMS tabs, and log in with the 2 accounts. In both sessions run the query below:

USE DynamicDataMasking
GO

SELECT * FROM dbo.Users

 
The difference is clearly visible when you select the top 10 records in both sessions:

 
In the admin connection you get to see the full dataset, and in the application login the masked dataset is returned.

 
Querying data
The retrieval and querying of data isn’t changed when you have data masks applied. If you run a query where you search for users with a specific first name, the correct results is returned:

SELECT *
FROM dbo.Users
WHERE FirstName = 'Jeffrey'

 
Security issues
Now we get to the interesting part: the security issues. Even though this is not a form of data encryption, I did expect this could be a part of data security. But unfortunately this was a false sense of security…

Let’s try a normal query on the masked data, with the AppLogin session:

USE DynamicDataMasking
GO

SELECT * FROM dbo.Users

 
This returns the data as expected: masked. But is there any way around this? Let’s make the AppLogin a db_owner on another database (run this statement with a user that has permissions to do this):

USE Sandbox
GO

CREATE USER AppLogin FOR LOGIN AppLogin WITH DEFAULT_SCHEMA = dbo
GO

ALTER ROLE db_owner ADD MEMBER AppLogin
GO

 
So the AppLogin is still db_datareader on the DynamicDataMasking database, and db_owner on the Sandbox database. Now let’s run the query below:

USE Sandbox
GO

SELECT *
FROM DynamicDataMasking.dbo.Users

 
Well, that’s awkward! If your application login has db_owner permissions on another database, you can query the data from the users table without the data masks!

But I know what you’re thinking. My application logins aren’t db_owner anywhere so I’m safe. WRONG! Let me show you another example:

USE DynamicDataMasking
GO

SELECT
	U1.*
FROM dbo.Users U1
INNER JOIN dbo.Users U2 ON U2.USerID = U1.UserID

 
Well hello precious data! If you just join a table to ANY other table (or join it to itself), all data is returned without masking.

This must be all right? There’s one last thing…

Let’s try to insert the masked data into a temp table, and select that result:

USE DynamicDataMasking
GO

SELECT *
INTO #UsersTemp
FROM dbo.Users

SELECT *
FROM #UsersTemp

DROP TABLE #UsersTemp

 
I’m not sure what the idea behind of this feature was, or how the SQL Server designed it, but this sounds like a real security issue to me. I expect my data to be obfuscated for my application users, but it’s the complete opposite. If you have a direct connection to the database (and not through an application only) it’s really easy to get a hold of the unmasked data.

 
Conclusion
Even though I really like this feature (and yes, I’m serious!) there are some things the SQL Server team needs to look at. If you’re expecting your data to be masked for all non-admin users, you’re in for a nasty surprise.

Hoping this will be fixed soon, I’ve created a Connect item for this. If you agree with me on this, and you think it’s a problem as well, please up-vote it or leave a comment in the connect item. There is also a specific item for the join issue. Let’s make sure the SQL Server team is aware of these issues.