Given the nature of high profile data breaches in recent years, security is at the forefront of modern application design. This attention to security is heightened when organizations move to the cloud, because of the perceived loss of control of their data. Fortunately, major cloud vendors are aware of this and have built mechanisms to easily secure your applications.

Through centralization and economies of scale, cloud computing democratizes options such as disaster recovery and horizontal scaling that were previously only available to large organizations with large budgets. Security is no different. One of the options you will learn about in this article is transparent data encryption (TDE). Normally, TDE requires a complex multi-step code based process to configure, but in Azure SQL Database (SQL DB) it’s simply a switch on the portal.

Microsoft has built a number of features into SQL DB in recent years to easily make your applications more secure. In this article you will learn about:

The layers of protection Microsoft has in place in the platform

The Azure features that you can leverage to secure your application

General principles of database security

Platform Security

One thing to keep in mind about any platform-as-a-service offering, such as SQL DB, is that there are several aspects that are outside of your control. As a developer working with databases this can be a good thing because it means that you no longer need to worry about backups or patches to SQL Server.

Platform-based security is part of this. The first and clearest example is connection encryption. All connections to SQL DB are encrypted by default. There are also a number of other platform-based security features that SQL DB includes. Keep in mind SQL DB is part of the broader Azure infrastructure, which includes intrusion detection and numerous other security controls.

Azure SQL Database also offers other security options. For example, auditing is quick and easy to configure. Unlike in SQL Server, the audit service runs outside of the database, and therefore does not impact database performance. In conjunction with auditing, there is a Threat Detection service which scans for SQL injection, vulnerability to SQL injection, and anomalous logins

Figure 1 Azure SQL Database Threat Detection

Configuration of this feature is easy. To use it, you need auditing to be turned on at the server level. You will also need to choose a storage account to store your audit records. You can access the related settings by navigating to SQL Databases -> your-database -> Settings -> Auditing & Threat Detection.

A choice you have with auditing is to choose table or blob auditing. Blob auditing is the newer and preferred method. It gives you more control and is more cost effective.

You will need to choose table storage in order to use the threat detection feature. From there you simple click the On button in Figure 2 under “Threat Detection” and supply an email to be notified when Azure identifies anomalous activity.

Figure 2 Configuring Audit and Threat Detection

You can also take advantage of auditing to identify activity within your database. Auditing allows SQL run against the database to be captured and logged. This can be especially useful for apps in heavily regulated industries like healthcare and finance which require per statement auditing. You can easily consume this data in Microsoft Excel, and an “Open in Excel” button is provided on the auditing portal page.

You can find out how to configure Excel to work properly with the audit data here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-threat-detection-portal.

Firewalls—The First Measure of Security

Exposing a database to the entire internet is really bad idea—you probably don’t need to hear that from me, but I recently ran into a SQL Server (on-premises) that had port 1433 exposed to the internet. It showed lots of failed logins attempts from SA accounts all over the world.

Azure SQL Database has two levels of firewalls to protect your database: server-level firewall rules and database-level firewall rules.

To understand SQL Database firewalls, you need to have some understanding of the platform. When you create an Azure “SQL Server” you are not creating an instance of SQL Server in the traditional sense. Your “server” is simply a logical container for a grouping of databases that may or may not be running on the same hardware. There are several settings which are configured at server level: auditing (as mentioned above), long-term backup retention, and the firewall rules (which can be controlled at both server and database levels).

Figure 3 Azure SQL Database Firewall

As you can see in Figure 3, the user attempts to authenticate via a connection. The authentication is first validated before the firewall is checked.

If the user successfully authenticates with the server, the server-level firewall rules are checked. If the IP address is in the rule set, then the connection will go straight through.

If the connection is not in the rule set, and the connection specifies a database, the firewall rules (which are stored in that database) are checked and then the connection is either allowed or denied.

If the user successfully authenticates with the server, and there is no firewall rule in place, then the user (assuming they are using SQL Server Management Studio) will be prompted to login to the Azure portal to create a new server level firewall rule (see Figure 4).

Server-level firewall rules may be created using non-database sources (such as through the Azure portal, PowerShell, or the Azure API) as the data is not stored in the database. Database-level rules can be created using T-SQL as in Example 1.

