How to Secure Azure SQL Database

Security is always a concern for Customers & Organisations when they thing about moving to the cloud. Azure provides new security paradigms and robust security at physical, logical and data access layers for both Application and Databases. If you want to know how to secure Azure SQL Database look no further. Azure SQL Database is a cloud based “Database as a Service” offering from Microsoft. With the latest V12 Databases, you now have the option to protect your Databases by implementing multiple layers of security.

Note: If you buy something from our links, we might earn a commission. See our disclosure statement.

Here are the options to secure your Azure SQL Database:

Built-in Security on Azure
Firewall and ACLS (Access Control Lists)
Authentication with SQL or Azure AD (AAD)
Manage Permissions using Database roles and Server roles
Database Auditing for Logging and monitoring

Data protection options using the following:

Transparent Data Encryption
Always Encrypted
Encrypting Transit Data
Row Level Security (RLS)
Dynamic Data Masking (DDM)

Read my earlier posts on Azure SQL Database Performance & Azure SQL on Virtual Machines.

Azure databases already meets most regulatory and security requirements like HIPAA, ISO 27001/27002, and PCI DSS Level 1, among others. Visit the Microsoft Azure Trust Center site to see the complete list.

Firewall per Azure SQL Database

Azure provides you firewall per database where you can explicitly specify who has access to the database. You have granular access over IP addresses and using the Azure Management Portal you can specify which IP addresses can connect to your database.

Port 1433 has to be opened since Azure SQL Database does not work on any other port.

You can manage the Firewall rules using the following options:

Azure Management Portal
T-SQL
Powershell
Rest API

Authentication:

Azure SQL supports two types of Authentication

Azure Active Directory (AAD)
SQL Authentication

Azure SQL Database extends existing authentication mechanism by allowing Azure AD users to login to the database using either the Principle Username & Password, Integrated Windows Authentication or Token based authentication.

For more information see Connecting to SQL Database By Using Azure Active Directory Authentication

Best Practices:

Use separate login users and applications for each database to avoid malicious attacks or a SQL Injection attack.
For V12 databases, use contained Database users, which directly authenticate against the Database instead of going to the master database, thereby increasing performance.
User Server-Level roles for managing server level access and security and Database roles for managing Database level access.
Use Database auditing which is available on all service tiers to tack logins and transaction management.

Transparent Data Encryption(TDE)

TDE is meant to encrypt data at rest and can be implemented by a push of a button. Just like SQL on premises, backups of TDE enabled databases are also encrypted.

Always Encrypted

If you are looking to go beyond encrypting data at rest and encrypt data on disk as well as on memory, then Always Encrypted is the way to go. For .NET 4.6 and above, you can use the client libraries to encrypt the data on the client and not on the database. This way, even the server administrator or anyone with direct database access will not be able to decrypt the data. The encryption key remains in the application in a trusted location, such as “Azure Storage Vault”

Encrypting Data in Transit

You can encrypt your SQL Database connections using TLS/SSL for the Tabular Data Stream (TDS) transfer of data. The new V12 now supports Transport Layer Security (TLS) 1.2 when connecting with the latest versions of the

ADO.Net (4.6)
JDBC (4.2)
ODBC

Support for ODBC on Linux, PHP, and node.js is coming soon.

For Azure SQL Database Microsoft provides a valid certificate for the TLS connection to eliminate any possibilities of “man-in-the-middle” attacks.

Row Level Security (RLS)

Using Row-Level Security (RLS) you can restricts access to rows, using a security predicate that is defined as an inline table-valued function (TVF). You create a security policy to enforce this function.

Code Sample:

----------------------------------------------------------------
-- PART 1: What RLS looks like
----------------------------------------------------------------
-- RLS is already enabled in the sample database to limit access to sensitive data about 
-- AdventureWorks customers, contained in the Sales.CustomerPII table. The access logic is:
-- - Sales Persons should only be able to view customers who are in their assigned territory.
-- - Managers and VPs in the Sales org should be able to see all customers.
-- EXAMPLE 1:
-- The user 'michael9' is a member of the SalesPersons role, so he can only access customers 
-- who are in his assigned territory (Territory 2):
EXECUTE AS USER = 'michael9'
go
-- Only customers for Territory 2 are visible (other territories are filtered)
SELECT * FROM Sales.CustomerPII 
go
-- Cannot update or delete customers who are not in Territory 2 (other territories are filtered)
DELETE FROM Sales.CustomerPII WHERE TerritoryID = 10 -- 0 rows affected
UPDATE Sales.CustomerPII SET FirstName = 'Changed' WHERE TerritoryID = 9 -- 0 rows affected
go
-- Blocked from inserting a new customer in a territory not assigned to him...
INSERT INTO Sales.CustomerPII (CustomerID, FirstName, LastName, TerritoryID)
VALUES (0, 'Bad', 'Customer', 10) -- operation failed, block predicate conflicts
-- ...but can insert a new customer in a territory assigned to him
INSERT INTO Sales.CustomerPII (CustomerID, FirstName, LastName, TerritoryID)
VALUES (0, 'Good', 'Customer', 2) -- 1 row affected
-- Blocked from updating the territory of an accessible customer to be in an unassigned territory
UPDATE Sales.CustomerPII SET TerritoryID = 7 WHERE CustomerID = 0 -- operation failed, block predicate conflicts
-- Reset the changes
DELETE FROM Sales.CustomerPII WHERE CustomerID = 0
go
REVERT
go

