Make sure you understand what Azure SQL Database Transparent Data Encryption (TDE) mitigates

Encryption of data at rest is one of the most important defenses we have in our architectural arsenal. When firewalls, authentication and authorization fail, correctly encrypted data gives the attacker nothing but a jumble of valueless bytes.

Leaked data is bad, but correctly encrypted data is substantially less worse.

I really want to stress ‘correctly encrypted’, by this I mean appropriate algorithms, keys, key derivation, key length, key storage etc.

This brings us to SQL Server Transparent Data Encryption (TDE).

Please note that TDE-type technology exists in other database engines, not just SQL Server.

What TDE Does

TDE was first introduced in SQL Server 2008 and allows an administrator to configure SQL Server so that it automatically encrypts and decrypts data at rest. The key word in the last sentence is ‘automatically.’ No code updates are needed whatsoever, no updates to client code, triggers, or stored procedures. It all ‘just works.’

But this is where knowing what TDE is designed to mitigate is critical. TDE mitigates a stolen drive or database file. If an attacker accesses a TDE-encrypted database (ie; tables etc) perhaps through a SQL injection attack, then the bad guy will get plaintext not ciphertext. This is the ‘transparent’ aspect of TDE.

Let me say that last part again, because it’s important:

TDE + SQLi == Plaintext

The Value of TDE

If the attacker can read the database file directly (eg; database.mdf), for example via a directory traversal vulnerability at the server, then they will only get ciphertext back, because SQL Server does not decrypt the data automatically.

Note this last point is also true for a database backup. It’s encrypted using TDE even when held in another location away from the SQL Server database itself.

I would argue that when using Azure SQL Database (the PaaS version, not SQL Server IaaS, that resides in a Windows or Linux VM), the value of TDE in a stolen hard drive scenario is considerably lower than on-prem. TDE provides protection against a lost or stolen disk, and Azure takes physical custody of the disks seriously; and while these scenarios are unlikely, mistakes happen, and TDE provides an extra defensive layer.

As a side note, you can read more about Azure data center physical security here.

If you use TDE, then you should use it in conjunction with keys you manage, that way if you know of an attack you can pull the keys or deny access to the keys. This way, the attacker has access only to cipher text once you pull the keys because SQL Server no longer has access to the keys. It’s not perfect, but it’s better than the attacker getting everything.

For many customers, TDE (using customer managed keys) offers protection against an internal Azure attack. Again, the likelihood of this scenario is slim, but it’s never zero.

Now What?

So what are our options?

One option is to use SQL Server “Always Encrypted“, but there are implications you must be aware of.

First, you will probably need to make code changes to an existing system. I can’t go into what you have to do because it varies by app, but don’t think you can take an existing application, change SQL Server to use Always Encrypted and expect everything to work. It probably won’t.

In my opinion, if you’re designing a new system, you should seriously consider using Always Encrypted on top of TDE. You can certainly update an existing system to use Always Encrypted, but as noted, it’s not trivial.

Always Encrypted allows you to perform only equality operations over encrypted columns that use a specific version of Always Encrypted called “Deterministic Encryption” so you will need to change the way some of your queries work. SQL Server 2019 adds support for performing more complex queries in a secure enclave, but that is available only in the non-PaaS version of SQL Server.

One final feature to look at is Dynamic Data Masking, it’s not a true security boundary, but it does help mitigate casual snooping.

Wrap Up

For an existing Azure SQL Database system that uses TDE, continue to use TDE, but I would suggest you use keys held in Key Vault if you want 100% control of the keys.

See if there’s an opportunity to move some of the more sensitive fields to take advantage of Always Encrypted. The fewer the number of columns using Always Encrypted, the smaller the chance of regressions.

Big thanks to Andreas Wolter, Shawn Hernan and Jakub Szymaszek from the SQL Server Security and Azure Security Assurance teams for their review and valuable comments.

So you want to learn Azure Security?

A few weeks ago I spoke to a new Microsoft employee who is trying to find his spot in security within the company. What follows is some advice I gave him.

Before I get started I want to share something that serves as the cornerstone for the rest of this article.

Some years ago, I made a comment that if you’re a developer working in the cloud then you need to learn basic networking, and if you’re a networking geek, you need to learn basic programming.

This comment is, in my opinion, as true today as it was when I first made the comment. The worlds of development and networking are deeply intertwined in the cloud and if you want to excel, you really need to understand both.

Now onto my Azure security advice.

Embrace the complexity

First up, cloud infrastructure is complex, so don’t be too concerned if you don’t understand all of it at once. No-one I know understood all of it from the get-go, either. When you do finally understand it, something new or updated will come along anyway! So don’t be disheartened! Just roll with the punches and keep learning.

I set aside a 2-3 hours a week in my calendar labeled ‘Learn’ and I use Microsoft ToDo to track “Stuff to Learn” as I run across items of interest where I feel I should know more.

Right now I have about 20 items on the list, and whenever I come across something of interest, I add it to the list.

Examples in the list include:

Setup an Azure account

If you don’t already have a free Azure account, sign up for one. There is absolutely nothing that can compare with getting your hands dirty. Head over here to get your free account.

Learn the basic network defenses and technologies

Azure has many network defenses, below is a list of some defenses/techs you MUST understand, I would recommend you learn these before you progress:

  • Virtual Networks <link>
  • Network Security Groups <link>
  • Service End-points <link>
  • Azure Private Link<link>
  • Web Application Firewall <link>
  • Azure Bastion <link>
  • Azure Firewall <link>
  • Azure Security Center <link>
  • Azure Sentinel (at least understand what it is) <link>
  • DDoS Protections <link>

Learn the basic application defenses and technologies

Next, you need to understand various application-layer defenses/techs, examples include:

  • Azure Active Directory <link>
  • Setting up Multi-Factor Authentication <link>
  • Azure AD Privileged Identity Management <link>
  • Service Principals and Managed Identities <link>
  • Application Gateway <link>
  • Application Security Groups (they are associated with NSGs) <link>
  • Application-specific ‘firewalls’ (eg; SQL Server, CosmosDB etc) <link><link>
  • Key Vault <link>
  • RBAC <link>
  • Azure Policy and Blueprints <link> <link>
  • OAuth 2 and OpenID Connect <link>
  • Application-specific encryption of data at rest, such as for Storage accounts <link>


Another important topic is compliance. Yes, I realize that security != compliance, but it’s a topic you must be versed in. Start here for your Azure compliance journey.

Build Something

Now that you have a basic idea of the core security-related tools and technologies available to you in Azure, it’s time to create something. When I want to learn something I build something.

Some years ago when PowerShell was still in its infancy, I asked Lee Holmes, “What’s the best way to learn PS?” He replied, “You know all those tools you wrote in C/C++/C#? Re-write them in PowerShell!” So I did, and I learned an incredible amount about PowerShell in a short time.

What you decide to create is up to you, but what I’d start with is:

  • Create two VMs in the same VNet, but different subnets – try pinging one VM from the other, does it work? Explain.
  • Using the same VMs, add an NSG to one subnet that blocks all traffic to/from the other VM’s IP address. Can you ping one VM from the other? Explain.
  • Create two VMs in different VNets – try pinging them, does it work? Explain.
  • Encrypt the hard drive of one of the VMs. You will need to create a Key Vault to do this.
  • Take a look at the NSG associated with a VM. Enable Just-in-Time (JIT) access to the VM in Azure Security Center. Now look at the NSG again. Now request JIT access and take another look at the NSG. Explain what happened.
  • Create a Key Vault, add a secret and pull out the secret from, say, an Azure Function. This is quite complex and requires you add a managed identity to the Function or run the function in the same VNet as the Key Vault.
  • If you used a managed identity in the example above, make sure you assign least privilege access to the Key Vault (ie; read access to secrets and nothing else)
  • Create a custom role with very specific actions.
  • Create a blob in a Storage Account. Experiment with the various authorization polices, most notably SAS tokens.
  • Install Azure SQL and configure ‘Always Encrypted’
  • Use Azure Monitor to see who is doing what to your subscription.
  • Set an alert on one of the event types.
  • Open Azure Security Center – look at your issues (red). Look at the compliance issues (PCI etc)
  • Remediate something flagged by ASC.
  • Set a policy that only allows a hardware-backed Key Vault and create a non-HSM KV (ie; not Premium). Use this as a starting point Remember, it can take 30mins after a policy is deployed to it being effective. I previously wrote about Policy here.

I could keep going with more examples and I will update this list over time!

As a side-note, I often use a resource group named rg-dev-sandbox, when experimenting, that way I can blow the resource group away when I am done, leaving nothing behind.

Go Deep

After you have learned and experimented, it’s time to go deep. Pick a product, say Azure SQL Database, and learn absolutely everything there is to know about security, reliability, compliance and privacy for that product. For a product like Azure SQL, this would include:

  • Access Policies for data at rest
  • Crypto for data at rest (TDE, Always Encrypted, Column Encryption)
  • Crypto for data on the wire (ie; TLS!)
  • Auditing
  • Disaster recovery
  • Secure access to connection strings
  • Azure AD vs SQL Authentication
  • Data masking (ok, not REAL security, but useful nonetheless)
  • Threat Protection
  • Azure SQL firewall (note a lower-case ‘f’ as it’s not a true ‘F’irewall)
  • SQL injection issues and remedies

Consider AZ-500 Certification

I know some people are cynical about certification, but the Azure certifications are not easy and from customers I have spoken to, they are welcome and required. I worked with a large financial organization for over a year and they required their staff working on Azure get certified in various Azure topics. You can get more information about certifications here.

AZ-500 measures Azure security knowledge, and the exam includes labs. I would highly recommend you read the skills outline. Even if you don’t take the exam and get certified, this is a broad set of security-related items you really ought to know.

Wrap Up

I hope this helps you on your journey through Azure security, even if this post only skims the surface!

But remember, as soon as you understand it, something will change, so stay abreast of new features and function by monitoring the Azure Heat Map.

Big thanks to my colleague Ravi Shetwal for his review and feedback.

Who is Authenticating Whom?

I have worked on too-many-to-count threat models over the last twenty or so years and invariably I have to explain, “Who is authenticating whom?”

Let me explain the issue with a simple example.

In this scenario, a user communicates through an API App on Azure which in turn communicates with a CosmosDB instance.

When I ask the question, “How do you authenticate the API App?” The response is often, “We use a username and password” and here’s where the confusion lies: the answer (uid and pwd) is the user’s credential.

What I do at this point is hop up to the whiteboard to explain that both communicating parties should authenticate one another.

First, when the user connects to the API App, how does the user know that the API App is the correct API App and not a rogue? The answer is to use server authentication and the correct answer most of the time is to use TLS.

That’s what I meant when I asked, “How do you authenticate the API App?”

Next, how does the API App know the user is a valid user of the system?

That depends on what the server, in this case API Apps, supports.

Different server technologies offer various ways to authenticate incoming connections from users. Examples include client TLS, username and password (with 2FA!) and delegating to a federated identity provider, such as Google, Facebook, Twitter or Microsoft ID.

A common way to authenticate a valid user is through the use of access keys, which are often implemented as a long, random series of bytes that are passed in the client request. Here’s an example from an Azure Storage Account:

Another method to authenticate and authorize a client is to use Shared Access Signatures. Here’s an example from the same Storage Account:

…and a resulting SAS token:

This page and this page explain the client authentication (not authorization) policies supported by Azure API Apps.

Let’s be super secure and in our solution we’re going to use TLS at both ends: TLS is used to authenticate the server and TLS is used to authenticate the client.

Let me state this another way.

When the user connects to a server, the user’s software (browser) authenticates the server is the correct server. This is SERVER authentication.

On accepting a user’s connection, the server verifies the user is who they are. This is CLIENT authentication.

But it gets a little more complex. When the API App connects to CosmosDB, how does CosmosDB know the connection is from a valid source and how does the API App know it is talking to the correct CosmosDB?

In this scenario, the API App is now the client, and CosmosDB is the server. When building the threat model, you might decide that you don’t need to authenticate the two to each other. That’s fine, so long as is it’s the threat model and the rationale for that decision.

API App authenticating CosmosDB, the server, is easy: use TLS, which is enabled by default anyway in CosmosDB.

