Friday, July 21, 2017

Creating a Complex Custom Azure Role

We recently had the need to create a custom role in the Azure Portal which stopped a set of administrators from creating networks or virtual machines.

This was because we are planning to share our ExpressRoute connection with their subscription and we only allow IT to add new devices to our network or domain.

Now the standard Azure RBAC roles don't do anything like this.  These roles are typically configured with only a small set of permissions.

The role needed the following setup

Allow All
Allow start, stop, deallocate VM
Deny All Compute
Deny All Network
Deny All Permissions

The following article was pretty useful in describing the process of creating the custom role.  There are a few methods, but I opted for the creation of the JSON file.

To get the actual permissions required to build the JSON file itself, we needed to run the following commands

Get-AzureRMProviderOperation Microsoft.Compute/*
Get-AzureRMProviderOperation Microsoft.Network/*

Output in Powershell

This produced a list of all of the operations which can be allowed or denied.  I needed to export this to ensure I allowed the administrators to be able to start, stop and deallocate VMs.

The following items are the permissions we want to allow the users to have.

Operation         : Microsoft.Compute/virtualMachines/read
OperationName     : Get Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Get the properties of a virtual machine

Operation         : Microsoft.Compute/virtualMachines/start/action
OperationName     : Start Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Starts the virtual machine

Operation         : Microsoft.Compute/virtualMachines/powerOff/action
OperationName     : Power Off Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Powers off the virtual machine. Note that the virtual machine will continue to be billed.

Operation         : Microsoft.Compute/virtualMachines/restart/action
OperationName     : Restart Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Restarts the virtual machine

Operation         : Microsoft.Compute/virtualMachines/deallocate/action
OperationName     : Deallocate Virtual Machine
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machines
Description       : Powers off the virtual machine and releases the compute resources

Operation         : Microsoft.Compute/virtualMachines/instanceView/read
OperationName     : Get Virtual Machine Instance View
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machine Instance View
Description       : Gets the detailed runtime status of the virtual machine and its resources

Operation         : Microsoft.Compute/locations/vmSizes/read
OperationName     : List Available Virtual Machine Sizes in Location
ProviderNamespace : Microsoft Compute
ResourceName      : Virtual Machine Sizes
Description       : Lists available virtual machine sizes in a location

There were a few more than originally intended e.g. Instance view, list sizes etc.  We remove these entries from the Output, filter just on Operation and copy these items into the NotActions section of the JSON file.

The next step was to create a JSON file.  The * in Actions allows all permissions and then we use the NotActions to Deny Operation permissions.
{
  "Name": "BI administrator",
  "Id": "null",
  "IsCustom": true,
  "Description": "Can do everything other than create virtual machines and manage networking.",
  "Actions": ["*"
  ],
  "NotActions": [
"Microsoft.Compute/availabilitySets/delete",
"Microsoft.Compute/availabilitySets/read",
"Microsoft.Compute/availabilitySets/vmSizes/read",
"Microsoft.Compute/availabilitySets/write",
"Microsoft.Compute/disks/beginGetAccess/action",
"Microsoft.Compute/disks/delete",
"Microsoft.Compute/disks/endGetAccess/action",
"Microsoft.Compute/disks/read",
"Microsoft.Compute/disks/write",
"Microsoft.Compute/images/delete",
"Microsoft.Compute/images/read",
"Microsoft.Compute/images/write",
"Microsoft.Compute/locations/diskOperations/read",
"Microsoft.Compute/locations/operations/read",
"Microsoft.Compute/locations/runCommands/read",
"Microsoft.Compute/locations/usages/read",
"Microsoft.Compute/operations/read",
"Microsoft.Compute/register/action",
"Microsoft.Compute/restorePointCollections/delete",
"Microsoft.Compute/restorePointCollections/read",
"Microsoft.Compute/restorePointCollections/restorePoints/delete",
"Microsoft.Compute/restorePointCollections/restorePoints/read",
"Microsoft.Compute/restorePointCollections/restorePoints/retrieveSasUris/action",
"Microsoft.Compute/restorePointCollections/restorePoints/write",
"Microsoft.Compute/restorePointCollections/write",
"Microsoft.Compute/snapshots/beginGetAccess/action",
"Microsoft.Compute/snapshots/delete",
"Microsoft.Compute/snapshots/endGetAccess/action",
"Microsoft.Compute/snapshots/read",
"Microsoft.Compute/snapshots/write",
"Microsoft.Compute/virtualMachineScaleSets/deallocate/action",
"Microsoft.Compute/virtualMachineScaleSets/delete",
"Microsoft.Compute/virtualMachineScaleSets/delete/action",
"Microsoft.Compute/virtualMachineScaleSets/extensions/delete",
"Microsoft.Compute/virtualMachineScaleSets/extensions/read",
"Microsoft.Compute/virtualMachineScaleSets/extensions/write",
"Microsoft.Compute/virtualMachineScaleSets/instanceView/read",
"Microsoft.Compute/virtualMachineScaleSets/manualUpgrade/action",
"Microsoft.Compute/virtualMachineScaleSets/powerOff/action",
"Microsoft.Compute/virtualMachineScaleSets/read",
"Microsoft.Compute/virtualMachineScaleSets/reimage/action",
"Microsoft.Compute/virtualMachineScaleSets/restart/action",
"Microsoft.Compute/virtualMachineScaleSets/rollingUpgrades/cancel/action",
"Microsoft.Compute/virtualMachineScaleSets/rollingUpgrades/read",
"Microsoft.Compute/virtualMachineScaleSets/scale/action",
"Microsoft.Compute/virtualMachineScaleSets/skus/read",
"Microsoft.Compute/virtualMachineScaleSets/start/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/deallocate/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/delete",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/instanceView/read",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/powerOff/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/read",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/reimage/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/restart/action",
"Microsoft.Compute/virtualMachineScaleSets/virtualMachines/start/action",
"Microsoft.Compute/virtualMachineScaleSets/write",
"Microsoft.Compute/virtualMachines/capture/action",
"Microsoft.Compute/virtualMachines/convertToManagedDisks/action",
"Microsoft.Compute/virtualMachines/delete",
"Microsoft.Compute/virtualMachines/extensions/delete",
"Microsoft.Compute/virtualMachines/extensions/read",
"Microsoft.Compute/virtualMachines/extensions/write",
"Microsoft.Compute/virtualMachines/generalize/action",
"Microsoft.Compute/virtualMachines/performMaintenance/action",
"Microsoft.Compute/virtualMachines/redeploy/action",
"Microsoft.Compute/virtualMachines/runCommand/action",
"Microsoft.Compute/virtualMachines/write",
"Microsoft.Network/applicationGatewayAvailableWafRuleSets/read",
"Microsoft.Network/applicationGateways/backendAddressPools/join/action",
"Microsoft.Network/applicationGateways/backendhealth/action",
"Microsoft.Network/applicationGateways/delete",
"Microsoft.Network/applicationGateways/read",
"Microsoft.Network/applicationGateways/start/action",
"Microsoft.Network/applicationGateways/stop/action",
"Microsoft.Network/applicationGateways/write",
"Microsoft.Network/bgpServiceCommunities/read",
"Microsoft.Network/checkTrafficManagerNameAvailability/action",
"Microsoft.Network/connections/delete",
"Microsoft.Network/connections/read",
"Microsoft.Network/connections/sharedKey/read",
"Microsoft.Network/connections/sharedKey/write",
"Microsoft.Network/connections/vpndeviceconfigurationscript/read",
"Microsoft.Network/connections/write",
"Microsoft.Network/dnsoperationresults/read",
"Microsoft.Network/dnsoperationstatuses/read",
"Microsoft.Network/dnszones/A/delete",
"Microsoft.Network/dnszones/A/read",
"Microsoft.Network/dnszones/A/write",
"Microsoft.Network/dnszones/AAAA/delete",
"Microsoft.Network/dnszones/AAAA/read",
"Microsoft.Network/dnszones/AAAA/write",
"Microsoft.Network/dnszones/CNAME/delete",
"Microsoft.Network/dnszones/CNAME/read",
"Microsoft.Network/dnszones/CNAME/write",
"Microsoft.Network/dnszones/delete",
"Microsoft.Network/dnszones/MX/delete",
"Microsoft.Network/dnszones/MX/read",
"Microsoft.Network/dnszones/MX/write",
"Microsoft.Network/dnszones/NS/delete",
"Microsoft.Network/dnszones/NS/read",
"Microsoft.Network/dnszones/NS/write",
"Microsoft.Network/dnszones/PTR/delete",
"Microsoft.Network/dnszones/PTR/read",
"Microsoft.Network/dnszones/PTR/write",
"Microsoft.Network/dnszones/read",
"Microsoft.Network/dnszones/recordsets/read",
"Microsoft.Network/dnszones/SOA/read",
"Microsoft.Network/dnszones/SOA/write",
"Microsoft.Network/dnszones/SRV/delete",
"Microsoft.Network/dnszones/SRV/read",
"Microsoft.Network/dnszones/SRV/write",
"Microsoft.Network/dnszones/TXT/delete",
"Microsoft.Network/dnszones/TXT/read",
"Microsoft.Network/dnszones/TXT/write",
"Microsoft.Network/dnszones/write",
"Microsoft.Network/expressRouteCircuits/authorizations/delete",
"Microsoft.Network/expressRouteCircuits/authorizations/read",
"Microsoft.Network/expressRouteCircuits/authorizations/write",
"Microsoft.Network/expressRouteCircuits/delete",
"Microsoft.Network/expressRouteCircuits/peerings/arpTables/action",
"Microsoft.Network/expressRouteCircuits/peerings/delete",
"Microsoft.Network/expressRouteCircuits/peerings/read",
"Microsoft.Network/expressRouteCircuits/peerings/routeTables/action",
"Microsoft.Network/expressRouteCircuits/peerings/routeTablesSummary/action",
"Microsoft.Network/expressRouteCircuits/peerings/stats/read",
"Microsoft.Network/expressRouteCircuits/peerings/write",
"Microsoft.Network/expressRouteCircuits/read",
"Microsoft.Network/expressRouteCircuits/stats/read",
"Microsoft.Network/expressRouteCircuits/write",
"Microsoft.Network/expressRouteServiceProviders/read",
"Microsoft.Network/loadBalancers/backendAddressPools/join/action",
"Microsoft.Network/loadBalancers/backendAddressPools/read",
"Microsoft.Network/loadBalancers/delete",
"Microsoft.Network/loadBalancers/frontendIPConfigurations/read",
"Microsoft.Network/loadBalancers/inboundNatPools/join/action",
"Microsoft.Network/loadBalancers/inboundNatPools/read",
"Microsoft.Network/loadBalancers/inboundNatRules/delete",
"Microsoft.Network/loadBalancers/inboundNatRules/join/action",
"Microsoft.Network/loadBalancers/inboundNatRules/read",
"Microsoft.Network/loadBalancers/inboundNatRules/write",
"Microsoft.Network/loadBalancers/loadBalancingRules/read",
"Microsoft.Network/loadBalancers/networkInterfaces/read",
"Microsoft.Network/loadBalancers/outboundNatRules/read",
"Microsoft.Network/loadBalancers/probes/read",
"Microsoft.Network/loadBalancers/read",
"Microsoft.Network/loadBalancers/virtualMachines/read",
"Microsoft.Network/loadBalancers/write",
"Microsoft.Network/localnetworkgateways/delete",
"Microsoft.Network/localnetworkgateways/read",
"Microsoft.Network/localnetworkgateways/write",
"Microsoft.Network/locations/checkDnsNameAvailability/read",
"Microsoft.Network/locations/operationResults/read",
"Microsoft.Network/locations/operations/read",
"Microsoft.Network/locations/privateAccessServices/read",
"Microsoft.Network/locations/usages/read",
"Microsoft.Network/networkInterfaces/delete",
"Microsoft.Network/networkInterfaces/effectiveNetworkSecurityGroups/action",
"Microsoft.Network/networkInterfaces/effectiveRouteTable/action",
"Microsoft.Network/networkInterfaces/ipconfigurations/read",
"Microsoft.Network/networkInterfaces/join/action",
"Microsoft.Network/networkInterfaces/loadBalancers/read",
"Microsoft.Network/networkInterfaces/read",
"Microsoft.Network/networkInterfaces/write",
"Microsoft.Network/networkSecurityGroups/defaultSecurityRules/read",
"Microsoft.Network/networkSecurityGroups/delete",
"Microsoft.Network/networkSecurityGroups/join/action",
"Microsoft.Network/networkSecurityGroups/read",
"Microsoft.Network/networkSecurityGroups/securityRules/delete",
"Microsoft.Network/networkSecurityGroups/securityRules/read",
"Microsoft.Network/networkSecurityGroups/securityRules/write",
"Microsoft.Network/networkSecurityGroups/write",
"Microsoft.Network/networkWatchers/configureFlowLog/action",
"Microsoft.Network/networkWatchers/delete",
"Microsoft.Network/networkWatchers/ipFlowVerify/action",
"Microsoft.Network/networkWatchers/nextHop/action",
"Microsoft.Network/networkWatchers/packetCaptures/delete",
"Microsoft.Network/networkWatchers/packetCaptures/queryStatus/action",
"Microsoft.Network/networkWatchers/packetCaptures/read",
"Microsoft.Network/networkWatchers/packetCaptures/stop/action",
"Microsoft.Network/networkWatchers/packetCaptures/write",
"Microsoft.Network/networkWatchers/queryFlowLogStatus/action",
"Microsoft.Network/networkWatchers/queryTroubleshootResult/action",
"Microsoft.Network/networkWatchers/read",
"Microsoft.Network/networkWatchers/securityGroupView/action",
"Microsoft.Network/networkWatchers/topology/action",
"Microsoft.Network/networkWatchers/troubleshoot/action",
"Microsoft.Network/networkWatchers/write",
"Microsoft.Network/operations/read",
"Microsoft.Network/publicIPAddresses/delete",
"Microsoft.Network/publicIPAddresses/join/action",
"Microsoft.Network/publicIPAddresses/read",
"Microsoft.Network/publicIPAddresses/write",
"Microsoft.Network/register/action",
"Microsoft.Network/routeFilters/delete",
"Microsoft.Network/routeFilters/join/action",
"Microsoft.Network/routeFilters/read",
"Microsoft.Network/routeFilters/rules/delete",
"Microsoft.Network/routeFilters/rules/read",
"Microsoft.Network/routeFilters/rules/write",
"Microsoft.Network/routeFilters/write",
"Microsoft.Network/routeTables/delete",
"Microsoft.Network/routeTables/join/action",
"Microsoft.Network/routeTables/read",
"Microsoft.Network/routeTables/routes/delete",
"Microsoft.Network/routeTables/routes/read",
"Microsoft.Network/routeTables/routes/write",
"Microsoft.Network/routeTables/write",
"Microsoft.Network/trafficManagerGeographicHierarchies/read",
"Microsoft.Network/trafficManagerProfiles/azureEndpoints/delete",
"Microsoft.Network/trafficManagerProfiles/azureEndpoints/read",
"Microsoft.Network/trafficManagerProfiles/azureEndpoints/write",
"Microsoft.Network/trafficManagerProfiles/delete",
"Microsoft.Network/trafficManagerProfiles/externalEndpoints/delete",
"Microsoft.Network/trafficManagerProfiles/externalEndpoints/read",
"Microsoft.Network/trafficManagerProfiles/externalEndpoints/write",
"Microsoft.Network/trafficManagerProfiles/nestedEndpoints/delete",
"Microsoft.Network/trafficManagerProfiles/nestedEndpoints/read",
"Microsoft.Network/trafficManagerProfiles/nestedEndpoints/write",
"Microsoft.Network/trafficManagerProfiles/read",
"Microsoft.Network/trafficManagerProfiles/write",
"Microsoft.Network/unregister/action",
"Microsoft.Network/virtualnetworkgateways/supportedvpndevices/read",
"Microsoft.Network/virtualNetworks/checkIpAddressAvailability/read",
"Microsoft.Network/virtualNetworks/delete",
"Microsoft.Network/virtualNetworks/peer/action",
"Microsoft.Network/virtualNetworks/read",
"Microsoft.Network/virtualNetworks/subnets/delete",
"Microsoft.Network/virtualNetworks/subnets/join/action",
"Microsoft.Network/virtualNetworks/subnets/joinPrivateAccessService/action",
"Microsoft.Network/virtualNetworks/subnets/joinViaServiceTunnel/action",
"Microsoft.Network/virtualNetworks/subnets/read",
"Microsoft.Network/virtualNetworks/subnets/virtualMachines/read",
"Microsoft.Network/virtualNetworks/subnets/write",
"Microsoft.Network/virtualNetworks/virtualMachines/read",
"Microsoft.Network/virtualNetworks/virtualNetworkPeerings/delete",
"Microsoft.Network/virtualNetworks/virtualNetworkPeerings/read",
"Microsoft.Network/virtualNetworks/virtualNetworkPeerings/write",
"Microsoft.Network/virtualNetworks/write",
"Microsoft.Authorization/*/Delete", 
"Microsoft.Authorization/*/Write",
"Microsoft.Authorization/elevateAccess/Action"
 ],
  "AssignableScopes": [
    "/subscriptions/123456789-1234-1234-1234-123456789123",
  ]
}

You will notice the last three entries

"Microsoft.Authorization/*/Delete", 
"Microsoft.Authorization/*/Write",
"Microsoft.Authorization/elevateAccess/Action"

These will stop the administrator being able to change the permissions for themselves.  Clearly if I missed this, the whole exercise would be pointless!

If you want to use this JSON file, you will just need to replace the name, description and the subscription ID.

You will then need to create a new Role from the JSON file.  This article has more detail on how to do this, but the PowerShell command you will need to run is
New-AzureRmRoleDefinition -InputFile "C:\CustomRoles\customrole1.json"

Role in Azure Portal
And there you have it!  A custom Azure Role that can do nearly everything other than VMs, network and permissions.

Friday, July 07, 2017

Exchange Hybrid Mailbox Move - Corruption Due To Missing Security Principals (ACL issues)

About midway through 2016, a change was introduced to Exchange Online whereby if a security principal could not be successfully validated/mapped to an Exchange Online object, it would be marked as a bad item. Previously, the behaviour was that invalid permissions would simply be ignored, and administrators were then left to wonder why some permissions no longer worked after the migration. With this new behaviour, corrupt/invalid permissions are now logged so that administrators will know that there are problems with permissions.  The following article covers this in more detail: https://blogs.technet.microsoft.com/exchange/2017/05/30/toomanybaditemspermanentexception-error-when-migrating-to-exchange-online/?replytocom=310635#respond  

The end result users granted mailbox permissions but have since left the organisation and had their AD user account deleted will show as a corrupt item when that mailbox is migrated to Exchange Online.  These errors were previously hidden from the log but they're not marked as corruptions which means you need to raise that corruption level for your mailbox move/s.

In order to decipher which corruptions are are genuine are which are related to ACL issues, you can run the following script.  The script can be run against a migration batch in PowerShell for Exchange Online to determine and output which mailbox migrations have genuine corruptions and require investigation and which do not.

3 files will be output to directory specified with a subfolder with a timestamp prepended.

    1. A summary CSV output including the mailboxes queried along with the details in the table above.
    2. An output of all corrupt bad items found, including ACL security principals, should they need to be queried.

    3. A migrations XML output for each mailbox migration - this can imported into any PowerShell session at a later date and queried; this allows you to remove the migration job but keep a logged record.

Here is an example of what is output to screen:




<#
.NOTES
    Author: Ben Owens
    LinkedIn: https://www.linkedin.com/in/owensben/
    Creation Date: 04/07/2017
    Purpose: This script will query all the mailboxes in a migration batch 
    and detail which of the reported corruptions are genuine and which 
    corruptions related to ACL security principals that don't exist in the source or target forests.  
    This is to help manage the  change in behaviour as covered in the article 
    https://blogs.technet.microsoft.com/exchange/2017/05/30/toomanybaditemspermanentexception-error-when-migrating-to-exchange-online/?replytocom=310635#respond 
.INPUTS
    You will be prompted to enter a batch name when you ruin the script
    
.OUTPUTS
    Configure the $LogDirectory variable at the top of the script.
    3 files will be output to directory specified with a subfolder with a timestamp prepended.
    1. A summary CSV output including the mailboxes queried along with the details in the table above.
    2. An output of all corrupt bad items found, including ACL security principals, should they need to be queried.
    3. A migrations XML output for each mailbox migration - this can imported into any PowerShell session at a later date and queried; this allows you to remove the migration job but keep a logged record.

#>

$LogDate = get-date -f yyyyMMddhhmmss
$LogDirectory = Split-Path -parent "C:\BenTemp\$LogDate\*.*"
$Results = @()

$BatchID = Read-Host -Prompt 'Enter the batch name here'
Mkdir $LogDirectory

$MigrationUsers = Get-MigrationUser -BatchID $BatchID

ForEach ($User in $MigrationUsers) {
    $Statistics = Get-MoveRequestStatistics -Identity $User.Identity -IncludeReport
    #$MoveStatistics = Get-MoveRequestStatistics -Identity $User.Identity
    $MoveStatus = $Statistics | Select -ExpandProperty Status | Select -ExpandProperty Value
    $MoveStatusDetail = $Statistics | Select -ExpandProperty StatusDetail | Select -ExpandProperty Value
    $Identity = Get-User $User.Identity | Select -ExpandProperty Name
    $XMLPath = $logdirectory + "\" + $Identity + "_MigrationReport.xml"
    $Statistics | Export-CliXml $XMLPath
    $ReportedFailures = $Statistics.Report.BadItems
    $CSVPath = $logdirectory + "\" + $Identity + "_CorruptItemsOverview.csv"
    If ($ReportedFailures -eq $NULL) {
        Write-Host "No bad items to report for" $Identity
    }
    Else {
        $ReportedFailures | Select Kind,FolderName,WellKnownFolderType,Subject,Failure,Category | Export-CSV $CSVPath -NoTypeInformation
    }
    $ReportedSourceACLFailures = $ReportedFailures | Where {$_.Category -like "SourcePrincipalError"}
    $ReportedTargetACLFailures = $ReportedFailures | Where {$_.Category -like "TargetPrincipalError"}
    $FilteredReportedFailures = $ReportedFailures | Where {$_.Category -notlike "SourcePrincipalError"}
    $FilteredReportedFailures = $FilteredReportedFailures | Where {$_.Category -notlike "TargetPrincipalError"}
    
    If ($ReportedFailures -eq $NULL) {
        $Result = new-object PSObject -Property @{
                        #Identity = $MigrationUsers.Identity;
                        Identity = $Identity;
                        MoveStatus = $MoveStatus;
                        MoveStatusDetail = $MoveStatusDetail;
                        Corruptions = $ReportedFailures.count
                        GenuineCorruptions = $FilteredReportedFailures.count
                        SourcePrincipalErrors = $ReportedSourceACLFailures.count
                        TargetPrincipalErrors = $ReportedTargetACLFailures.count
                        Comment = "No corruptions to investigate"
                    }
                    $Results += $Result
    }

    ElseIf ($FilteredReportedFailures -eq $NULL) {
        $Result = new-object PSObject -Property @{
                        #Identity = $MigrationUsers.Identity;
                        Identity = $Identity;
                        MoveStatus = $MoveStatus;
                        MoveStatusDetail = $MoveStatusDetail
                        Corruptions = $ReportedFailures.count
                        GenuineCorruptions = $FilteredReportedFailures.count
                        SourcePrincipalErrors = $ReportedSourceACLFailures.count
                        TargetPrincipalErrors = $ReportedTargetACLFailures.count
                        Comment = "Only ACL issues - no need to investigate"
                    }
                    $Results += $Result
    }
    Else {
        $Result = new-object PSObject -Property @{
                        #Identity = $MigrationUsers.Identity;
                        Identity = $Identity;
                        MoveStatus = $MoveStatus;
                        MoveStatusDetail = $MoveStatusDetail;
                        Corruptions = $ReportedFailures.count
                        GenuineCorruptions = $FilteredReportedFailures.count
                        SourcePrincipalErrors = $ReportedSourceACLFailures.count
                        TargetPrincipalErrors = $ReportedTargetACLFailures.count
                        Comment = "Investigation required!"
                    }
                    $Results += $Result
    }
}

