5 ways to secure your SQL data in Microsoft Azure

Data security in the cloud is of chief concern not only to healthcare and financial services, but anyone with sensitive data of any kind that should only be disclosed to authorized parties. No discussion of enterprise security would be complete without a look at data protection and governance.

For purposes of this discussion, data comes in two forms:

  • Structured. Structured data refers to kinds of data with a high level of organization, such as information stored in a relational database, as in Microsoft SQL Server.
  • Unstructured. Unstructured data refers to data that is not contained in a database or some other type of data structure. Examples include email messages, Word documents, PowerPoint presentations and instant messages.

Important considerations in data protection and governance include data classification and rights management, encryption at-rest and in-flight, as well as management and storage of encryption keys and other secrets related to securing data.

Securing Structured Data In-Flight & In Use

SQL Server 2016 (both SQL in VMs and Azure SQL) introduces some new capabilities to prevent unintentional leakage of data by misconfigured applications or security controls. Key highlights are listed below:

#1 Always Encrypted:

This is a client-side encryption capability, enabling the application to encrypt data so the SQL server (or service if using Azure SQL) can never see the data. This is particularly useful for protecting content such as SIN/SSN, Credit Card, and private health identifiers.


#2 Row-Level Security:

This allows the organization to create policies which only return data rows appropriate for the user executing the query. For example, this allows a hospital to only return health information of patients directly related to a nurse, or a bank teller to only see rows returned which are relevant to their role. For more info, see https://msdn.microsoft.com/en-us/library/dn765131.aspx.

#3 Dynamic Data Masking:

This allows the organization to create policies to mask data in a particular field. For example, an agent at a call center may identify callers by the last few digits of their social security number or credit card number, but those pieces of information should not be fully exposed to the agent. Dynamic Data Masking can be configured on the SQL server to return the application query for the credit card numbers as XXXX-XXXX-XXXX-1234.


These capabilities help prevent and mitigate accidental exposure of data while it is in-flight or in-use by a front-end application. For more info, see https://msdn.microsoft.com/en-us/library/mt130841.aspx.

Securing Structured Data At-Rest

Protection of SQL data at-rest is a feature that has been around for a long time now, which the SQL Server product team at Microsoft has enhanced in the 2016 release.

#4 SQL Transparent Data Encryption

In order to protect structured data at-rest, Microsoft first introduced SQL Transparent Data Encryption in SQL Server 2008. This technology protects data by performing I/O encryption for SQL database and log files. Traditionally a certificate that SQL Server manages (and is stored locally within the SQL master database) would protect this data encryption key (DEK). In June 2016, Microsoft made a significant enhancement to this capability by making generally available a SQL Server Connector for Azure Key Vault.


Image credit: Microsoft

This allows organizations to separate SQL and Security Administrator roles, enabling a SQL Administrator to leverage a key managed by the security operators in Azure Key Vault, with a full audit trail should the SQL administrator turn rogue. This connector can also be used for encrypting specific database columns and backups, and is backward compatible all the way back to SQL 2008.

More info at https://msdn.microsoft.com/en-us/library/dn198405.aspx

Detecting SQL Threats

In addition to securing SQL data, we also need to consider protecting data sources from the threats that would lead to breach.

#5 SQL Threat Detection

Running SQL in the cloud brings some additional benefits. For databases running on the Azure SQL service, the new SQL Threat Detection service monitors database activity and access, building profiles to identify anomalous behavior or access. If suspicious activity is detected, security personnel can get immediate notification about the activities as they occur. Each notification provides details of the suspicious activity and recommendations on remediating the threat.

SQL Threat Detection for Azure SQL Database can detect threats such as the following:

  • Potential Vulnerabilities: SQL Threat Detection will detect common misconfigurations in application connectivity to the SQL data, and provide recommendations to the administrators to harden the environment.
  • SQL Injection Attacks: One of the most common approaches to data extraction is to insert a SQL query into an unprotected web form, causing the form to return data that was unintended. SQL Threat Detection can identify if an attacker is attempting to leverage this mechanism to extract data.
  • Anomalous Database Access: If a compromised database administrator account starts to execute queries from an abnormal location, SQL Threat Detection can detect and alert on the potential insider threat or identity compromise, enabling the security personnel to update firewall rules or disable the account.

SQL Threat Detection for Azure SQL Database is a powerful new tool in detecting potential data leakage threats. For more info, see https://docs.microsoft.com/en-us/azure/sql-database/sql-database-threat-detection.

I hope you’ve found this short read on some of Microsoft’s capabilities for protecting structured data valuable. Questions or comments? Feel free to leave your thoughts in the comments section at the end of this article.

3 Truths of Modern Enterprise Security

