Saturday, December 30, 2017

Azure Storage Firewalls and Azure SQL Audit

In a previous post, I started to take a look at the use of Azure Storage firewalls.  We noted that the firewalls seemed to work quite well for protecting against IP address access.  Further, it was noted that you can whitelist certain Azure Trusted Services, which was a fairly limited list.

Okay, so lets test this with Azure SQL Auditing.  The test for this is quite simple.

  • Enable Azure SQL Auditing to a target storage account
  • Do some logins
  • Use the audit viewer in Azure SQL to review the audit
  • Enable Azure Storage Firewall
  • Do some more logins
  • See if these logins appear in the audit
 It turns out that when the firewall is enabled, Azure SQL audit cannot write to its audit file.  This is super apparent as the blade itself stops responding.  Here is what it looks like:

When you allow the firewall access from all networks, you can then see the log again.  You will also notice that the logins during the time when the firewall was enabled are not showing up (or, in other words, were never written).  You can see this via the missing timestamps.

It seems weird services such as Azure SQL (at least the audit part) would not be part of the trusted services.  IMHO, it makes some of the use cases for storage firewalls not possible.  Storage accounts that host things like audit and ASC are not ones that you want to be public and a firewall would make sense.

Sunday, December 17, 2017

Fooling around with Azure Storage Firewalls

At the end of September, the Azure storage team announced support for virtual network connected storage.  In the days before managed disk, this was actually quite a big deal.  A lot of my customers got tripped up on the fact that VHDs are stored in Azure storage, and that the container itself could be accessed from anywhere provided someone had the correct name and key.

Azure storage makes use of Azure storage firewalls to allow customers to limit access to a storage account either by subnet/vnet or by any publically addressable IP address (or range).  The goal of this post is to explore this feature in a little more detail.

When you first create an Azure storage account, the default operation is allow access from all vnets/subnets.  In fact, firewall setup is not currently part of the storage account creation process in the portal.  Here is what the firewall tab looks like out of the gate:

Okay, cool.  I can open up storage explorer and add a file no problem.  Lets try setting the "default rule" to deny.  What this will do, essentially, is limit access to my storage account from anywhere.

Here is the powershell command I ran:

Update-AzureRmStorageAccountNetworkRuleset -ResourceGroupName  -Name  -DefaultAction Deny

Here is the result in the portal.

Lets try uploading something in Storage Explorer.  Please note that I did not "log out and log back in" in storage explorer.  Oops! I got an error.

  "name": "StorageError",
  "message": "Forbidden",
  "stack": "Error\n    at Function.StorageServiceClient._normalizeError (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\lib\\common\\services\\storageserviceclient.js:1189:23)\n    at Object.StorageServiceClient._processResponse (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\lib\\common\\services\\storageserviceclient.js:736:50)\n    at Request.processResponseCallback [as _callback] (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\lib\\common\\services\\storageserviceclient.js:311:37)\n    at Request.self.callback (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\node_modules\\request\\request.js:188:22)\n    at emitTwo (events.js:106:13)\n    at Request.emit (events.js:194:7)\n    at Request.<anonymous> (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\node_modules\\request\\request.js:1171:10)\n    at emitOne (events.js:96:13)\n    at Request.emit (events.js:191:7)\n    at IncomingMessage.<anonymous> (C:\\Program Files (x86)\\Microsoft Azure Storage Explorer\\resources\\app\\node_modules\\azure-storage\\node_modules\\request\\request.js:1091:12)",
  "code": "Forbidden",
  "statusCode": 403,
  "requestId": "xxxxxxxx"

Awesome, a pretty standard forbidden error message.  Okay, lets try adding my ip address so I can communicate. You can do this by running the following command:

Add-AzureRmStorageAccountNetworkRule -ResourceGroupName  -Name  -IPAddressOrRange ""

Now when I try and upload, everything works.  I was simply able to hit retry and it worked.  I now have a "secured" storage account!

One interesting thing to note is that even when the default action is set to deny, the checkbox for "Allow trusted Microsoft services to access this storage account" is enabled.  Based on the documentation there is only about 5 services that are granted access.  In a future post, I hope to see how this checkbox handles diagnostic/audit storage for things like ASC, Azure SQL, etc.

In this post, we covered some basics of using Azure Storage firewalls to secure your storage accounts.

Saturday, December 9, 2017

Azure ARM Template for Service Health

There have been a few changes to how Azure is surfacing health data for the platform itself.  For more information on a new service in preview, please see this link.  Monitoring the platform for health issues is an important part of any overall monitoring strategy.

In the examples, they provide a walk through of how to enable this service using the portal.  This post expands on that by providing an ARM template to do the same thing (in this case an email alert).

You can apply this to any of your subs.


    "$schema": "",
    "contentVersion": "",
    "parameters": {
        "actionGroups_name": {
            "defaultValue": "SubHealth",
            "type": "String"
        "activityLogAlerts_name": {
            "defaultValue": "ServiceHealthActivityLogAlert",
            "type": "String"
    "variables": {
    "resources": [
            "comments": "Action Group",
            "type": "microsoft.insights/actionGroups",
            "name": "[parameters('actionGroups_name')]",
            "apiVersion": "2017-04-01",
            "location": "Global",
            "tags": {},
            "scale": null,
            "properties": {
                "groupShortName": "[parameters('actionGroups_name')]",
                "enabled": true,
                "emailReceivers": [
                        "name": "[parameters('actionGroups_name')]",
                        "emailAddress": "[parameters('emailAddress')]"
                "smsReceivers": [],
                "webhookReceivers": []
            "dependsOn": []
            "comments": "Service Health Activity Log Alert",
            "type": "microsoft.insights/activityLogAlerts",
            "name": "[parameters('activityLogAlerts_name')]",
            "apiVersion": "2017-04-01",
            "location": "Global",
            "tags": {},
            "scale": null,
            "properties": {
                "scopes": [
                "condition": {
                    "allOf": [
                            "field": "category",
                            "equals": "ServiceHealth"
                            "field": "properties.incidentType",
                            "equals": "Incident"
                "actions": {
                    "actionGroups": [
                            "actionGroupId": "[resourceId('microsoft.insights/actionGroups', parameters('actionGroups_name'))]",
                            "webhookProperties": {}
                "enabled": true,
                "description": ""
            "dependsOn": [
                "[resourceId('microsoft.insights/actionGroups', parameters('actionGroups_name'))]"

Tuesday, December 5, 2017

Azure SQL Database Firewall Rules

Today I had a use case where I wanted to grant external access to one of my Azure SQL databases that was hosted in an elastic pool along with other client databases. 

If you are unaware, in Azure SQL, there are two types of firewalls.  The first is at the server level, and applies to all databases hosted on a particular server.  The second is a "database" firewall, where rules are configured within a given database an only applies to that database.  The second method is particularly useful when using Azure SQL and opting for a "database-contained" approach. 

You can read more about the different database firewall options and the order of operations here.

In any event, database contained firewall rules fits my need as I want to grant access to only a single database on a particular server.

To view the firewall rules currently in place:
select * from sys.database_firewall_rules

To add a new rule:
execute sp_set_firewall_rule @name = 'fwrule' @start_ip_address = '<ip>', @end_ip_address  = '<ip>'

To remove a rule:
execute sp_delete_database_firewall_rule @name = 'fwrule'

One key thing to note is that database firewall rules do not appear on the Azure portal, and are probably not reviewed in either Azure Security Center, Threat Detection, or otherwise.  As far as I can tell, these events are also not part of SQL diagnostics.  The only service that might capture them is the audit, but those would need to be reviewed manually.

Remember to actively review your databases for these types of changes and limit who can make them.

Thursday, November 23, 2017

Change UPN Script for Azure AD Operations

In a lot of Azure deployments, synchronizing identity plays a key part of the overall delivery.  This allows one to utilize on-premises accounts/passwords/etc when logging into Azure, and makes overall management easier.

In some client cases, their internal active directory structure might utilize a domain name that is not routeable on the internet.  This poses a problem, particularly with office 365 integration.  Clients want to log in with their .com address, lets say, but AD Connect ends up syncing a .local address instead.

The fix to this can be quite simple, and generally involves setting the user UPN to the correct external address.  Doing this on an existing domain can be a little painful.  There are some scripts out there that do this, but I wanted to write my own and include some powershell features such as -whatif.

Here is the script, enjoy!

    [Parameter(Mandatory=$true,HelpMessage="The old suffix to look for")]
    [Parameter(Mandatory=$true,HelpMessage="The new suffix to set")]
    [Parameter(Mandatory=$true,HelpMessage="The ou to filter for")]
    [Parameter(Mandatory=$true,HelpMessage="The server to target")]

Import-Module ActiveDirectory 
"Oldsuffix: $oldSuffix"
"Newsuffix: $newSuffix"
"ou: $ou"
"server: $server"

$users = Get-AdUser -Filter "*" -SearchBase "$ou"

if (-not $users){
    "Found no users with specified filter"

foreach ($user in $users){
    "===== Processing {0}" -f $user.UserPrincipalName
    if (-not ($user.UserPrincipalName -like ("*$oldSuffix"))){
        "Users UPN suffix does not match oldsuffix, skipping.."

    if ($user.UserPrincipalName -like ("*$newSuffix")){
        "User is already set correctly, skipping..."
    if ($PSCmdlet.ShouldProcess("Updating user")){
        $newUpn = $user.UserPrincipalName.Replace($oldSuffix,$newSuffix)
        $user | Set-ADUser -server $server -UserPrincipalName $newUpn
        "Changed suffix"
    } else {
        "Would have replaced suffix"

Saturday, November 11, 2017

CryptoAnchors, Azure Key Vault, and Managed Service Identity

There has been a lot of fallout due to the recent breaches in the news, and the public punching that was dealt to the executives (or former executives) over at Equifax and Yahoo.  Through all of this, the security lead @ Docker released an interesting post on the concept of Crypto Anchors.  You can read the original article here.

The idea is a simple one.  Currently, most modern web frameworks store passwords along with the salt in hashed format in the database.  An attacker who finds a hole in the application is free to download the database and subsequently offline crack all the passwords to their hearts desire.  Obviously salting helps with this, ensuring that the attacker would have to crack each password individually.  The power of the cloud, however, has turned this into something that can be done on demand.

Crypto anchors, in this case, ensure that decrypting the data can only be done from the environment itself.  That is to say, when you ex-filtrate the database, not all the components you need to decrypt the data are present.

From my own history, I feel like this was the way we created code way back in the dark ages.  I remember configuring a "salt" in my configuration scripts, which would be used to encrypt the password in the database.  Of course, there are problems with this approach (code check-ins, et al) which I think was the main reason why frameworks went down the ways of putting all that data in the database.

I'd say there are limitations to this technique in the real world.  Recent attacks against the memory on a webserver would show that no matter where the "key" is actually stored, provided the compute is actually done on the web server, the key could be ex-filtrated.   When the author refers to an HSM to store the key, what we really need is a trusted compute environment to execute the function itself.  That way the key is never exposed to the web application at all.  Probably a really good use case for Confidential Computing.

In any event, enough pre-amble.  The goal of this post is to mock up a proof of concept to show this in action.  I decided to use a .net core web application along with Azure Key Vault, and the Managed Service Identity which is currently in preview.

Rather then going through the trouble of creating and implementing my own UserManager, I opted to simply add code in the required functions that acted on a password.  The code looks something like the following:

            var azureServiceTokenProvider = new AzureServiceTokenProvider();

            var keyVaultClient = new KeyVaultClient(
                    new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));

            var secret = await keyVaultClient.GetSecretAsync("").ConfigureAwait(false);

            var saltInBytes = System.Text.Encoding.Unicode.GetBytes(secret.Value);
            var hashedPassword = KeyDerivation.Pbkdf2(
                        password: password,
                        salt: saltInBytes,
                        iterationCount: 12341,
                        numBytesRequested: 256 / 8,
                        prf: KeyDerivationPrf.HMACSHA512

            return Convert.ToBase64String(hashedPassword);

I simply added the above as a function in my AccountController.cs.  The code is called to "prehash" passwords that are passed to me, and this is done before I pass it to the UserManager.  Keep in mind that the UserManager is still hashing the password into the database, and storing it along side the salt.  When the database is ex-filtrated, it will be missing the key vault salt.

I think the key part of this architecture is using Managed Service Identity on your keyvault.  In the past, you would have to provide a client id/secret that was stored in configuration.  This, of course, could be ex-filtrated from the web app itself if it was compromised.  Since KeyVault is a publically accessible service, anyone with the id/secret could access the vault.

Managed Service Identity solves this by having the platform grant an identity to the app container itself, something that cannot be transferred by simply knowing the client id and secret.  Pretty cool.  Setting this up was super simple.  Follow the instructions here.

The last part was setting up Azure Key Vault, which literally only takes a smile.  Ensure that you grant access to the managed service identity you created for your app.

In conclusion, we talked a little bit about crypto anchors, and how it can be an effective pattern in protecting data.  I then showed a quick setup in .net core, using Azure Managed Service Identity and Azure Key vault.  Happy encrypting!

Saturday, October 14, 2017

Tapping into Azure resource group events with Event Grid

At this point in the Azure lifecycle, there are a couple of different ways you could tap into events occurring in your resource groups in Azure.  One of the newest services (still in preview) is Azure Event Grid.  The goal of this post is to use an event subscription to get access to Azure events on a target resource group in real-time.

After creating a resource group in Azure (to target), you can head on over to "Event Subscriptions" in the menu bar.

Click on the "+ Event Subscription" button, and you will follow the form to create an event subscription for a target Azure Resource Group.


A couple of notes:
  • The name has to be subscription unique, so you might want to be fairly descriptive here
  • There are 7 default event types (all the events you can do with a resource) and they are generic in nature.  Remember that an event will be created for all of them, so pick carefully not to overload any downstream systems
  • Prefix could be used to narrow this down to only the specific event types you want to target.
After you have all of this setup, you can create some events in your target resource group and watch them show up in your destination.  For my purposes, I executed an Azure Backup of a VM that I had already created.  The two events I was looking to see were the restore point creation event and the restore point deletion event.

Based on the RequestBin output, the event grid service calls out to the target url (the subscriber endpoint above) with a POST call.  The body of the POST is essentially the same as what you would see in the activity log for that event.

Of course, the endpoint could have just as easily been an Azure function or an Automation runbook.

In conclusion, the goal of this post was to tap into Azure Resource Group events via Azure Event Grid, a new service currently in preview.  We walked through a basic setup and passed our event directly to RequestBin.  Happy event tapping!

Wednesday, October 11, 2017

Course Review: Managing IaC With Terraform

As you probably have seen from previous posts, Terraform as a technology has peaked my interest.  Luckily for me, safaribooks put on a 3-hour course on this very topic.  The course was presented by Yevgeniy Brikman and it was very well done!

What I liked?

The presenter was absolutely awesome.  Very knowledgeable and what I liked was the presentation went quickly from the basics to some of the more advanced terraform concepts.  He talked about some issues that I ran up against when I was using terraform.  For example, he touched on how he does credential management from both local servers and build servers.  He also talked about how he organizes his tf files and how he integrates modules into his workflow.  Finally, I really liked the project he showed at the end for using go to "test" your terraform scripts in an automated fashion.

What I didn't like?

The O'Reilly platform requires flash to get the audio.  Wow, #howisthisstillathing ?

I am really liking terraform and it's ability to deploy resources cross-platform.  I am hoping to experiment more with it to see how I can bring this technology to my clients.  If you are interested in the course, I think there is another one coming up in a few weeks.  Check out for more info.

Sunday, October 8, 2017

Performing an Azure SQL Security Audit with AzSDK

I have really been enjoying being an MVP.  One of the great perks about this is access to mailing lists where all the MVPs can discuss relevant topics to the different Microsoft areas in scope.  One email thread got me to look at the Azure AzSDK project on github.  This project seems to have a bunch of Microsoft contributors, and is focused on building scripts to both report and remediate on security baselines in Azure.  The goal of this post is to show how to apply this project against your Azure SQL resources.

The first thing to cover here is that the AzSDK is focused on the Azure side of any given resource.  That is to say, it will investigate (via the APIs) how your resource is configured in Azure against recommended best practices.  In the case of SQL, for example, it will not "log in" to the server to do any checks at the sql level.

Step 1 in the process is obviously to install the module.  You can find detailed instructions in their posted Installation Guide.  What I like about the guide is it's focus on not having to use elevated permissions in powershell to get the project up and going. One key note here is to ensure you have the correct version of the AzureRM modules installed.

After installation, you can simply run the built-in set of command-lets against your Azure resources.  As always, I recommend reading the code before running it to get an idea of what it is doing.  There is a lot of bootstrapping code in the modules, which eventually targets JSON files that have all the rules to apply defined.

Here is an example of one of the SQL rules:

      "ControlID": "Azure_SQLDatabase_Audit_Enable_Logging_and_Monitoring_DB",
      "Description": "Enable SQL Database audit with selected event types and retention period of minimum $($this.ControlSettings.SqlServer.AuditRetentionPeriod_Min) days",
      "Id": "SQLDatabase140",
      "ControlSeverity": "Medium",
      "Automated": "Yes",
      "MethodName": "CheckSqlDatabaseAuditing",
      "Rationale": "Auditing enables log collection of important system events pertinent to security. Regular monitoring of audit logs can help to detect suspicious and malicious activities early enough.",
      "Recommendation": "Run command  Set-AzureRmSqlDatabaseAuditingPolicy -ResourceGroupName '{ResourceGroupName}' -ServerName '{ServerName}' -DatabaseName '{DatabaseName}' -StorageAccountName '{StorageAccountName}' -EventType 'All' -AuditType 'Blob' -RetentionInDays $($this.ControlSettings.SqlServer.AuditRetentionPeriod_Min). Refer:",
      "Tags": [
      "Enabled": true,
      "FixControl": {
         "FixMethodName": "EnableDatabaseAuditingPolicy",
         "FixControlImpact": "Low",
         "Parameters": {
            "StorageAccountName": ""

I like that it is extensible, and allows you to potentially add your own rules if required.

Running the scan is pretty easy.  You can follow the instructions here and simply target the resource group you would like to evaluate.  Here is the output from the command on one of my resources in production.

Oh my, that is a lot of failures.  You can find detailed reports in a CSV that is stored on disk (see the last line of the screenshot above).  Upon review, it seems like all of my failures were due to auditing not being turned on at the database level.  Of course, auditing is turned on at the server level, and, as per the docs, this covers all databases.  Seems like this test could use some improvement.

In any event, I really like where this project is going and will be following it closely. I can see adding this type of automated check in a build/release pipeline to ensure at least the baselines are covered.  There is a LOT more that this project can do, so be sure to check it out if you are interested.