Introduction to Azure Privileged Identity Management

As a general security best practice, it’s best to operate and manage IT infrastructure under the least privilege principle. Doing this on premise has often been problematic as it involved either a manual escalation process (Run As) or a custom automated process to achieve. The Run As approach is typically not ideal as even those secondary accounts have generally way more privileges than required to perform administrative tasks on systems. PowerShell Just Enough Administration definitely helps in that regard but today I will cover Azure’s take on this problem by covering the basics of Azure Privileged Identity Management (PIM).

With Azure PIM, you will have better visibility on the privileges required to manage your environment. It’s fairly easy to get started and to use so I highly encourage you to adopt this security practice in your environment, especially if you are just getting started with Azure in general.

Initially, Azure Privileged Identity Management (PIM) only covered privilege escalation for Azure Active Directory roles. This changed when Microsoft announced they are now covering Azure Resource Manager resources as well. This means you can now do just in time escalation of privileges to manage things like subscriptions, networking, VMs etc. In this post, I’ll cover the Azure AD roles portion of Azure PIM.

To quickly get started with Azure PIM with Azure AD roles, you can simply login to the Azure Portal and start assigning users as eligible to specific Azure AD roles. To achieve this, you go to the Azure AD Directory Roles section.

Once in the section, you can now go in the Roles section to start making users eligible to specific Azure AD roles by clicking the Add user button. A thing to note, is that you can only assign roles to specific users, not to a group.

Once you have specified a user as eligible to a role, that user can now activate it. To do this, they simply have to go in the Azure PIM section of the Azure Portal and pick My Roles. The user can then select the appropriate role to activate in order to perform the desired administrative task.

When you activate a role, you will be prompted to enter a reason as to why you need to elevate your privileges. This is generally good practice as it will allow the persons reviewing the escalations to understand why certain high privileges had to be used to perform a task.

Now that we have covered the basics to quickly get you started with PIM. We can dive a bit into how that experience can be customized. Here are the configuration options for an Azure AD role:

  • Maximum Activation duration: When the user activates a role, how long should it remain activated? A shorter duration is desirable for security reasons.
  • Notifications: Should an email be sent to an administrator when a role is activated? This can also give the admin a feeling as to whether an admin role is abused. i.e. Why use Global Admin when its not necessary to perform task X?
  • Incident/Request Ticket: You could enforce a support ticket number to be entered with each activation. This can be useful if you really need to close the loop as to why elevation is required. i.e. Need to change a setting to apply a change request or resolve an incident #####.
  • Multi-Factor Authentication: A user will need to be enrolled in Azure MFA in order to activate a role.
  • Require approval: When this is enabled, an admin will need to approve the activation for a user. This might be useful for high privilege roles such as Global Admin where you don’t want to have abuse of privileges. It also documents the full process better. i.e. User X asked for elevation and admin Y approved the request.

From an operational standpoint, you can also get alerts for the following things:

Out of those alerts, you can tune the thresholds in order to match your organization requirements:

  • For There are too many global administrators alerts, you can define the number of allowed global admins and the percentage of global admins versus the total number of administrators configured.
  • For Roles are being activated too frequently, you can specify the maximum duration between activation and the number of acceptable activation during that period. This could be useful to flag users that simply activate all roles for no good reasons just to make sure they have the required privileges to perform a task.

You can also configure the Access review functionality which specifies how you want to review the user activation history in order to maintain a tight ship security wise. You can configure the access review with the following settings:

  • Mail notifications: Send an email to advise an administrator to perform the access review
  • Reminders: Send an email to advise an administrator to complete an access review
  • Require reason for approval: Make sure the reviewer documents why an activation was approved/makes sense.
  • Access review duration: The number of days between each access review exercise (default is 30 days).

Once all this is configured, you can monitor the activation/usage of roles using the Directory Roles Audit History section:

I hope this quick introduction to Azure Privileged Identity Management was helpful. Should you have any questions about this, let me know!


Doing an Email Loop For 3rd Party DLP Inspection With O365 Exchange Online

While Exchange Online provides Data Leakage Prevention (DLP) capabilities, it’s still possible to integrate it with a third party DLP solution. The goal was to achieve this while still providing a solution that’s highly available and not dependent on on premises resources. Here’s the configuration we’ve picked to experiment with this.

The first thing needed to be setup was a pair of VM appliances hosted in Azure. Those appliances are receiving emails from Exchange Online, inspect them and send them back to Exchange Online. We could have opted with a configuration where the appliances would send the emails directly without involving Exchange again but we wanted to maintain the IP/service reputation and message tracking capabilities provided by Exchange. I will not got into the details of creating those VMs as this is vendor dependent. In our particular case, we uploaded a VM VHD to Azure Storage and then created an Azure Image using that. It was then fairly straightforward to deploy the VMs afterward using an Azure Resource Manager template. The VMs are part of an Azure Availability Set and an Azure Network Security Group for traffic filtering.

Once the VM appliances have been deployed in Azure IaaS, an Azure Load Balancer was configured in order to provide high availability. This is achieved by first configuring a load balancing rule for SMTP (port 25).

Load Balancing Rule Configuration

Once that was completed, an health probe that monitors the availability of the backend VMs delivering the DLP service again for port 25 was created.

Health Probe Configuration

With the Azure portion of the setup completed, we now move on to the Exchange Online configuration. First we configured two connectors. One to send emails from Exchange Online to the DLP solution and another to ensure that Exchange Online would accept emails from the DLP solution back and then send those to the Internet.

From Connector Configuration
To Connector Configuration

Once the connectors have been created, it was required to create a mail flow/transport rule that would send all emails to the DLP solution while also avoiding to create a mail loop when those emails would come back from it. To achieve this, the rule was configured to send all emails to the connector that’s responsible to send the emails to the DLP solution as an action and an exception on the sender IP address was configured. In this particular case, we want to make sure that all emails coming from the public IP of the load balancer in front of the DLP solution are excluded from that rule to avoid the mail loop.

Mail Flow Rule Configuration

With that configuration in place, we were able to successfully send the emails through the DLP servers and then back to Exchange Online to be sent on the Internet. We can confirm this by looking at the message trace in Exchange Online:

If you have any questions about this, let me know!

Measuring GPU Utilization in Remote Desktop Services

I recently spent some time experimenting with GPU Discrete Device Assignment in Azure using the NV* series of VM.  As we noticed that Internet Explorer was consuming quite a bit CPU resources on our Remote Desktop Services session hosts, I wondered how much of an impact on the CPU using a GPU would do by accelerating graphics through the specialized hardware.  We did experiments with Windows Server 2012 R2 and Windows Server 2016. While Windows Server 2012 R2 does deliver some level of hardware acceleration for graphics, Windows Server 2016 did provide a more complete experience through better support for GPUs in an RDP session.

In order to enable hardware acceleration for RDP, you must do the following in your Azure NV* series VM:

  1. Download and install the latest driver recommended by Microsoft/NVidia from here
  2. Enable the Group Policy Setting  Administrative Templates\Windows Components\Remote Desktop Services\Remote Desktop Session Host\Remote Session Environment\Use the hardware default graphics adapter for all Remote Desktop Services sessions as shown below:

To validate the acceleration, I used a couple of tools to generate and measure the GPU load. For load generation I used the following:

  • Island demo from Nvidia which is available for download here.
    • This scenario worked fine in both Windows Server 2012 R2 and Windows Server 2016
    • Here’s what it looks like when you run this demo (don’t mind the GPU information displayed, that was from my workstation, not from the Azure NV* VM):
  • Microsoft Fish Tank page which leverages WebGL in the browser which is in turn accelerated by the GPU when possible
    • This proved to be the scenario that differentiated Windows Server 2016 from Windows Server 2012 R2. Only under Windows Server 2016 could high frame rate and low CPU utilization was achieved. When this demo runs using only the software renderer, I observed CPU utilization close to 100% on a fairly beefy NV6 VM that has 6 cores and that just by running a single instance of that test.
    • Here’s what FishGL looks like:

To measure the GPU utilization, I ended up using the following tools:

In order to do a capture with Windows Performance Recorder, make sure that GPU activity is selected under the profiles to be recorded:

Here’s a recorded trace of the GPU utilization from the Azure VM while running FishGL in Internet Explorer that’s being visualized in Windows Performance Analyzer:

As you can see in the WPA screenshot above, quite a few processes can take advantage of the GPU acceleration.

Here’s what it looks like in Process Explorer when you’re doing live monitoring. As you can see below, you can see which process is consuming GPU resources. In this particular screenshot, you can see what Internet Explorer consumes while running FishGL my workstation.

Windows Server 2016 takes great advantage of an assigned GPU to offload compute intensive rendering tasks. Hopefully this article helped you get things started!

Near Real-time Power BI Dashboard using SQL Server Change Data Capture

As I wanted to have a near real-time dashboard of some of our core databases, I started to look at various solutions to achieve this. It also didn’t hurt that I worked with another proof of concept using CDC in C# and Stream Insight in the past. It gave me some inspiration on that new process and I also took the opportunity to rewrite/improve some of the parts when writing the new PowerShell code.

Here are some of the pieces I ended up selecting:

Dashboard : Microsoft Power BI
Data Extraction: PowerShell
Message Queue: Azure Event Hub
Real-time aggregation engine: Azure Stream Analytics
Darabase Change Tracking: SQL Server Change Data Capture

Overall Process

  1. Configure which table will be tracked by the DataCollector PowerShell process
  2. Configure which columns on those table will be collected
  3. Enumerate the databases that will be collected
  4. Enumerate the tables collected
  5. Enumerate the columns collected
  6. Capture the CDC changes starting after the last LSN that was captured and output that change as a custom PSObject
  7. The PSObject is then converted to a hashtable to facilitate the transmission to Azure Event Hub
  8. The hashtable is then converted to JSON and included as the body of an Azure Event Hub Message.
  9. The message is then queued in Azure Event Hub
  10. The Azure Stream Analytics will then pull the messages from the Azure Event Hub queue
  11. Aggregation/filtering will be performed as per the query defined
  12. The result of the query is then sent as a record in a Power BI table in a dataset.
  13. The Power BI report is based on the dataset, which, when included in a Power BI dashboard, will refresh as new data comes in.

Here’s how the collection process would be invoked:

Get-DataCollectorCDCChange | Convert-DataCollectorCDCChangeToHashTable | Send-SBEventHubMessage -url "<path to the event hub queue" -sasKey "the event hub SAS key (authentication)"

The overall process has a fairly low impact on your source SQL Server. In our case, we’re talking below 25 reads per collection and 15ms of CPU  time.

Here’s a diagram of what the process looks like at a high level:


Here’s more information on the various pieces involved in the process.


Set-DataCollectorCDCConfiguration: Creates the required tables to track the DataCollector operations. It will create the CDCTrackedTables (tracks which table on which server, database, schema) and CDCTrackedTableColumns (which tracks which columns are captured per tracked table).
Get-SQLCDCTable: Lists the tables on a server/database that have CDC enabled.
New-DataCollectorCDCTrackedTable: Creates a record in the CDCTrackedTables table in the DataCollector configuration database. This determines that this table will be collected by the process. It also keeps track of the last LSN that was captured in the process. By default, when the configuration is created for the table, it will take the first LSN that’s available for that object.
New-DataCollectorCDCTrackedTableColumn: This will create a configuration record in the CDCTrackedTableColumns table that contains the name of the column that will be captured by the process. By default, no columns are configured.
: Overarching process for the data collection, contains the loop that will call Get-DataCollectorCDCTrackedDatabase at specific interval.
Get-DataCollectorCDCTrackedDatabase: Gets the list of databases for which the CDC changes will be collected.
Get-DataCollectorCDCTrackedTable: Gets the list of tables for a table for which the CDC changes will be collected.
Get-DataCollectordCDCTrackedTableColumn: Gets the list of columns that will be extracted from the CDC change record. This allows you to select only a few columns that will be sent further down the process. This helps keeping the data/event message size down that will be sent to Azure Event Hub.
Get-DataCollectorCDCDatabaseChange: Gets the CDC change records from the SQL Server instance/database/table selected. A custom PSObject is sent to the output stream that contains the columns selected by Get-DataCollectordCDCTrackedTableColumn.
Set-DataCollectorCDCTrackedTableLastLSN: Updates the CDCTrackedTables record in the DataCollector database with the last LSN captured by the data collection process.
Converts the PSObject to a hashtable to facilitate sending the Azure Event Hub message.


Send-SBEventHubMessage: Sends the CDC change message to Azure Event Hub using JSON as message body.

Azure Event Hub

An Azure Event Hub queue is required to store all the CDC change messages. Those will then be consumed by the Azure Stream Analytics job.

Azure Stream Analytics

An Azure Stream Analytics job is required to process the CDC change messages to perform the filtering/aggregation. You first configure the job input to point to the previously created Event Hub Queue. You will then create a new output that will point to a Power BI account.

You can then define the query based on the defined input and output. Here’s an example of the query I’ve used to generate the dashboard:

SELECT ServerName,
INTO [power-bi-output]
FROM [cdc-eventhub]
GROUP BY ServerName,

In that particular example, a simple COUNT aggregation is performed on all the messages received per server,database,schema and table. That aggregation operates on tumbling window of 2 minutes. Once the window ends, the result of the aggregation is sent to the output, which in our case is a Power BI table in a dataset.

Once that’s defined, you can then start the Azure Stream Analytics job. The process takes a few minutes, once the job has started, it will start outputting data to Power BI. Note that the Power BI dataset/table will not be created until the job output data.

Power BI

The Power BI part is pretty straightforward. You first define a report that connects to your Azure Stream Analytics dataset/table. You then pin that report to an existing or a new Power BI dashboard. Once you are in the dashboard view and the Azure Stream Analytics job outputs data, you will then see the graph update as new data is received.

Here’s what the end results looks like:


Next Steps

Here are some of the future improvements I would like to add:

  • Add a way to enrich the captured data (i.e. foreign key descriptions)
    • By adding reference tables in Azure SQL or
    • By enriching the data as it’s going through the PowerShell pipeline
  • Add infrastructure to orchestrate collection process across multiple servers. i.e Right now the process assumes that all the data collection is done from a single server.


The first release of the code is available on Codeplex with version and forward. I hope this might be valuable to some of you! Should you have any questions regarding this, feel free to let me know through the comments below!

For more information

SQL Server Change Data Capture
Azure Stream Analytics & Power BI: Live dashboard for analytics in real-time on streaming data

GEM Automation