-- EXAMPLE 2:
-- On the other hand, the user 'amy0' is a member of the SalesManagers role, so she can access
-- all customers in all territories:
EXECUTE AS USER = 'amy0' -- Manager
go
SELECT * FROM Sales.CustomerPII -- all customers are visible
go
REVERT
go
----------------------------------------------------------------
-- PART 2: How to configure RLS
----------------------------------------------------------------
-- Let's change the security policy to support a common scenario for web applications. In this scenario,
-- Sales Persons connect to the database through a middle-tier application using a shared SQL login. To 
-- identify the current application user in the database, the application will store the current user name 
-- in the SESSION_CONTEXT immediately after opening a connection. This way, the RLS policy can filter rows 
-- based on the user name stored in SESSION_CONTEXT.
-- First, create a shared SQL login for the application's connection string
CREATE LOGIN ApplicationServiceAccount WITH PASSWORD = '{SomeStrongPassword}'
CREATE USER ApplicationServiceAccount FOR LOGIN ApplicationServiceAccount
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales.CustomerPII TO ApplicationServiceAccount
go
-- To set the SESSION_CONTEXT, the application will execute the following each time a connection is opened:
EXEC sp_set_session_context @key=N'user_name', @value=N'michael9' -- for example, the Sales Person from above
go
-- Now, this user name is stored in the SESSION_CONTEXT for the rest of the session (it will be reset when the
-- connection is closed and returned to the connection pool).
SELECT SESSION_CONTEXT(N'user_name')
go
-- Reset for now
EXEC sp_set_session_context @key=N'user_name', @value=NULL
go
-- We need to change our security policy to filter based on the user_name stored in SESSION_CONTEXT. To do this,
-- create a new predicate function that adds the new access logic. As a best practice, we'll put the function in a 
-- separate 'Security' schema that we've already created.
CREATE FUNCTION Security.customerAccessPredicate_v2(@TerritoryID int)
 RETURNS TABLE
 WITH SCHEMABINDING
AS
 RETURN SELECT 1 AS accessResult
 FROM HumanResources.Employee e 
 INNER JOIN Sales.SalesPerson sp ON sp.BusinessEntityID = e.BusinessEntityID
 WHERE
 -- SalesPersons can only access customers in assigned territory
 ( IS_MEMBER('SalesPersons') = 1
 AND RIGHT(e.LoginID, LEN(e.LoginID) - LEN('adventure-works')) = USER_NAME() 
 AND sp.TerritoryID = @TerritoryID ) 
 
 -- SalesManagers and database administrators can access all customers
 OR IS_MEMBER('SalesManagers') = 1
 OR IS_MEMBER('db_owner') = 1
 -- NEW: Use the user_name stored in SESSION_CONTEXT if ApplicationServiceAccount is connected
 OR ( USER_NAME() = 'ApplicationServiceAccount' 
 AND RIGHT(e.LoginID, LEN(e.LoginID) - LEN('adventure-works')) = CAST(SESSION_CONTEXT(N'user_name') AS sysname)
 AND sp.TerritoryID = @TerritoryID )
go
-- Swap this new function into the existing security policy. The FILTER predicate filters which rows
-- are accessible via SELECT, UPDATE, and DELETE. The BLOCK predicate will prevent users from INSERT-ing or
-- UPDATE-ing rows such that they violate the predicate.
ALTER SECURITY POLICY Security.customerPolicy
 ALTER FILTER PREDICATE Security.customerAccessPredicate_v2(TerritoryID) ON Sales.CustomerPII,
 ALTER BLOCK PREDICATE Security.customerAccessPredicate_v2(TerritoryID) ON Sales.CustomerPII
