WELCOME TO CLOUD MATTER

Search
  • Roman Guoussev-Donskoi

Securing Azure at scale. Azure SQL

Updated: May 14, 2019

Securing Azure at scale requires planning and automation. Microsoft provided amazing tools for this and keeps improving them. But overall task is pretty large and can be overwhelming.


To tackle this I believe we can spend regularly 15-30 min to protect Azure environment and deliver tangible results every time. This helps with milestones and to demonstrate results and get permission to spend more time :) . So this brought the idea to write several posts to demonstrate this approach. Here is the first.


Below is personal thoughts only and does not reflect opinion of my employer or anyone else. Keeping it short and simple

  1. Secure your authentication and access control

  2. Secure your data

  3. Secure your network perimeter

  4. Secure you applications

All needs to be done but I believe steps 1. and 2. go first since the rest of the steps are dependent on 1. and if we do not protect data then the rest of security is on no much benefit...


Secure your authentication and access control

  1. Make sure all users access leverage multi-factor authentication. Azure Active Directory makes this easy.

  2. Make sure you use Managed Identity whenever possible. For more details you can check The best way to protect application credentials is not to have them - meet Azure Managed Identity

  3. Make passwords random-generated and long (e.g. 128 characters) and store them in Key Vault when using Managed Identity is not feasible.

Secure your data

Will start with Azure SQL.

Azure SQL Authentication

Configure Azure Active Directory authentication for Azure SQL servers. In combination with MFA and using Managed Identity this reduces the need for using SQL authentication and makes you more better protected against brute-force password attacks.


Believe the best practice here is adding a Active Directory group as opposed to individual so you can manage access via membership in this group.




Similarly it is a good idea to create Azure Active Directory group for individuals and managed identities that share the same set of privileges in database.


For example

1. Create group in Azure AD and add users and App Service that have identity configured for them (The best way to protect application credentials is not to have them - meet Azure Managed Identity)



2. in Azure SQL use the name of the group to create user and grant roles:

CREATE USER [xxx-group-xxxx-readwrite] FROM EXTERNAL PROVIDER

GO






3. Now you applications and users added to this group can connect to the database


For automation examples are provided in Azure-automation/AzureSQL


Azure SQL Networking

Use Virtual Network Service Endpoints and firewall rules to secure your Azure service resources at the network level.

For scalable/supportable implementation we store Virtual Network Service Endpoints configurations and sets of IP CIDRs in our database as named entities (e.g. "all datacenter external IP addresses", "my awesome application vnets"). This allows re-use and provide easier and more supportable configurations facilities than specifying start/end of IP ranges as provided by Azure API and portal. The automation code parses those definitions and translates into Azure Power Shell "New-AzureRmSqlServerFirewallRule" and "New-AzureRmSqlServerVirtualNetworkRule" commands.





Azure SQL Audit

Azure SQL Audit must be enabled on any production database (i'd argue it should be enabled on any database :) ). It takes no effort to turn it on and Audit links nicely with Azure logs in Azure Monitor (previously known as Log Analytics) so you can answer the questions like "My application is no longer functional! Who has dropped the table?"


Microsoft documentation is Get started with SQL database auditing and logs in Azure Monitor



Note:

*** Please always use custom audit policy

I'd strongly advise against turning audit from Azure portal or using default audit policy. The default audit policy will audit all SQL statements issued against database and can easily generate gigabytes of data, hundreds of thousands of records and incur significant cost.


Instead do something like below:

Set-AzSqlServerAuditing -ResourceGroupName ... -ServerName ... `

-AuditActionGroup @("SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP","DATABASE_OBJECT_CHANGE_GROUP", "SCHEMA_OBJECT_CHANGE_GROUP") -State Disabled -LogAnalytics -WorkspaceResourceId ...


Specify your own policy. See Set-AzSqlServerAuditing for details.

You can use the following to check the audit policy details

$srvaud=Get-AzSqlServerAuditing -ResourceGroupName ... -ServerName ... -LogAnalytics




Query Audit

Microsoft even goes to trouble to write queries for you so you do not need to start from scratch - just hit "View Audit Logs", then "View in OMS" above and it will bring you to Log Analytics with the query already pre-crafted to read you Audit Logs.





- just add your own logic (e.g. I added the clause for searching the specific drop table statement):



To validate audit records kept to reasonable amount you can use something like below

search * | where Category == "SQLSecurityAuditEvents" | summarize count() by action_name_s, session_server_principal_name_s, bin(TimeGenerated, 1day) | sort by bin(TimeGenerated, 1day)





Azure SQL Advanced Data Security (ADS)

Microsoft documentation Advanced data security (ADS) for Azure SQL Database provides great description and step-by-step getting started. The cost at the moment is less than 20 CAD/server/month so believe this option should be enabled all production and test databases.


For to check the state of Azure SQL security head to Advanced Security section of your Azure SQL database blade.


Article SQL Vulnerability Assessment service helps you identify database vulnerabilities is a pretty good start. Baselines save tons of time here - Azure flags the violations which you can act upon or make the part of the baseline - all from the same blade in Azure portal.


Microsoft provides automation documentation in Manage Vulnerability Assessments using Azure PowerShell and in Azure SQL Vulnerability Assessment – now with PowerShell support!


Encryption

To be added soon.


Availability

To be added soon.


References




92 views0 comments

Recent Posts

See All

Databricks is an amazing platform for data engineering, data science and machine learning. One of the critical requirements of secure data processing is data audit - the ability to identity what data

SAS access to storage account is very convenient and easy and while Microsoft recommends that you use Azure AD credentials when possible as security best practice still SAS sometimes hard to avoid. Le

 

Subscribe

 

CONTACT

Your details were sent successfully!

Computers