Figure 4 New Firewall Rule Creation SQL Server Management Studio

EXECUTE sp_set_firewall_rule @name = N'ContosoFirewallRule', @start_ip_address = '192.168.1.1', @end_ip_address = '192.168.1.10'

In theory, only the allowed IP addresses of the application services that are connecting to your database should be allowed. It is not a good design pattern to allow users unfettered access to the database tier, particularly on cloud databases that have limited resources. In reality, you will likely need to add some more IP addresses for management purposes (code deployments, troubleshooting, etc.), but limiting the ruleset as far as possible will help keep your database more secure.

Authentication, Users, and Roles

In the early days of Azure SQL Database, there was only one option for authentication, which was SQL authentication. Recently Microsoft switched to support Azure Active Directory (AAD) authentication, which allows users to leverage their Azure AD credentials. This is slightly different from traditional Windows authentication in SQL Server. Users will be required to provide a password in most scenarios—the exception is when Azure Active Directory is integrated with Active Directory Federation Services (ADFS) either on-premises or in Azure Infrastructure-as-a-Service. This is a fairly complex configuration, but it does allow for a seamless sign-on experience for users and applications. The options you have for authenticating with AAD are as follows:

Active Directory Universal Authentication. This builds on Azure Multi-Factor Authentication to give a two-factor experience. This can be particular useful for authenticating administrators securely.

Active Directory Password Authentication. Users simply authenticate with their Azure AD username and password.

Active Directory Integrated Authentication. This allows authenticated users to authenticate without using a password on a domain joined machine with ADFS in place.

Figure 5 Login menu from SQL Server Management Studio

Another relatively new user concept in SQL DB is that of contained database users. These are users that are exclusive to the database (the login information is stored within the user database, and not the master database). To create a contained user, simply use the syntax in Example 2.

After your users are authenticated, roles and privileges are configured the same as in an on-premises SQL Server. You should follow the rule of least privileges when designing a security model. One thing to be aware of is that, at the time of writing, there are no cross-database queries in SQL DB, so all of your tables need either be in one database, or data needs to be joined at the application layer.

SQL DB has row-level security (RLS), which allows you to use the database as a multi-tenant application. This feature was added to SQL Server 2016, but has been generally available in SQL DB since mid-2015. Through the creation of a function, this allows us to specify which rows users can access in each table. In the code in Example 3, you can see how this is implemented using the demo user you created in Example 2.

We use the Fact.Sale table in the WideWorldImportersDW sample database. In this example, a string predicate is created based on all sales reps having a username of “SalesRepNN” where NN is the Salesperson Key in the fact sales table. As you execute this code, you will see that each sales rep can only see his or her rows in the table, while the manager can see everyone’s sales records.

USE WideWorldImportersDW GO --Create a series of users CREATE USER SalesManager WITHOUT LOGIN; CREATE USER SalesRep83 WITHOUT LOGIN; CREATE USER SalesRep74 WITHOUT LOGIN; GO --Grant Access to the Sales Fact Table GRANT SELECT ON Fact.Sale TO SalesManager; GRANT SELECT ON Fact.Sale TO SalesRep83; GRANT SELECT ON Fact.Sale TO SalesRep74; GO --Create Function for Row Level Security CREATE OR ALTER FUNCTION dbo.fn_rlspredicate(@SalesPerson AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @SalesPerson = SUBSTRING(USER_NAME(),9,100) OR USER_NAME() = 'SalesManager'; GO CREATE SECURITY POLICY SalesRepFilter ADD FILTER PREDICATE dbo.fn_rlspredicate([SalesPerson KEY]) ON Fact.Sale WITH (STATE = ON); GO EXECUTE AS USER = 'SalesRep83'; SELECT * FROM Fact.Sale REVERT GO EXECUTE AS USER = 'SalesManager' SELECT * FROM Fact.Sale REVERT GO

In Example 3, you are configuring a table valued function, which is schema binding (both of these are requirements for your RLS function) based on the user connecting to the database. In the security policy, you are filtering based on the application user id to limit which rows the user can see. Additionally, you are creating a block predicate that stops the user from inserting data into those rows.

One thing to be aware of is that your RLS function is effectively an additional predicate on a query, and this can impact performance. To alleviate this, you should make your function simple, and more importantly you should index the column that the function is operating on.

As mentioned earlier in the article, all connections to SQL DB are encrypted by default. Microsoft uses Transport Layer Security (TLS) 1.2 to encrypt these connections.

Securing Your Data with Encryption

Encryption at rest is very easy in SQL Database. Simply set the “Data encryption” slide to “On”, as in Figure 6.

Figure 6 Transparent Data Encryption for Azure SQL Database

Unfortunately, Transparent Data Encryption (TDE) is very transparent—it secures your data files and backups, but does not provide any security for the data that is stored within the database. If a user gains access to a TDE enabled database, they will be able to see all the data they have access to. This is particularly problematic for administrators who have complete access to every object in the database.

The traditional method of securing data within the database was to use cell-level encryption, which is available within SQL DB. The problem with cell level encryption is that the keys are stored within the database, and therefore a malicious admin can still access the data. Microsoft’s solution for this problem is called Always Encrypted, and is supported in SQL DB as well as all editions of SQL Server 2016 SP1.

Implementing Always Encrypted

Always Encrypted is a solution where the application maintains the keys to decrypt the data, and they are never stored in the database. This means the data in those columns is never stored in an unencrypted manner anywhere in the database—the data files, the buffer pool, or any execution plans. There are a few requirements to implement this—you must use .NET 4.6 and the latest version of the SQL Server drivers (JDBC, ODBC, and OLE DB are currently supported).

There are two types of Always Encrypted encryption—deterministic and random. Random should be used where the data has a low range of distinct values, as each value will be individually encrypted. Deterministic encryption always generates the same encrypted value for each record, which allows the database engine to index, compare, join on and group records that are encrypted. Only queries that evaluate equality are supported on encrypted columns—you couldn’t for example do a wildcard search or a greater than calculation as those would require decryption, and the database does not have the keys. Being able to index the columns allows your application to use encryption with limited impact on performance.

In Example 4 you will see a basic application, using the HumanResources.Employee table from the AdventureWorks2016CTP3 database from Microsoft. The Adventure Works database is already enabled for encryption, so to demonstrate we will create a new database and table.

CREATE DATABASE AEDemo (Edition = 'Basic') GO CREATE COLUMN MASTER KEY [DemoAE] WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE', KEY_PATH = N'CurrentUser/My/5E91A235D494D7D31C3E2C1F1F969EC5594308EF' ) GO CREATE COLUMN ENCRYPTION KEY [DemoCEK] WITH VALUES ( COLUMN_MASTER_KEY = [DemoAE], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003500650039003100610032003300350064003400390034006400370064003300310063003300650032006300310066003100660039003600390065006300350035003900340033003000380065006600124B9F03C589747A7B7E1A569B60F2E2CE6016510FE66FBB1D79406BE5B1744D66D989E08B67691EFF793906C72815A8930193EA67152A48FA63BF3FF572208867472E2EC2A62A62D1F7CC3040BDBFE822917216F06ED542CEF6BDE3BAB5AAD9095EEDA39D821E8323D96489E457C880487BEC8840DF998BC1A514EFA3252E16967BC61521CC579E965C1B129D2C7E954ABE35560652F567DE59C963C1EE2FD010991BFA63B6DCDD2BEE4FE695BCA43894E3A85F850C0DAC9811153A6CF76FC45BECB452312835F88413C805B4EAE3BDDA3B9BAB742B91261F78C790571C936C46A65B798A7BDA94FB71E19D460465B1CA4D0763A1D18C928228262EB89A9476594D272936EEE787198BB7AAF4210AE00CBE94E517E005CD9A0375B84210EFBCE50EC035E3ABEF5FED2FAF97FE70C88BAC44D6816120C9EC39D447521A5265C0844873E63DB897739F2801BC57623A980CBF65F4545467DE2475EAC6411F47EE79BF004971FC8FA3ED3B8AB32C7641D78ED43575CD3F4F3B24B67D579361C43F5BF26FD69C21E4AE583ED632040441F98EA520B308CA2A0E67BDBA9168AD8F1068947C5828F3D3483A8B21E64EB5BE1DBA96DA60710B5FB4D42A62DBA2A65224F1EF2EB66880486DB086D4A5CB85ED6FB3B4612225240BE5604C19A871373F02C2FEF0CAB5D3FBBC296AC1349D472438FE4C3B30C281DD79B5C09B0F74FEC082 ) GO

You are doing a couple of things in this T-SQL. First you create a column master key, and store it within SQL’s key store. Some more secure implementations may require the use of Azure Key Vault for this storage. Next you create a column encryption key based on that column master key. You will note that the T-SQL definitions for this are quite complex and require specific locations. Because of that I would recommend using the SSMS GUI to create both your column encryption key and your column master key. You can use the “Generate Script” option to save the T-SQL for source control purposes.

Next you will create a table with the keys you just created. In this scenario we are replicating the Employee table, but putting randomized encryption on the birthday, and deterministic encryption on the NationalIDNumber column. This means we can do equality searches on the IDNumber field, but not birthday.

CREATE SCHEMA [HumanResources] GO CREATE TABLE [HumanResources].[Employee_Encrypted]( [BusinessEntityID] [int] NOT NULL IDENTITY(1,1) , [NationalIDNumber] [char](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = DemoCEK) NOT NULL, [LoginID] [nvarchar](256) NULL, [BirthDate] [date] ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = DemoCEK) NOT NULL, CONSTRAINT [PK_Employee_Encrypted_BusinessEntityID] PRIMARY KEY CLUSTERED ( [BusinessEntityID] ASC ))