go
-- To simulate the application, impersonate ApplicationServiceAccount
EXECUTE AS USER = 'ApplicationServiceAccount'
go
-- If the application has not set the user_name key in SESSION_CONTEXT (i.e. it's NULL), then all rows are filtered:
SELECT * FROM Sales.CustomerPII -- 0 rows
go
-- So the application should set the current user_name in SESSION_CONTEXT immediately after opening a connection:
EXEC sp_set_session_context @key=N'user_name', @value=N'michael9' -- assume 'michael9' is logged in to the application
go
-- Only customers for Territory 2 are visible
SELECT * FROM Sales.CustomerPII
go
-- Application is blocked from inserting a new customer in a territory not assigned to the current user...
INSERT INTO Sales.CustomerPII (CustomerID, FirstName, LastName, TerritoryID)
VALUES (0, 'Bad', 'Customer', 10) -- operation failed, block predicate conflicts
go
REVERT
go
-- Reset the changes
EXEC sp_set_session_context @key=N'user_name', @value=NULL
go
ALTER SECURITY POLICY Security.customerPolicy
 ALTER FILTER PREDICATE Security.customerAccessPredicate(TerritoryID) ON Sales.CustomerPII,
 ALTER BLOCK PREDICATE Security.customerAccessPredicate(TerritoryID) ON Sales.CustomerPII
go
DROP FUNCTION Security.customerAccessPredicate_v2
DROP USER ApplicationServiceAccount
DROP LOGIN ApplicationServiceAccount
go

-- Final note: Use these system views to monitor and manage security policies and predicates
SELECT * FROM sys.security_policies
SELECT * FROM sys.security_predicates
go

Dynamic Data Masking

If you do not want to go into the complications of encrypting the entire database, or use cases like credit cards or social security where you want to see a part of your data, you can go for “Dynamic Data Masking”.

In order to implement DDM, you would require the following components:

Privileged SQL users: These SQL users always have access to unmasked data.
Masking function: This set of methods controls access to data for different scenarios.
Masking rules: This set of rules defines the fields to mask and the masking function.

Code Sample:

-- This demo uses the Sales.CustomerPII table in the AdventureWorks2016CTP3 sample database
-- to demonstrate how Dynamic Data Masking (DDM) can be used to mask (fully or partially) data
-- in sensitive columns.
--
USE AdventureWorks2016CTP3
go

-- DDM is already enabled in the sample database to mask the EmailAddress and PhoneNumber columns
-- in the Sales.CustomerPII table. 
-- If you are connected as 'dbo', you will always see unmasked data:
SELECT * FROM Sales.CustomerPII
go
-- Unprivileged users see masked data by default. For example, this SalesPerson 'michael9' will
-- see masked data:
EXECUTE AS USER = 'michael9'
SELECT * FROM Sales.CustomerPII -- EmailAddress and PhoneNumber are masked
REVERT
go
-- Granting users or roles the UNMASK permission will enable them to see unmasked data:
GRANT UNMASK TO SalesPersons -- role
go
EXECUTE AS USER = 'michael9'
SELECT * FROM Sales.CustomerPII -- EmailAddress and PhoneNumber are no longer masked
REVERT
go
-- Reset the changes
REVOKE UNMASK TO SalesPersons
go
-- DDM is configured in the table schema. For example, if you have the ALTER ANY MASK permission,
-- you can remove a mask on a column like this:
ALTER TABLE Sales.CustomerPII
ALTER COLUMN EmailAddress DROP MASKED
go
-- And you can add a mask like this: 
ALTER TABLE Sales.CustomerPII
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()')
go
-- You can also edit a mask with a different masking function. This shows how to define a custom
-- mask, where you specify how many characters to reveal (prefix and suffix) and your own padding
-- string in the middle: 
ALTER TABLE Sales.CustomerPII
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'partial(2, "zzz@abab", 4)') -- New mask for email
go
ALTER TABLE Sales.CustomerPII
ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'partial(0, "111-111-11", 2)') -- New mask for phone
go
-- See how that masks now:
EXECUTE AS USER = 'michael9'
SELECT * FROM Sales.CustomerPII -- New custom masks for EmailAddress and Phone
REVERT
go
-- Reset the changes
ALTER TABLE Sales.CustomerPII
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()')
go
ALTER TABLE Sales.CustomerPII
ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'default()')
go
-- Try doing a SELECT INTO from the table with a mask into a temp table (with a user that doesn't 
-- have the UNMASK permission), and you'll find that the temp table contains masked data:
EXECUTE AS USER = 'michael9'
SELECT CustomerId, EmailAddress, PhoneNumber INTO #temp_table
FROM Sales.CustomerPII -- Masked Email and Phone
SELECT * FROM #temp_table -- temp table has masked data
DROP TABLE #temp_table
REVERT
go

References:

 Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases.