Inside Azure SQL Database: Security Features

Editor's note: This article has been updated to note that Microsoft now offers its standalone Data Classification, Vulnerability Assessment (VA) and Threat Detection solutions as part of the new SQL Advanced Threat Protection (ATP) package, which consolidates these features into a centralized security experience for Azure SQL database.

Data security and privacy have come to the forefront of conversation. Thanks to a never-ending supply of data breaches and leaks, we are all being given an education in how not to handle security (or security breaches). For many, security is an afterthought, coming after applications and databases are designed. Functionality will outweigh security every time.

Microsoft knows that security is hard. In the past few years the company has spent considerable time and effort to make it easier. You knew the tide was turning when Microsoft made its security features available in all editions of Azure SQL Database. In a more recent step forward, Microsoft has consolidated its key data security offerings into a comprehensive package, SQL Advanced Threat Protection (ATP), which bundles data classification, vulnerability assessment and threat detection features into a single platform.

Security shouldn’t be something you pay extra for; it should be something you can access, easily, at any time.

Existing Threat Detection customers will be automatically ported to the new offering, but all other customers must opt in to the service. If you haven’t migrated yet, this piece explores the features and functionality of your current version: Audit and Threat Detection and Vulnerability Assessment. Included in these two features are three essential functions: audit capabilities, threat detection and vulnerability assessment. These features would take a long time to develop and/or would be expensive to buy, but Microsoft is building them into the platform.

Here is a look at each:

Audit and Threat Detection

You can get started with Audit and Threat detection by navigating either to your logical instance or to a specific SQL Database in the Azure Portal. Here we will show an example by starting at a specific SQL Database. On the left, select “Auditing & Threat Detection”:

The subsequent blade that appears has a handful of items to discuss. First, at the top there is a notice that states clearly how the server settings will apply to all databases, regardless of the database setting. That means if you enable the SQL Database as well as the server, events are collected twice, which means they are stored twice. You can click there to review the server settings.

There are two scenarios in which you would want to enable auditing and threat detection at both the server and database level. The first is if you want or need to use distinct storage accounts. The second would be if you need to monitor the database for something specific that you don’t want done at the server level (and thus for all databases). Essentially, you can customize the settings per database, if desired.

Clicking on “Threat Detection Types” will open a new blade:

The last item to discuss is the email address. You can have alerts pertaining to threat detection sent to an email address listed here. There is no need to configure Database Mail or an SMTP server, or to build your own SQL injection detection system. It’s all part of the Azure service.

In addition, while SQL Audit is a feature in the Earthed version of SQL Server, auditing in Azure SQL Database is far simpler to implement. Threat detection is only available in Azure. Once enabled, if your database or web application detects a threat, you will receive emails that look like this:

Vulnerability Assessment

Microsoft announced the public preview of Vulnerability Assessment at Ignite late last year. This feature will evaluate your SQL Database against a baseline of rules to check for possible security vulnerabilities. You can find Vulnerability Assessment (VA) located right below Auditing and Threat Detection when you are on the main page for your SQL Database.

When you navigate to VA and run a scan you will see a page like this:

You can review the items in the “Passed” tab to see what the vulnerability scan is checking for. In the screenshot above you can see that we have two checks that failed, the risk associated with each, and whether a baseline has been set. With VA you can run a scan, get results and then make the result a baseline for future scans. This gives you the flexibility you need to tailor your baseline for specific SQL Databases.

VA also allows for quick remediation. Clicking on the “Sensitive data columns should be identified” row opens a blade that lists specific columns:

 From here you can decide to use a feature such as Dynamic Data Masking (DDM), and with just a few clicks you can have DDM applied to the columns.

VA is now generally available for both Azure and SQL Database and for traditional Earthed versions of SQL Server via SQL Server Management Studio.

For years I would hear naysayers dismiss the idea of using the cloud, questioning the security of data with a cloud provider. These same people would hand out “sa” passwords like candy on Halloween and put off security patches because “something might break." These people may not have noticed, but Microsoft Azure has suddenly become the safest cloud around. If you don’t believe me, then go review the long list of certifications available in the Trust center.

Microsoft understands that data is the most critical asset that any company owns. It’s time we treat it as such.

Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry as a programmer, developer, analyst, and database administrator. LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010, where his research and experience helped to create the initial versions of the software now known as SolarWinds® Database Performance Analyzer.

LaRock has served on the board of directors for the Professional Association for SQL Server (PASS), and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses on working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2. He’s made it his mission to give IT and data professionals longer weekends.