New Azure SQL Server V12 Security Features

Secure Data with Azure SQL Database

Azure SQL Database offers a set of out-of-the-box features to secure organization data from non-authenticated users. It offers simple-to-implement features that help to protect the data and build secure business applications within Azure.

A lot of the newer Azure SQL Security Features are available only when clients connect using Secured Connection String.

Older In-Secure Connection String

{ServerName}.database.windows.net

Newer Secure Connection String

{ServerName}.database.secure.windows.net

Most of the newer Azure SQL Database Security Features only works with v12 engine which is GA. 

If you have an older version v11 database; it is required to upgrade it to v12. 

Check the version first by using this PowerShell Cmdlets

Get-AzureSqlServer 

-ServerName ''

-ResourceGroupName ''

Note:

ResourceGroupName can be find using Azure Preview Portal

You will get some results when you run above PowerShell command and out of the all information; find ServerVersion value

ServerVersion : 2.0

If the value is 2.0 then the available SQL Engine is not v12 it can be upgraded with following PowerShell Command

Start-AzureSqlServerUpgrade

-ServerName ''

-ResourceGroupName ''

-ServerVersion 12.0

Running above Cmdlet will queued the upgrade request and will finish ASAP.

If you run above command for In-Use Database; there will be an outage which can potentially takes few minutes subject to the database size.

To get the status of the above upgrade request; run this PowerShell Cmdlet

Get-AzureSqlServerUpgrade

-ServerName ''

-ResourceGroupName ''

Now Azure SQL Server has been upgraded to v12 and it is required to protect the data using these security features.

New Azure SQL Database Security Features

1. Connectivity

Azure Active Directory (AAD) Authentication Support is Generally Available (GA) for connecting to SQL Database by using user identities in AAD for managed and federated domains in a centralized location.

Azure Active Directory authentication uses contained database users to authenticate identities at the database level. It is an alternative to SQL Server Authentication and database permissions can be managed using AAD groups.

2. Authorization

Row-Level Security (RLS) Support is GA that allows user identity based, role membership based, or query execution context based access to data rows.

Row-Level Security (RLS) capability embeds and centralizes custom data access logic within the Azure SQL database which minimize the risk of accidental data access.

Azure SQL Database supports for Filter Predicates and Block Predicate.

Filter Predicate restrict row-level read access.

Block Predicate restrict row-level write access

Dynamic Data Masking is supported for v12 of Azure SQL Database.
This feature mask the organization sensitive data for unauthorized access. It hides database fields of the requested data in the query result set and there is no impact on database operations.

3. Encryption

Always Encrypted Feature to be in public preview soon.

Always Encrypted Feature is designed to protect organization sensitive data that are stored in SQL Server databases.

Always Encrypted Feature also allows clients to encrypt sensitive data inside client applications and not expose the encryption keys to the database.

The benefit of doing it is; Always Encrypted Feature always differentiate between those who own the data (and can view it) and those who manage the data (but should have no access).

The whole encryption mechanism is transparent to the business applications which is achieved by installing a Always Encrypted-enabled driver on the end-user computers which automatically encrypt/decrypt organisation sensitive data in all business applications.

This Always Encrypted driver encrypts the sensitive data only in specified sensitive data columns/fields before passing it to the SQL Server and similarly the same driver decrypts the encrypted data automatically in the query output results.

4. Compliance

Transparent Data Encryption Feature is GA and it encrypts

• Databases
• Transaction Logs
• Associated Backups

to meet the organization compliance requirements.

Transparent Data Encryption (TDE) is based on SQL Server Transparent Data Encryption Technology that encrypts the storage of the database by using AES-256 Symmetric Database Encryption Key.

SQL Server Database protects the Database Encryption Key with a Service-Managed Certificate and all key management for database copying, geo-replication, and database restores anywhere in SQL Server Database is handled by this service.

You can enable it on your database with two clicks in the Azure Preview Portal:

Go to Azure Preview Portal
Under Database Security --> Transparent data encryption

(1) Click ON and then
(2) Click Save

There is a slight downside that if you are migrating any SQL Database from On-Premises; it is required to

- Decrypt On-Premises SQL Database before Migrating
- Re-Encrypt again once it is migrated to Azure

You can achieve it by using (a) OR (b)

(a) TSQL on Azure Preview Portal

ALTER Database [SQL Database Name] 
SET ENCRYPTION ON;
GO

(b) Azure PowerShell Command

Set-AzureSqlDatabaseTransparentDataEncryption 
-ServerName ['Server Name']
-ResourceGroupName ['Resource Group Name']
-DatabaseName ['Database Name']
-State "Enabled"

Note:
Run the commands and wait for database to be encrypted.


Use this PowerShell command to check the status of this encryption process

Get-AzureSqlDatabaseTransparentDataEncryption


5. Threat Detection / Azure SQL Database Auditing

Auditing is GA on Basic, Standard, and Premium service tiers.

Azure SQL Database Auditing capabilities tracks SQL database events and writes audited events to the SQL Audit Log in Azure Storage.

Threat Detection is an additional feature of Azure SQL Auditing which gives alert unauthorized / suspicious activities on Azure SQL Databases or even at the Database Server Level.

There are many PowerShell commands available for Azure SQL Auditing

For "Database" Auditing

(i) Get-AzureRmSqlDatabaseAuditingPolicy

Example:
Get-AzureRmSqlDatabaseAuditingPolicy 
         -ResourceGroupName ""             
         -ServerName ""
         -DatabaseName ""


(ii) Set-AzureRmSqlDatabaseAuditingPolicy

Example:
Set-AzureRmSqlDatabaseAuditingPolicy 
         -ResourceGroupName ""             
         -ServerName ""
         -DatabaseName ""
         -StorageAccountName ""

(iii) Remove-AzureRmSqlDatabaseAuditingPolicy

Example:
Remove-AzureRmSqlDatabaseAuditingPolicy 
         -ResourceGroupName ""             
         -ServerName ""
         -DatabaseName ""

For "Database Server" Auditing

(i) Get-AzureRmSqlServerAuditingPolicy
 
Example:
Get-AzureRmSqlDatabaseAuditingPolicy 
         -ResourceGroupName ""             
         -ServerName "" 

(ii) Set-AzureRmSqlServerAuditingPolicy
 
Example:

- Set up the Auditing Policy of the Azure SQL Server

Set-AzureRmSqlDatabaseAuditingPolicy 
         -ResourceGroupName ""             
         -ServerName "" 
         -StorageAccountName ""

- Set the Storage Account Key of an existing Auditing Policy of Azure SQL Server

Set-AzureRmSqlDatabaseAuditingPolicy 
         -ResourceGroupName ""             
         -ServerName "" 
         -StorageAccountKey Secondary

- Set the Auditing Policy of Azure SQL Server to use specific Event Type

Set-AzureRmSqlDatabaseAuditingPolicy 
         -ResourceGroupName ""             
         -ServerName "" 
         -EventType Login_Failue

(iii) Remove-AzureRmSqlServerAuditingPolicy
 
Example:

Remove-AzureRmSqlDatabaseAuditingPolicy 
         -ResourceGroupName ""             
         -ServerName "" 


Defining for a "Database" to use Auditing Policy of  the "Database Server" 


(i) Use-AzureRmSqlServerAuditingPolicy

Example:

Use-AzureRmSqlServerAuditingPolicy 
         -ResourceGroupName ""             
         -ServerName ""
         -DatabaseName ""