Audit Logs for Azure Events

Note: This post is cross-post with http://blogs.msdn.com/b/cloud_solution_architect.

A common ask when working with Microsoft Azure is, “how can I view audit logs to determine who made changes to the subscription(s) and the related Azure resources?” With the new Audit Logs feature now available in the Azure Preview Portal (https://portal.azure.com), keeping track of changes becomes easier than ever.

Before getting started, I would like to point out that Dushyant Gill has an excellent post in which he provides one way to answer three very important questions:

  1. Who all has access to my subscriptions?
  2. What access does a specific user have on my subscription?
  3. Who gained (or lost) access to my subscriptions in the past n days?

In Dushyant’s post, he describes a PowerShell module he wrote that makes it easy to generate reports which answer the above questions.

In this post, I will describe one approach for working with the new Azure Audit Logs to determine what changed, and also how to view logs related to the addition or removal of co-administrators. Along with the PowerShell module described in Dushyant’s post, this should allow much more insights into the actions taken against an Azure subscription.

What Changed?

By opening the new Audit Logs blade in the Azure Preview Portal, you can view many of the events that occurred against the subscription.

Audit Logs - Single

The Audit Logs blade will show five key pieces of information for the list of operations:

  1. Operation name
  2. Level: Critical, Error, Warning, or Informational
  3. Status: Succeeded or Failed
  4. Resource
  5. Time

Note 1: Use the Filter action at the top of the Audit Logs blade to modify the operations displayed.

Note 2: Audit logs are available for only the Azure resources available in the Azure Preview Portal and/or the Azure Resource Manager API.

 

Clicking on a specific operation will open additional blades to view more detailed information.

Full Audit Logs

The Detail blade will allow you to view key pieces of information such as who made the change, when the change was made, what was the Azure resource and resource group affected, etc.

Audit Logs - Detail Blade

If the change is related to a role assignment (i.e. adding a user to one of the new Role Based Access Control (RBAC) related roles – Virtual Machine Contributor, Website Contributor, etc.), the Detail blade will show additional information to show the user that was added to the role. At the time of this writing, however, the user and role is displayed as a GUID and not the corresponding friendly name (e.g. email address or role name). It is anticipated this will be cleaned up in a feature update to the Azure Preview Portal. In the meantime, you can leverage a few Azure PowerShell cmdlets to piece together the relevant information.

In the Details blade, have a look at the PROPERTIES value. There are two key pieces of information here: the PrincipalId and the RoleDefinitionId.

Audit Logs - Add User to RBAC Role

For the cmdlets below, you will need to switch to the Azure Resource Manager mode in Azure Powershell by executing the command below.  Azure PowerShell version 0.8.15 is used in this post, although the cmdlets below first appeared in version 0.8.14.

Switch-AzureMode AzureResourceManager

 

To determine the role to which the user was assigned, use the Get-AzureRoleDefintion cmdlet. This cmdlet returns all the role definitions, so you will need to search for the specific definition (the GUID after “roleDefinition”) as indicated the PROPERTIES in the Detail blade.

Get-AzureRoleDefinition | where { ($_.Id -like "*de139f84-1756-47ae-9be6-808fbbe84772" )}

Get-AzureRoleDefinition

To get the user added to the role, use the Get-AzureAdUser cmdlet. For the -ObjectId parameter, use the PrincipalId value from the PROPERTIES in the Detail blade.

Get-AzureADUser -ObjectId b458cfbc-a101-45c1-9575-1ec43aefcc45

Get-AzureAdUser

What about Co-Administrators and Management Certificates?

When it comes to permissions in Azure, there are three approaches that can be used: the co-administrator model, management certificates, and Role Based Access Control (RBAC). The co-administrator model has been the security model in working with Azure for quite some time. People that wanted to work with Azure (assuming they were not the subscription owner) could be granted access to the Azure subscription as a co-administrator. As a co-administrator, they could take any action they desired against any Azure service.  If non-interactive administrative access was needed, a management certificate could be added to the target subscription(s).

Going back to the earlier topic of what change was made and by whom – how can we tell when a co-administrator or management certificate was added to the subscription?

For management certificates, you can use the List Subscription Operations service management API call. There is an Operation Name parameter in the response that should include AddSubscriptionCertificate and RemoveSubscriptionCertificate for the respective action.

AddSubscriptionCertificate

An astute observer of the MSDN documentation for List Subscription Operations will notice there is no corresponding operation for co-administrators. To obtain information about co-administrators being added or removed from a subscription, we can leverage the new audit logs available in the Azure Preview Portal and Azure Resource Manager API. Co-administrators in the Azure Management Portal actually correspond to the Owner role available in the Azure Preview Portal (and thus Azure Resource Manager). Adding or removing a co-administrator via the Azure Management Portal creates an event with Azure Resource Manager that is then logged. However, as of the time of this writing, the addition or removal of co-administrators is not yet reported in the Azure Preview Portal’s Audit Logs.

To get the audit logs related to the addition or remove of co-administrators, you can use the Get-AzureResourceProviderLog PowerShell cmdlet. This cmdlet retrieves the operations associated for a specific Resource Provider – in this case we would want operations against the Microsoft.Authoriztion provider. For example, to view the logs for a certain date range, you could execute a command similar to the following:

Get-AzureResourceProviderLog -ResourceProvider "Microsoft.Authorization" -StartTime "2015-03-10" -EndTime "2015-03-11" -DetailedOutput

A screenshot of the output is below. In the screenshot you’ll notice you can see the adminEmail and adminType for the user added via the Azure Management Portal.

Get-AzureResourceProviderLog - Add CoAdmins

Alternatively, you could get all the authorization events starting from a specific date up until the current time:

Get-AzureResourceProviderLog -ResourceProvider "Microsoft.Authorization" -StartTime "2015-03-11" -DetailedOutput

Get-AzureResourceProviderLog - Remove CoAdmins

It should be noted that there currently does seem to be at least two known bugs related to the shown information:

  1. If the removed co-admin is an Azure AD user (as opposed to a Microsoft Account), the adminEmail is empty
  2. The Caller value is not populating

 

But Wait! There’s More!!

The Get-AzureResourceProviderLog cmdlet shown above is just one of the new audit log cmdlets now available. It is easy to view the additional cmdlets by executing the following command:

get-help get-*Azure*log*
Name Description
Get-AzureSubscriptionIdLog Gets the operations associated with the current subscriptionId
Get-AzureResourceProviderLog Gets the operations associated with a Resource Provider
Get-AzureResourceLog Gets the operations associated with a ResourceId
Get-AzureResourceGroupLog Gets the deployment log for a resource group
Get-AzureCorrelationIdLog Gets the operations associated with a CorrelationId.

 

Summary

The new Audit Logs feature in the Azure Preview Portal, combined with new Azure PowerShell cmdlets, make it easier than ever to have insights into the changes made to an Azure subscription. Together, with the module described in Dushyant Gill’s post, many of the administrative actions taken against an Azure subscription and related resources are now easily viewable.

Please be sure to be using at least version 0.8.14 (that’s when the cmdlets mentioned in this post first appeared) of the Azure PowerShell cmdlets, and switch to use the AzureResourceManager mode for the cmdlets as well.

New Book – Microsoft Azure Essentials

In early February, Microsoft Press released a free new eBook entitled Fundamentals of Azure: Microsoft Azure Essentials. This is a book I, along with Azure MVP Robin Shahan spent several months on in late 2014. To see if finally be published is truly exciting!Book Cover

The goal of the book is to serve as primer for those new to cloud computing in general, and the Microsoft Azure platform specifically. We do not go too deep on the topics, and we don’t cover all the services available in Azure. We cover the core concepts people need to get started and be successful.

Writing a book is hard work – much harder than I originally anticipated. I had friends tell me it would be a lot of work – they were right! Writing a book on Azure adds to the challenge, I think. The reason is Azure is a constantly evolving platform. It’s hard to write a book on something that changes several times a month. We tried to keep up with the changes as best we could. But, inevitably some things will be inaccurate by the time to book is published or shortly thereafter.

Writing a book is also fun! While many nights and weekends were sacrificed to write this book, it was a very rewarding experience. I know I learned a lot while writing this book. I had many late night Skype calls with Robin to talk through how we wanted topics to flow. I had many great email conversations with the Azure MVPs that helped to provide early feedback on the book. A lot of work, but fun – and that’s the best kind of work!

You can find the book at http://aka.ms/fundamentalsofazure and get it in PDF, EPUB, or Mobi formats.

Topics covered in the book include.

  • Getting started with Azure: Understand what cloud computing is, visit the management
    portals, and learn about billing.
  • Websites and Cloud Services: Learn about Azure Websites, from deployment to monitoring, and gain an understanding of the web and worker roles used in Azure Cloud Services.
  • Virtual Machines: Explore the basic features of Azure Virtual Machines, including how to create, configure, and manage them.
  • Storage: Read about the basics of Azure Storage, including blobs, tables, queues, and file shares.
  • Virtual Networks: Learn the basics of virtual networks, including how to create one, and why a virtual network might be necessary. This also covers site-to-site and point-to-site networking, as well as ExpressRoute.
  • Databases: Explore two relational database options available in Azure: Azure SQL Database and SQL Server in Azure Virtual Machines.
  • Azure Active Directory: Explore basic features of Azure AD, including creating a directory, users and groups, and using the application gallery.
  • Management Tools: Explore three common tools for working with Azure: Visual Studio 2013 and the Azure SDK, Azure PowerShell cmdlets, and the Cross-Platform Command-Line Interface
  • Business Scenarios: Explore four common scenarios for utilizing Azure features: development and test, hybrid, application and infrastructure modernization, and Azure Mobile Services.

2014 Blog in Review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 27,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 10 sold-out performances for that many people to see it.

Click here to see the complete report.

Azure Worker Role Changes in SDK 2.4

It’s been quite a while since we’ve seen any significant (ok, really any) changes in the boilerplate template code Visual Studio generates for Azure Cloud Services (web or worker roles). For as long as I can remember, the code has looked like this:

public override void Run()
{
    // This is a sample worker implementation. Replace with your logic.
    Trace.TraceInformation("WorkerRole.23 entry point called");

    while (true)
    {
       Thread.Sleep(10000);
       Trace.TraceInformation("Working");
    }
}

Pretty basic, right?  Just do some work forever.  If there is a failure (i.e. an unhandled exception), it’ll bubble up and out of the Run method, effectively crashing the role and Azure will restart it. The problem with this is that it didn’t effectively handle the case when the role stops – when OnStop is called. If your code was doing something at the that time, too bad, so sad.  You’re done. Microsoft released some guidance way back in January 2013 on The Right Way to Handle Azure OnStop Events. Unless you knew of this blog post, or were really good at your Bing-fu, it was too easy to just use the Visual Studio generated code. And then something happens, your code doesn’t do what you think it should (even though it does exactly what it is told to do), and bad words are uttered silently, and feelings get hurt.

Staring with Azure SDK 2.4, there is new boilerplate code generated by Visual Studio.  Check this out:

public class WorkerRole : RoleEntryPoint
{
    private readonly CancellationTokenSource cancellationTokenSource = new CancellationTokenSource();
    private readonly ManualResetEvent runCompleteEvent = new ManualResetEvent(false);

    public override void Run()
    {
        Trace.TraceInformation("WorkerRole.24 is running");

        try
        {
            this.RunAsync(this.cancellationTokenSource.Token).Wait();
        }
        finally
        {
            this.runCompleteEvent.Set();
        }
    }

    public override void OnStop()
    {
        Trace.TraceInformation("WorkerRole.24 is stopping");

        this.cancellationTokenSource.Cancel();
        this.runCompleteEvent.WaitOne();

        base.OnStop();

        Trace.TraceInformation("WorkerRole.24 has stopped");
    }

    private async Task RunAsync(CancellationToken cancellationToken)
    {
       // TODO: Replace the following with your own logic.
       while (!cancellationToken.IsCancellationRequested)
       {
           Trace.TraceInformation("Working");
           await Task.Delay(7000);
       }
    }
}

At first blush, this looks more complicated. But it really isn’t that bad. In a way, it’s doing what that Microsoft blog post from 2013 was indicating, just using cancellation tokens.

The OnStop method is actually overridden in the template now. There, the cancellation token is triggered to indicate the running thread should be cancelled, and then wait for that to happen. In the RunAsync method, we’re just waiting for that cancellation request, otherwise continue doing some business. Easy.

Keep in mind that there is still the 5 minute limit to finish, or else Azure will terminate the process.

DevLink 2014 Presentations

Last week I had the privilege to speak at DevLink in Chattanooga, TN.  I had a great time!  It was fun to present.  I was also was able to attend some great sessions.  DevLink is always a top notch event to attend.

I had a full load of presentations – four in three days!  Overall I think the sessions came together very well.  If you attended any of my sessions, I would greatly appreciate any feedback. You can find a copy of all my presentations below and on SlideShare.net

  1. Programming Azure Active Directory
  2. Inside Azure Diagnostics
  3. Automating Your Azure Environment
  4. More Cache for Less Cash

Azure Website & Managed Cache Service

If you’re working with an Azure Website and trying to use the Azure Managed Cache Service, you really need to be on either the Basic or Standard tier for the Azure Website.  Apparently the Azure Managed Cache Service is not supported when on the Azure Websites Free or Shared tiers.

If using the Free or Shared tier of Azure Websites, and trying to access the Azure Managed Cache Service, you’ll get an error similar to this one:

Failure in About. Microsoft.ApplicationServer.Caching.DataCacheException: ErrorCode<ERRCA0017>:SubStatus<ES0016>:There is a temporary failure. Please retry later. (CRL Server for SSL Certificate is offline) —> Microsoft.ApplicationServer.Caching.ChannelAuthenticationException: CRL Server is offline —> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.

No amount of retry logic will fix this error.  Gnarly, huh?

The fix is easy enough though. Scale your Azure Website to either the Basic or Standard tier.  You can make the Azure Website change easily via the Azure management portal.

Creating a Custom SQL Server VM Image in Azure

Recently I had the opportunity to work on a project were I needed to create a custom SQL Server image for use with Azure VMs.  The process was a little more challenging than I initially anticipated.  I think this is mostly because I was not familiar with the process of preparing a SQL Server image.  Perhaps this isn’t much of a challenge for an experienced SQL Server DBA or IT Pro.  For me, it was a great learning experience.

Why a Custom SQL Server Image?

The Azure VM image gallery already contains a SQL Server image.  It’s very easy to create a new SQL Server VM using this image.  However, doing so has a few important trade-offs to consider:

  • Unable to fully customize the base install of SQL Server.  This is a template/image after all – you get a VM configured the way the image was configured.
  • Unable to use your own SQL Server license.  If your company has an Enterprise Agreement (EA) with Microsoft, it’s likely there is already some SQL Server licenses built into that agreement.  Depending on the details, it may be significantly cheaper to use the licenses from the EA instead of paying the SQL Server VM image upcharge from Azure.

The Basic Steps

There are 6 basic steps to creating a custom SQL Server VM image for use in Azure.

  1. Provision a new base Windows Server VM
  2. Download the SQL Server installation media
  3. Run SQL Server setup to prepare an image
  4. Configure Windows to complete the installation of SQL Server
  5. Capture the image and add it to the Azure VM image gallery
  6. Create a new VM instance using the custom SQL Server image

The basic idea here is to create a base VM, customize it with a SQL Server image, capture the VM to create an image, and then provision new VMs using that captured VM image.

Create_SQL_VM_Image_Azure 2

Let’s dive into each of these in a little more detail.

Note: the terminology here can be a little confusing. When referring to the VM used to create the template/image, I’ll use the term “base VM”. When referring to the VM created from the base VM, I’ll use the term “VM instance”.

1. Provision a new base Windows Server VM

There are multiple ways to create a Windows Server VM in Azure.  Creating a VM via the Azure management portal and PowerShell are probably the two most popular options.  Be sure to check out this tutorial to learn how to do so via the portal. For the purposes of this post, I’ll do so via PowerShell.

$img = Get-AzureVMImage `
	| where { ( $_.PublisherName -ilike "Microsoft*" -and $_.ImageFamily -ilike "Windows Server 2012 Datacenter" ) } `
	| Sort-Object -Unique -Descending -Property ImageFamily `
	| sort -Descending -Property PublishDate `
	| select -First(1)

$vmConfig = New-AzureVMConfig -Name "sql-1" -InstanceSize Small -ImageName $img.ImageName |
    Add-AzureProvisioningConfig -Windows -AdminUsername "[admin-username-here]" -Password "[admin-password-here]" 

New-AzureVM -ServiceName "SQLServerVMTemplate" -VMs $vmConfig -Location "East US" -WaitForBoot

 2. Download the SQL Server installation media

With the base Windows Server 2012 VM created, we can now get ready to prepare (sysprep) the SQL Server installation.  To do that, we need to get the SQL Server installation media onto the machine.  The easiest way I found to do this was to leverage Azure blob storage.

  1. Upload the SQL Server ISO file to Azure blob storage
  2. Remote Desktop (RDP) into the base VM
  3. From the VM, download the SQL Server ISO file to the local disk
  4. Mount the SQL Server ISO file to the VM
  5. Copy the ISO contents (not the ISO file itself) to the VM’s C:\ drive.  For example, use C:\sql

The SQL Server installation media files need to be copied to the local C: drive so it can be used later to complete the SQL Server installation (when provisioning the actual SQL Server VM instance).

3. Run SQL Server setup to prepare an image

In order to prepare the (sysprep’d) SQL Server VM image (which we can use as a template for future VMs), we need to run the SQL Server installation and instruct it to prepare an image – not run the full installation.  An easy way to do this is with a SQL Server configuration file, an example of which I’ve included below.

ConfigurationFile.ini

;SQL Server 2012 Configuration File
[OPTIONS]
; Specifies a Setup workflow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="PrepareImage"
; Detailed help for command line argument ENU has not been defined yet.
ENU="True"
; Parameter that controls the user interface behavior. Valid values are Normal for the full UI, AutoAdvance for a simplified UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
;UIMODE="Normal"
; Specifies setup not display any user interface.
;QUIET="False"
; Specifies setup to display progress only, without any user interaction.
QUIETSIMPLE="True"
; Specifies whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
UpdateEnabled="True"
; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.
FEATURES=SQLENGINE
; Specifies the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
UpdateSource="MU"
; Displays the command line parameters usage
HELP="False"
; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS="False"
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
X86="False"
; Specifies the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
; Specifies the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
; Specifies the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.
INSTANCEID="MSSQLSERVER"
; Specifies the installation directory.
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

There are two steps in this process:

  1. Copy the ConfigurationFile.ini file (from your local PC) to the same location as the SQL Server installation media (i.e. c:\sql) on the base VM.
  2. Run SQL Server setup to prepare an image.  From a command prompt (on the base VM), navigate to the C:\sql folder and then execute the following command:
Setup.exe /ConfigurationFile=ConfigurationFile.ini /IAcceptSQLServerLicenseTerms=true

 4. Configure Windows to complete the installation of SQL Server

At this point the base VM should have an “installation” of SQL Server that is not fully completed. The SQL Server bits are in place, but they’re not configured for a full server install . . . at least not yet. The final configuration of SQL Server will take place when the VM instance (of which this template/image is the base) is provisioned and boots up for the first time. This is accomplished by using a CMD file with the following content:

@ECHO OFF && SETLOCAL && SETLOCAL ENABLEDELAYEDEXPANSION && SETLOCAL ENABLEEXTENSIONS
REM All commands will be executed during first Virtual Machine boot
"C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\setup.exe" /QS /ACTION=CompleteImage /INSTANCEID=MSSQLSERVER /INSTANCENAME=MSSQLSERVER /IACCEPTSQLSERVERLICENSETERMS=1 /SQLSYSADMINACCOUNTS=%COMPUTERNAME%\Administrators /BROWSERSVCSTARTUPTYPE=AUTOMATIC /INDICATEPROGRESS /TCPENABLED=1 /PID="[YOUR-SQL-SERVER-PRODUCT-ID-HERE]"
  1. On your local PC, save the file as SetupComplete2.cmd
  2. RDP / log into the base VM
  3. Copy the SetupComplete2.cmd from your local PC file to the c:\Windows\OEM folder on the base VM
  4. Change the value for the SQLSYSADMINACCOUNTS value to be that of the administrative account created on the VM (or better yet – the local Administrators group account)
  5. If needed, supply the SQL Server product ID (PID) value.

When Windows starts on the new VM instance for the first time, the SetupComplete2.cmd file should automatically run.  It is invoked by the SetupComplete.cmd file already on the machine.

vm-image-setupcomplete

5. Capture the image and add it to the Azure VM image gallery

At this point a base SQL Server VM has been created and the groundwork laid to complete the install. Now it is time to create the VM image from the base VM, and do to that you sysprep and capture the base VM.  Please follow the guide on How to Capture a Windows Virtual Machine to Use as a Template.

6. Create a new VM using the custom SQL Server image

With a new custom VM image template available in the VM image gallery, you can provision a new VM instance using that custom template.  Upon first boot, the newly provisioned VM should complete the full SQL Server installation as laid out in your SetupComplete2.cmd file.  Please follow the guide on How to Create a Custom Virtual Machine for more information on creating the VM from the template.

 

Closing Thoughts

One of the quirks I noticed when preparing the base SQL Server image is that it was not possible to prepare the image with SQL Server Management Studio (SSMS).  I would have to do the install after the newly provisioned VM instance is created. Not hard, but time consuming (an annoying if doing this on multiple VM instances).  I later learned that SQL Server 2012 Cumulative Update 1 does allow for preparing a SQL Server image with SSMS installed.  I’ve included a link below that describes the process for creating a SQL Server image with CU1.

In the end, this process really is not all that hard.  Time consuming?  Yes!  The worst part (at least for me) was really just understanding how the SQL Server installation and sysprep process works.  Once I wrapped my head around that, the process was a lot smoother.

 

Helpful Resources

While I was learning how to create a custom SQL Server VM image, the following resources were very helpful:

 

I would like to thank Scott Klein for his assistance in verifying these steps.  His help was extremely valuable to ensure I was doing this the right way.