Minette Steynberg

Five ways to protect your data in Azure SQL Database

April 28, 2017 by

When storing data in the cloud the main concern companies generally have is whether or not their data is safe. And what can be done to ensure that the following 4 scenarios are addressed:

  • My database files must be protected in case the server is breached.
  • My data in the database must be stored in such a manner that no unauthorised user can view any data they do not explicitly have access to.
  • Certain sensitive data should never be displayed entirely.
  • My data should be protected while being transferred from my application to my database.

In this article, I will discuss 5 features of the SQL Azure Database which will help you achieve the abovementioned criteria.

Protecting your server with a firewall

The first step in protecting your data is to ensure that only authorized clients are in fact able to connect to your server and database. A firewall is used to allow only certain IP addresses or IP address ranges access to your database.

In my previous article Configuring the Azure SQL Database Firewall I explained how to configure this firewall and as such I will not go into much depth here.

Transparent Data Encryption

Transparent data encryption or TDE, as it is affectionately known, is not new to SQL Server. This was first introduced in SQL Server 2008 and is also included in SQL Database.

TDE encrypts the database files, such as the data, log files and backup files using a database encryption key. Basically, data gets encrypted when it is saved to disk, and it is automatically decrypted when the data is accessed.

TDE can be enabled without any changes to your application.

For SQL Server on premise, the first step involved is to create a master key, which is stored in the master database, using the following command:

In Azure SQL Database, however, you do not have to create your own master key. SQL Database will automatically do this for you when you enable TDE.

Enabling TDE for a SQL Database

In the Azure portal select SQL Databases.

Figure 1: Select SQL Databases

Next select the relevant SQL Database

Figure 2: Select the SQL Database

Click on the Transparent data encryption option

Figure 3: The Transparent data encryption Option

Set Data encryption on, and save.

Figure 4: Set encryption on

The following will be displayed while the files are being encrypted

Figure 5: Encryption in progress

And once completed, this will be displayed:

Figure 6: Encrypted!

Always Encrypted

Always Encrypted ensures that data is well, always encrypted, which means that the data is also encrypted while it is being transferred from the application to the database.

This ensures that the data cannot be stolen en-route using sniffers etc.

See the below infographic on how it works:

The data is stored encrypted, which means that not even the database administrator will be able to view the data if he does not have the Encryption Key.

The only change required to the application itself is to the connection string. Of course, the encryption key need to be available to the application.

Driver Required Version Example
Ado.NET 4.6 or higher “Data Source=min-sql-server; Initial Catalog=min-sql-database; Integrated Security=true; Column Encryption Setting=enabled”;
Microsoft JDBC 6.0 or higher “jdbc:sqlserver://min-sql-server;user=minette;password=mypassword;databaseName=min-sql-database;columnEncryptionSetting=Enabled;”;
Windows ODBC 13.1 or higher L”Driver={ODBC Driver 13 for SQL Server};Server=min-sql-server;Database=min-sql-database;Trusted_Connection=yes;ColumnEncryption=Enabled;”;

On the database side a couple more changes will be required which include:

  • Generating the Encryption keys
  • Generating the meta data for the keys in the database
  • Recreate the tables with the encrypted columns
  • Encrypt the data which already exists for the newly encrypted columns

Row-level security

The purpose of row-level security (RLS) is to restrict user access to individual rows. For example, in a hospital a nurse only requires access to the patient in his/her care. Since all patients are stored in the same table RLS is the answer. No application changes are required, and the security is enforced at the database tier, which means that it will always be enforced regardless of which client is being used.

RLS can be implemented in 2 basic steps:

  1. Create a filter predicate, by creating an inline table valued function which applies the filter.
  2. Create a security policy with the in-line function as the predicate WITH (STATE = ON)

Dynamic Data Masking

Dynamic data masking is used to obfuscate parts of certain sensitive fields, such as social security numbers, credit cards etc.

DDM has very little impact on the application since it is applied to the column in the database and access is controlled either a central masking policy

SQL Server 2016 has 4 predefined masks which can be applied.

Mask Description Example
Default A default mask will be applied depending on the datatype of the column. “Hello World” will become “XXX” 07.26.1984 will become 01.01.1900 88.99 will become 0 01001 will become 0
Email This mask is specifically created to obfuscate email addresses. Only the first letter and the domain will be displayed myname@mycompany.com will become mXXX@XXXX.com
Random This mask substitutes a random number for any numeric value. A range can be specified. 200 will become 388 or 023 or 777 or any other random value within the specified range.
Custom This mask allows you to substitute the middle of a string with another string. Allowing you to choose how many characters of the original string should remain as the prefix and suffix. 2334-4566-6778-4444 will become XXXX-XXXX-XXXX-4444 depending on the setting for prefix and suffix. In this case the function would look like this: (FUNCTION = ‘partial(0, “XXXX-XXXX-XXXX-“,4)’)

Here are some examples:

On my person table I have opted to apply a default mask to the [secret_answer] column, an email mask to the [email] column, and a random mask to the [salary] column, using the script below:

I user without the UNMASK permission will see the following:

Figure 7: Masked data

Whereas a user with the UNMASK permission, will see the real values:

Figure 8: Unmasked data

Conclusion

Microsoft continues to invest greatly in SQL Database security to ensure that your data is protected against internal and external threats. Although some of these features require some changes to your application or database, designing with security in mind has become a lot easier than what it used to be.

See more

Consider these Free tools and community editions for Azure SQL Database

References


Minette Steynberg

Minette Steynberg

Minette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups.

Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.

View all posts by Minette Steynberg
Minette Steynberg
495 Views