When the API App connects to ComsosDB, it can provide and key to do so. This key should be stored in Key Vault and use a Managed Identity for the API App to access Key Vault.

Now I think about this, I might do a Proof of Concept for the entire solution to show how it’s done and build the threat model while I am at it. There’re many more ways to restrict access at the backend, such as only allowing CosmosDB to listen on specific IP addresses or ranges, and in this scenario, we can use the backend IP address of API Apps. This way CosmosDB will ONLY allow traffic from our API App. Let me know what you think!

Anyway, to wrap up. When working with a threat model, or looking at the security of any architecture, you must always consider:

“Who is authenticating whom”

or… how is the client authenticating the server (server authentication) and how is the server authenticating the client (client authentication)?

Azure Monitor Activity Log Change History (Preview)

There’s a new public preview feature named Change History in Azure Monitor Activity Logs that allows you to get a better feel for what caused events in the first place.

I don’t know about you, but wading through a JSON description of an event to determine what happened can be a little cumbersome at times, and this feature is a great time-saver.

To get to the feature, go to Monitor and then click Activity Log:

This is the list of all your management plane activities across your subscription consumed by Azure Monitor.

NOTE: If you don’t see Monitor in the list on the left, type monitor in the Search resources, services, and docs textbox at the top of the portal:

Below is an expanded example of the Activity log from my personal subscription:

Now click on an Update event – in this example, I will click the first event. As you can see I have been experimenting with Azure Private DNS; I had just got done running this example.

Notice there’s a new option named Change history (preview):

There’s a list of changes to this resource shown and if I click any of these entries, the following pops up:

As you can see – right there is the diff – this change was me changing a tag named vmRelated from True to False.

More info is available here.

Simple, but effective!

Azure Logic Apps and SQL Injection Vulnerabilities

This code takes a property from an untrusted HTTP post named name and uses that as input to a SQL statement. If I use an HTTP POST tool, like Postman, I can test the code:

In this example, the name Mary is inserted into the database and the code is working as intended. But if the untrusted input is changed to something like this:

Well, I think you know what happens! Assuming the connection to SQL Server has enough privilege to delete the foo table this code will go ahead and delete the foo table.

Remember, one good definition of a secure system is a system that does what it’s supposed to do and nothing else. In this example, the code can delete a table, and that is far above what was written in the spec!

Most common SQLi attacks are used to exfiltrate data when using SQL select clauses using the classic or 1=1 -- string and the myriad of variants.

The Remedy

The remedy is to use another SQL Server action rather than the Execute a SQL Query action. The SQL Server connector has plenty of other, more constrained actions.

You can also use the Execute Stored Procedure action, as that will use SQL parameters under the hood.

If you must accept untrusted data in order to construct a SQL statement, make sure you use SQL parameters as shown below. Note that using parameterized queries to build SQL statements is as true today as it was well over a decade ago!

Notice how the SQL query has changed to use @name, which is a parameter declared using the formalParameters option.  The key is the parameter name and the value is the SQL data type which should match the table definition.

Now the value of this parameter can be set to reference the name dynamic content that came from the HTTP POST request in the actual parameter value below

So there you have it, use SQL parameters! Be careful out there!

I hope that helps, if you have any questions please leave a comment.

A Common Pattern – Using Insecure JavaScript Libraries

I will be up front and state that I am no JavaScript fan. I REALLY liked it back in the day when it was readable! I much prefer TypeScript as it fosters more robust, readable and maintainable code, especially for large projects. But that’s just my opinion.

With all that said, I understand JS’s importance in today’s modern toolset, and I am fine with that.

But there’s an alarming trend I have noticed with many customers who rely on common JS frameworks such as jQuery, Angular, Bootstrap, React and so on. Seems there’s a new framework every week!

The trend is devs using insecure JS libraries. 100% of the customers I have worked with over the last year or so have had at least one insecure JS library.

My old boss, Steve Lipner, coined a term about 20 years ago to describe these kinds of dependencies: he used the term ‘giblets’.

A giblet is code you depend on but you don’t control and JS frameworks are a prime giblet. If a JS framework you use has a security vulnerability, then you have a security vuln, too! Here’s a little more info on giblets.

So how do you remedy this issue of using insecure JS libraries?

First, you need to understand which libraries you use, and keep abreast of any security vulnerabilites therein. Someone in your org needs to keep track of this; it’s part of their job!

Next, you need to make a hard decision. Does your application host a copy of the libraries and possibly be behind on security fixes? Or, does your application pull the latest-n-greatest from the source and possibly run the risk of regressions? I cannot answer that question for you, you need to determine the policy and live with the tradeoffs offered by each scenario.

As a side note, there’s a great add-on for Chrome named Retire.js that will flag potentially vulnerable JS libraries from the browser. It produces output like the image below when you navigate to a web page that references a vulnerable library.

I want to wrap this up with a story.

One customer told me they had to use an old and insecure version of jQuery for compatibility with Internet Explorer on Windows XP! After looking at their analytics, they realized that some customers were still using Windows XP, but it was only three customers out of thousands!

So we added landing-page text that said, “We see you’re still using Windows XP! Windows XP is no longer supported by Microsoft, and we will retire support for Windows XP on September 30th 2019. After this date you might encounter issues using our system. Please upgrade before then.”

I think that’s reasonable!


Azure Policy – A Love Story

In a previous post I briefly mentioned the policy-enforcement capability built into Azure. If you have not seen Azure Policy, used it or simply not aware of it, you really need to know about it!

Before I start, this post is merely to make you aware Azure Policy exists, it’s not a complete tutorial by any stretch and it’s policy from my perspective.

So what is Azure Policy? It’s simply a way to make sure specific conditions exist and if the conditions don’t exist then either raise an audit event or block the action. There’re other options as well, but audit and deny are, by far, the two most important outcomes.

For example, some conditions might be:

  • Require TLS to blob store
  • Secrets in Key Vault must be in hardware
  • Don’t allow VMs to have a public end point
  • Require Threat Detection on Azure SQL Server
  • Require HTTP/2 for Azure Functions

I could keep going, but I am sure you get the picture. The beauty of Azure Policy is once it’s set, enforcement is performed automatically.

So let’s look at an example. Many customers want to, or need to control where their data resides. Data sovereignty is a big deal for some customers. For example, a customer might not want data in specific countries because of legal or regulatory reasons.

A customer wants their CosmosDB data available only in specific regions. One of the nice things about CosmosDB is there’s a simple UI that makes it easy to replicate data geographical. Of course, with that ease and power comes responsibility!

The customer wants to put a requirement in place that limits data replication to only East US, East US 2, South Central US and North Central US. That way ‘something’ comes to the rescue if someone fat-fingers the CosmosDB replication UI, or ‘accidently’ runs a PowerShell script that configures data replication all over the world.

Enter Azure Policy.

I won’t lie, Azure Policy files are a little obtuse. They are declared in JSON. The following file enforces a rule that only allows CosmosDB data in the four regions mentioned above.

     "properties": {
         "displayName": "Limit CosmosDB to specific locations",
         "policyRule": {
             "if": {
                 "not": {
                     "field": "Microsoft.DocumentDB/databaseAccounts/Locations[*].locationName",
                     "in": [
                         "East US",
                         "East US 2",
                         "South Central US",
                         "North Central US"
             "then": {
                 "effect": "deny"

Here’s how to read it. It all starts at the policyRule block. Before I start, remember that ComsosDB was known as DocumentDB.

“if the database account locations is not in East US, East US 2, South Central or North Central US, then deny the action. “

In other words, if someone attempts, by any means, to set a data replication site other than one of the four, the request is blocked.

If you change the “deny” to “audit” the action will go through, but an audit event is raised. This is useful if you want to roll out policy in an existing environment and you want to get a feel for how bad things are without stopping the business from running 🙂

Some customers think in terms of preventative and detective controls. “Deny” is preventatve and “Audit” is detective.

This is just the tip of Azure Policy. If you want to experiment, take a look at some of the sample policies available on GitHub.

One final thought. The title says “A Love Story” and clearly that’s a little extreme, maybe even clickbait, but Azure Policy is one of the most important security compliance features built in to Azure.