The threat landscape, specifically the types and sources of threats, has changed significantly in the last few years. There are a number of readily identifiable causal factors, including:

  •  Changes in technology. The introduction of new technology results in weaknesses that are related to low technological maturity, improper use, improper integration with existing systems, low user awareness, etc.
  • Advances in the capabilities of threat agents. The skills, available tools, available resources, information on exploits and motivation of threat agents (sometimes called ‘threat actors’) has evolved. The types of threat agents have evolved from the script kiddies of the 90’s to today’s well-resourced and highly sophisticated hackers, who in some cases are even sponsored by malicious nation states.
  • Data growth. 90% of all the world’s data has been produced in the last two years, and with the growth of myriad devices (the Internet of Things), the target just keeps getting bigger.

With the evolution of the threat landscape comes the need to modernize our thinking and approach to security and identity. Make no mistake, firewalls and antivirus are no longer enough. To help frame some of the key challenges, here are three truths of modern enterprise security, along with some free resources to start you on the journey to modernize your approach.

Truth #1: Your trusted network is not as secure as you think

While your trusted corporate network may seem like the simplest resource to secure, it may be, in fact, the most vulnerable. Some of the most common points of entry to your trusted network are through browser exploits, malicious document delivery, and phishing attacks. What these exploits all have in common are that they target what is perhaps the greatest vulnerability on your network – the end user. The reality is that trusting users can be fooled into clicking malicious URLs. This may result in the opening of infected e-mail attachments that install malware or ransomware on client computers, letting hackers and thieves through your secure network perimeter undetected.

This malware often lives undetected on your trusted network for an average of more than 200 days, listening to conversations, waiting to uncover network credentials, then stealing these secrets that enable lateral movement through your environment. This challenge is compounded by compromising more systems and uncovering more credentials, eventually enabling vertical movement from client to server.

Think your users are too smart to be lured into a phishing scam? Just ask Chairman of the Clinton presidential campaign, John Podesta, who fell for a phishing scam that landed his email archive on Wikileaks!

Truth #2: The network perimeter, as you know it, is history

The traditional model of the network perimeter, including firewalls and proxies and a perimeter network (aka DMZ), is dead. The perimeter, where access and authorization are enforced, can be the login screen on a mobile device, or an app installed on that device. The app is the window to your corporate data (content), and the new perimeter is the content and context by which the user tries to access that data.

The Cloud Security Alliance (CSA) advises that “identity management in the context of cloud computing should not only manage the user identities. It should extend this to manage cloud application/services identities, access control policies for these cloud applications/services, as well as privileged identities for the applications/services, etc.”.

With this in mind, organizations must rethink their approach to identity management, authentication, and authorization in a world that did not exist when the concept of username and password entered on a PC behind a trusted network were conceived. With an increasingly mobile workforce, Multi-factor authentication (MFA) is a must and policy-based authentication that evaluates the full context of the authentication attempt (user, device, location, date/time, app and data) is more important than ever.

And what about the security of your sensitive corporate data on employees’ personal mobile devices, full of unmanaged apps and direct connectivity to personal cloud storage?

Truth #3: Breach will happen…and you need to be prepared when it does

While post-breach detection may feel “too little too late”, it is actually a critical layer of defense, particularly as your efforts to mature your security posture in a race against an ever-evolving threat landscape. When a breach has occurred, detecting both weak spots and actual breaches in the context of your computing environments, as opposed to a single device, is absolutely critical to providing context and visibility into the scope of items that need attention.

It is one thing to see an alert on an infected computer in your trusted network. It is quite another to see lateral movement of a malicious entity in your environment through a suspicious pattern of behavior with a common set of compromised credentials. In this case, detecting and squashing lateral movement at the client tier can prevent the next step in the intrusion process…listening for and capturing privileged credentials that enable vertical movement into server and application tiers containing sensitive business and customer data.

Finding answers to the big questions

While important, these three truths are just the tip of the iceberg and raise some very important questions:

  • How do you defend against the weakest links in your trusted, on-premises network?
  • How do you secure your sensitive corporate information on devices that could be anywhere…and beyond your management reach?
  • What type of post-breach defense can you implement to ensure you have eyes on the presence and scope of a security breach?

Making the big picture of security and identity in a cloud-first, work-from-anywhere world, full of threats that marginalize the efficacy of traditional tools and techniques can seem an impossible task? I have two concise (and free!) resources I’d like to share with you to help you on your journey:

E-book: Defending the New Perimeter: Modern Security for the Enterprise

This comprehensive, yet concise guide to Microsoft’s approach to modern enterprise security will help you get a handle on how you can implement a strong, comprehensive cybersecurity strategy with a single vendor.

Download your free copy at http://modernsecurity.info.