Today I took some time to check-in some of the code I’ve been working on lately on CodePlex. Since it’s been about 6 months since the last release, it’s pretty significant. Here are some of the highlights:

  • Windows crash dumps analysis and automation (Get-CrashDump.ps1,Get-CrashDumpAnalysis,Get-CrashDumpAnalysisReport)
    • Gathers crash dumps from all the computers present in Active Directory by default or from a list of computers in a text file and copies them to a central location (just noticed the path is hardcoded in the script, will fix this soon)
    • Run cdb over the memory dumps gathered in an incremental fashion
    • Extract core attributes from the cdb log files (i.e. module faulting, process name, etc.)
    • Create a summary of the collected crash dump attributes and output it to a csv file (I’ll try to post the Excel workbook I use to analyze the output)
  • libWindowsPerformance.psm1
    • Get-PerformanceMonitoring : Capture perfmon counters from a list of computers and output to a file or to the PowerShell pipeline
    • Filter-CounterValues : Filter perfmon counter samples from the PowerShell pipeline. This is useful to remove the samples that have little interest to you. In one case I used this to get only samples that exceeded 50% Processor time on 275 computers
    • Convert-PerformanceCounterToHashTable: I mainly wrote this as an helper function for when I send the perfmon samples to Azure EventHub
    • Store-PerformanceCounter : A function that persist counter samples from the pipeline to a SQL Server database
    • Execute-CounterTrigger: This is a function I use to execute particular action on a particular counter sample. For instance, in the case where I was gather CPU perfmon samples, I executed a trigger to get the list of active processes when the threshold was met to get an idea of what is using CPU on the 275 computers
    • Get-CounterStatistics: On an already collected perfmon log file, query it to get generic statistics (min, max, avg, total)
    • Start-PerfmonOnComputers: An helper function to make sure the required services are running on remote computers to collect perfmon data
  • libStorageSpaces.psm1
    • Series of core helper function that I used while developing automated tests for Storage Spaces (mainly setup of pool, virtual disks)
  • libSQLServerStatistics.psm1
    • Added new functions to gather buffer pool composition (database and object level)
    • Added functions to persist buffer pool composition over time
  • Small change in Get-VHDHierarchy and Get-VMStorageInformation to use CredSSP (required when you have remote storage on SOFS for instance)
  • libHyperVStatistics.psm1
    • Add function to workaround a bug in resource metering where the metering duration is empty while collecting samples
    • Now capturing individual VHD statistics appropriately
  • Monitor-VMUsage.ps1
    • Now capturing individual VHD statistics appropriately
  • libConfiguration.psm1
    • Added new functions to validate configuration files against the centralized configuration store
  • libIIS.psm1
    • New Get-RemoteWebSite function
    • New Get-ImportedLogFiles function
  • libUtilities
    • Improved Assert-PSSession function
    • New Test-FileLock function
  • Initial release of libNetworking.psm1
    • New Test-Port function which allows you to test TCP and UDP ports
    • New Test-ComputerConnectivity function to test whether a computer is responding through various methods
  • Initial release of libNeo4j.psm1
    • Core functions to manipulate and query data in a Neo4j graph database
    • This is used for a POC of a discovery process written in PowerShell that creates a graph in Neo4j that is used as a CMDB.

You can download the latest release here: GEM Automation

Here are some of my goals for future releases:

  • Improve documentation (both in PowerShell and on CodePlex)
  • Publish CMDB discovery processes that persist data in Neo4j
  • Ensure the code is using the standard configuration store
In the meantime, try to enjoy this minimally documented release! If you have questions about the code, feel free to ask via the comments below.

Streaming Windows Performance Counters to Azure EventHub and Stream Analytics

I’ve had an interesting problem at work where I needed to capture high-resolution perfmon counters samples in order to highlight better an IO consumption issue. Sampling in something like Operations Manager or Hyper-V resource metering just didn’t cut is as the data was too averaged out. This meant we were losing significant IO spikes information. In order to get a better feeling of the data, I used my old friend PowerShell.

In order to achieve this, I used a helper function called Get-PerformanceMonitoring I wrote around the Get-Counter cmdlet. What this wrapper does is to facilitate the setup of capturing pre-determined performance counters for a list of computers. This saves you having to manually pick each and every counter for each machine you want to monitor. There’s also a few options as to what happens when that setup is done. You can send the samples to a file, capture only one sample and create a file (in case you want to do real-time monitoring, you can use the file with all the counters setup) or you can simply output the samples to the standard PowerShell pipeline as a stream.

After the first 24 hours of collecting that data on only 4 servers, I was left with a 7.7GB file. A file this size is quite painful to consult in perfmon. I then used perfmon to export that data as csv to consume it in Excel. This made it a bit easier to manipulate/summarize the data but it was still an unsustainable process in the long run and it’s also difficult to scale to a large number of computers.