$Results | ft Identity, MoveStatus, MoveStatusDetail,Corruptions,SourcePrincipalErrors,TargetPrincipalErrors,GenuineCorruptions,Comment
$ResultsPath = $logdirectory + "\" + "_ReportSummary.csv"
$Results | Select Identity, MoveStatus, MoveStatusDetail,Corruptions,SourcePrincipalErrors,TargetPrincipalErrors,GenuineCorruptions,Comment | Export-CSV $ResultsPath -NoTypeInformation

Write-Host "Go to" $LogDirectory "for output log files" -ForegroundColor Yellow

Exchange Back Pressure and Safety Net

The following article covers a scenario where 2 Exchange 2013 servers were in the same Active Directory site. There is no DAG in place and the Safety Net settings had the default configuration.

Issue: 

A receive connector was set-up on ExchangeSrv#1 to allow relays from 3rd party of line of business applications. ExchangeSrv#1 had plenty of space, not low on resources and was not in a back pressure condition. A 3rd party system was in place and submitting emails using Telnet.

The majority of emails being submitted were being rejected with the error "453 4.3.1 Insufficient system resources". ExchangeSrv#2, did not have the same receive connector configured and was not accepting emails from the 3rd party system. It did however have little of no disk space left on the C:\ drive which also hosted the transport queue file.

In the event viewer ExchangeSrv#2 was in back pressure. It was discovered that multiple IIS logs files were consuming space on the C:\ drive of ExchangeSrv#2. A process was put in place to truncate the IIS logs. After clearing space on the C:\ drive of ExchangeSrv#2, we were then able to submit emails again to ExchangeSrv#1.

Reason: 

So why was the low disk space and back pressure issue on ExchangeSrv#2 the cause of the insufficient system resources error on emails submitted ExchangeSrv#1. In short, it was because of Safety Net in the Transport Role.

By default emails submitted and processed on an Exchange server are kept in the mail queue for a set period of time. Copied of these emails are also kept in the mail queue on Exchange server in the same local Active Directory site.

Because there was no space left on ExchangeSrv#2, Safety Net features couldn't be achieved and therefore the emails were rejected.

Summary:

Be aware that back pressure on seemingly unused Exchange 2013's servers can cause mail submissions issues on your other Exchange 2013 servers.


Thursday, May 11, 2017

Creating Users in Azure SQL

I have been working quite a lot in Azure recently and have done some work with SQL Azure PaaS databases.

I am not an SQL DBA by any stretch of the imagination, so some tasks have taken a bit of Googling to conquer!

There are some key differences between a full SQL install and Azure SQL.  One of the main differences with Azure SQL Azure PaaS databases is that you need to do so much more through Transact-SQL.  My limited experience of SQL has all been based through GUI prompts, so this initially proved a problem for me.

One of the tasks which I have had to complete a fair amount recently is to create database users.  In the Azure Portal you can create administrator accounts for the whole server, but this doesn't match the requirements of the business in most occasions.

I came across these blog posts on the Microsoft website

https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

So to create a read-only user for a single database, the Transact-SQL you need are

USE [master]
GO
CREATE LOGIN readonlyuser WITH password='supersecurepassword1';
USE [specificDB]
GO
CREATE USER readonlyuser FROM LOGIN readonlyuser;
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

Now....when a user tries to connect with this account, they will most likely get this error.


The reason this error occurs is because you haven't added the user to the master database.  The trouble is that if you add the user to the master database, they will get Guest access and be able to see all of the databases on the server.  They will not be able to access the contents, but they will be able see their presence.

If you are not fussed by this, you can run the following Transact-SQL to solve your issue.


USE [master]
GO
CREATE USER readonlyuser FROM LOGIN readonlyuser;

Alternatively, you can inform the user to type in the specific database in the options of SQL Studio



Now, all of you SQL on-prem DBAs are probably yelling at the screen "use the default_database" T-SQL.  Well, guess what?  It isn't supported in Azure (yes, this is mentioned in the first link on the page).


I hope this is useful!

Sam