The below code is from a very basic C# console application to get you started with AlwaysEncrypted. This app will insert records into the database using the “Column Encryption Setting” parameter as part of its connection string. Additionally, you will need to ensure the project is defined with .NET Framework 4.6.1. From the code sample you will need to change the bolded settings to match your environment.

Figure 7 Project Settings for AE Demo

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; namespace AlwaysEncryptedDemo { class Program { static int Main(string[] args) { if (args.Length != 3) return 1; using (SqlConnection con = new SqlConnection()) { con.ConnectionString = " Server=tcp:YourServer.database.windows.net,1433;Initial Catalog=AEDemo;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Column Encryption Setting=Enabled;Connection Timeout=30;"; con.Open(); using (SqlCommand cmd = new SqlCommand("", con)) { cmd.CommandText = @"INSERT INTO [HumanResources].[Employee_Encrypted] ([NationalIDNumber], [LoginID], [BirthDate]) VALUES(@NationalIDNumber, @LoginID, @BirthDate);"; SqlParameter paramSelectNationalIDNumber = cmd.CreateParameter(); paramSelectNationalIDNumber.ParameterName = @"@SelectNationalIDNumber"; paramSelectNationalIDNumber.DbType = DbType.AnsiStringFixedLength; paramSelectNationalIDNumber.Direction = ParameterDirection.Input; paramSelectNationalIDNumber.Value = args[0]; paramSelectNationalIDNumber.Size = 15; cmd.Parameters.Add(paramSelectNationalIDNumber); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("{0}\t{1}\t{2}\t{3}", reader.GetInt32(0),reader.GetString(1),reader.GetString(2),reader.GetDateTime(3)); } } else { Console.WriteLine("No rows found."); } reader.Close(); } } return 0; } } }

The values inserted into the encrypted columns are passed as SqlParameter objects. This design pattern is required for Always Encrypted columns, but is also highly recommended for regular columns as it helps prevent SQL Injection attacks. Aside from that, there is nothing specific to encryption in this code.

Example 6 shows how to implement data retrieval with encrypted columns. If you go on to build the application, you can load sample data using the following command.

AlwaysEncryptedDemo.exe 222-11-3333 testuser1 11/15/1973

Figure 8 Console Program to Load Data

The data is loaded—you’ll note that the data is not encrypted in the app. However, if I go back to SQL Server Management Studio and query the table, I will see the following results.

Figure 9 Query Results in SSMS

The records are encrypted in the database which was our goal here.

Summary

Azure SQL Database is a secure platform. It includes many security features that are built into the broader Azure platform, and on top of that it enhances security within the database itself. This allows you to quickly develop secure applications without having to worry about lots of configuration options. Finally, Always Encrypted makes it easy to encrypt your sensitive data with minimal changes to your application code.