I then started to stream the output of the first function into a filter function called Filter-CounterValues I had used to capture CPU spikes on about 350 PCs. By excluding IO spikes below a specific number, I was able to reduce considerably the data captured but I wanted to still capture more accurately minimum and averages for those servers’ disks.

Then I realized that it might finally be a good excuse to kick the tires on Azure Stream Analytics. I had developed a POC in the past using SQL Server Stream Insights, so I was already familiar with the core concepts and capabilities. Here’s an overview diagram of what that looks like:


First step in getting the data processed by a Stream Analytics job was to send it to an Azure Event Hub. Since I already had a library for Azure Service Bus, I figured it would be straightforward to get this POC going. First step was to download the new Service Bus libraries from NuGet. After that was done, I started to write the function to send messages to Event Hub. Unfortunately, I was never quite able to do it using the .Net API. When calling the method on the EventData object to add properties or data to it, I received an error from PowerShell saying it couldn’t find that specific method even though it was showing up while using Get-Member. Someone might have a hint as to what’s going on here! So after much head scratching and not going anywhere, I decided to use Event Hub REST API instead. After spending some time figuring out how to authenticate (not as obvious as you would think), I finally managed to send messages to Event Hub. Step 1, done!

Creating the Stream Analytics job was a simple process. Define your input by picking the Event Hub queue, define your output by picking an Azure SQL Database and specifying a table to store your results and then finally write the actual Stream Analytics query that crunches that data. I was hitting an issue at first where the job would fail with only a message saying that diagnostic data was not available at this time (it never became available). So I spend some time rewriting the query and casting numerical properly (coming from JSON that didn’t happen automatically it seems) and then everything started working as expected.

In summary, here’s what the process look like at a high level:

1) Capture perfmon counter sample
2) Filter counter samples below a certain value
3) Convert the counter sample object to a hashtable
4) Send the event to Azure Event Hub
5) The Azure Stream Analytics picks the event data
6) Query runs according to tumbling window size
7) Results are persisted

Here’s what the PowerShell command look like:

Get-PerformanceMonitoring -computersListFilePath C:\Code\Windows\MonitoringComputersLists\Production_All_SQL_Servers.txt -counters "\LogicalDisk(*)\Disk Transfers/sec" -outputType Stream | Filter-CounterValues -minimumValue 250 -counterPath "\LogicalDisk(*)\Disk Transfers/sec" -instanceNameExclusion "_total"  | Convert-PerformanceCounterToHashTable -thresholdValue 250 -counterPath "\LogicalDisk(*)\Disk Transfers/sec" | Send-SBEventHubMessage -url "https://<eventhubname>"

Right now I have two jobs defined that are consuming the same stream of messages/events. I have one that calculating the min/max/avg/count for each counter instance using a 5 minutes tumbling window and another using an hour window. Now it’s very straightforward to have data aggregates created using various windows size (5min,60min, daily,etc) without having to persist highly detailed intermediary data. Here’s what the Stream Analytics query looks like:

    ComputerName,CounterName,InstanceName,System.TimeStamp AS SampleTime,MIN(CounterValue) AS MinCounterValue,MAX(CounterValue) AS MaxCounterValue,AVG(CounterValue) AS AvgCounterValue,Count(*) AS SampleCount 
GROUP BY ComputerName,CounterName,InstanceName,TumblingWindow(hour,1)

You can then monitor the jobs execution using the dashboard to keep track of inbound messages and output events:


You can also check Event Hub statistics to have an idea of the rate at which the event are sent:


Once everything was running smoothly, I just built a simple Excel workbook to consume this new data. Here’s what that looks like with some data:

IOPS Per Server

IOPS Per Logical Volume

I will also investigate using Power BI to create a proper dashboard. Let me know if you have any questions about this, I’ll be glad to share more detail about my experience.