Azure SQL Database Security Features
The Microsoft Azure platform is evolving fast. Azure SQL Database, which is a Relational Database service running on Azure, is riding high on the cloud wave with new features enabled at a fast pace. I want to share a few Azure SQL Database security features currently in GA or public preview) that could help developers and DBAs develop and manage a secure SQL Database solution. All security features mentioned in this blog are available for Basic, Standard, and Premium databases in v12 servers.
| Feature | Status |
Target scenario |
GA |
All |
|
GA |
All |
|
GA |
Log data access/change trails for regulatory compliance |
|
Public preview (V12) |
Obfuscate confidential data in the result set of a query. |
|
Row-level security (RLS) |
Public preview (V12) |
Multi-tenant data access isolation. |
Firewall (GA) – This feature has been available for Azure SQL Database since the very beginning. It’s a way for DBAs to control which clients, based on IP addresses, can access a logical Azure SQL Server or a specific database. By default, for a newly created logical server, no firewall rules are defined and nobody outside of Azure can access any database on that server yet. You must define a rule to start the first connection. Note the firewall rule IP ranges between server level and database level don’t overlap. You may also allow other Azure services to access your server or database using a single rule by selecting a checkbox rather than based on IP addresses.
Secure connection (GA) – SQL Database requires secure communication from clients based on the TDS protocol over TLS (Transport Layer Security). Note for application to be truly protected against man-in-the-middle type of attack, we encourage you to follow these guidelines to explicitly request an encrypted connection and do NOT trust server side certificate.
Auditing (GA) – Allows customers to record selected database events in log files for alerting and post-mortem analysis, for example, as part of maintaining regulatory compliance such as PCI, HIPAA. Common auditing events include insert, update, and delete events on tables. Using SQL Database Auditing, you can store the audit logs in Azure table storage and build reports on top of them. There is preconfigured dashboard report template available for download (requires Excel 2013 or later plus Power query). SQL Database Auditing requires the use of a secure connection string.
Data masking (public preview) – Is a policy based security feature that limits exposure of sensitive data like credit card numbers, social security numbers, clinic patient info to non-privileged users. Similar to Auditing, it’s useful for scenarios with compliance requirements. You may specify masking rules to be applied to designated fields, either at source (tables/columns), or at results (alias used in queries). Note that masking rules are applied to the appropriate data in the result set of a query. Unlike encryption, data masking does NOTprotect sensitive data at rest or during query processing in memory. Data masking requires the use of a secure connection string.
Row-level security (RLS) (public preview) – The feature is aimed at multi-tenant applications that share data in a single table within the same database. Typically, application developers currently have to build logic in the application code to isolate tenants from accessing each other’s. In contrast, RLS centralizes the isolation logic within the database, simplifying application design and reducing the risk of error. With RLS security policy managers can encode the isolation logic in a security policy using inline table-value functions. An example of how to use RLS in a middle-tier, multi-tenant application can be found here.
Additional security resources: