Tips to resolve "Access is denied" error when running Office 365 Compliance Center reports from Remote PowerShell command line

You can use the Office 365 activity report in the Office 365 Compliance Center to view user and admin activity in your Office 365 organization. The report contains entries from the Office 365 user and admin activity log for activity in SharePoint Online, OneDrive for Business, and Azure Active Directory, which is the directory service for Office 365. In our case, we are interested in the Audited events in the Office 365 activity report.

Since the report from UI only display 100 record, it would be much easier to manage yourOffice 365 Compliance Center settings from the Remote PowerShell command line. You use Windows PowerShell on your local computer to create a remote Shell session to the Compliance Center. It’s a simple three-step process where you enter your Office 365 credentials, provide the required connection settings, and then import the Compliance Center cmdlets into your local Windows PowerShell session so that you can use them.

Access is denied is the most common error when you use the Office 365 Compliance Center settings from the Remote PowerShell command line. There are at least two different Access is denied error as below.

New-PSSession :  [ps.compliance.protection.outlook.com] Connecting to remote server ps.compliance.protection.outlook.com failed with the following error : Access is denied.

New-PSSession :  [ outlook.office365.com] Connecting to remote server outlook.office365.com failed with the following message :

[ClientAccessServer=BY1PR13CA0016,BackEndServer=by1pr02mb1193.na,prd02.prod.outlook.com,RequestId=bf4b2467-03cf-465a-bf9d-6c5574a49f92,TimeStamp=6/1/2015 10:51:51 PM] Access Denied

There are two common issues that are permission issue and MFA configuration we will explain below to eliminate the access denied error. 

First, you should grant the proper permissions to the account that will run the Office 365 Compliance Center reports. You should need to make sure all the following permissions assigned to this account.

• Globaladministrator
• ComplianceAdministrator 
• Exchange license 
• Exchange Compliance Administrator

You could following the links to assign the first two permissions. Since the compliance center is leverage the exchange search on the backed, this account would need to assign the exchange license and then add exchange compliance administrator permission. You could browse to the exchange admin center and within permissions add the same account under Compliance management as in the below screenshot. This seems to be logical since the reports are leverage the exchange architecture. 

Second, you might need to disable the MFA for the account. At this time, the Remote PowerShell command line does not support MFA and this seems to be obvious. You could disable the MFA by browse the active users and select MFA settings as below screenshot.

You will find the error from Powershell log if the account is MFA enabled. You could use the Powershell to verify whether this account is MFA enabled or not. 

Get-MsolUser -UserPrincipalName <upn of the user>| fl

Here are the example attributes that will indicate whether MFA is enabled for a user or not:

StrongAuthenticationRequirements       : {Microsoft.Online.Administration.StrongAuthenticationRequirement}

StrongAuthenticationUserDetails        :

StrongPasswordRequired                 : True

Now you should have the account that could be used to generate the Office 365 activity report. Here is the sample script you could adjust for your own purpose.

$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

Import-PSSession $Session

$logs = Search-UnifiedAuditLog -StartDate "3/1/2015" -EndDate "3/7/2015" -RecordType SharePointFileOperation

#I would like to exclude the O365 crawl account activities from the report

$logs | %{$_.AuditData} | ConvertFrom-Json | ? {$_.userid -ne '0#.w|ylo001\_spocrwl_162_11435'} |

#select the properties you really need below, if you need all the properties - skip the Select statement, and directly pipe to CSV.

select userid,userkey,creationtime,operation,objectid,itemtype,siteurl,sourcefilename,sourcerelativeurl  |

Export-Csv -Path E:\logs-3-1.csv

There are some options you could use for Search-UnifiedAuditLog command.

SYNTAX

Search-UnifiedAuditLog

-StartDate <ExDateTime> #Search start time, e.g. "2/1/2015" or "2/1/2015 3:15pm"

-EndDate <ExDateTime> #Search end time, e.g. "2/1/2015" or "2/1/2015 3:15pm"

[-RecordType <AuditRecordType> {ExchangeAdmin  | ExchangeItem | ExchangeItemGroup | SharePoint | SyntheticProbe | SharePointFileOperation | OneDrive}]

[-ObjectIds <string[]>] #Array of objects, could be partial name, e.g. @(“document”, “.docx”) or “.pptx”

[-UserIds <string[]>] #Array of user Ids, e.g. @(“joe@contoso.com”, “bob@contoso.com”) or “kata@contoso.com”

[-Operations <string[]>] #Array of operation or event names, e.g. @(“FileDownload”, “FileView”) or “SharingSet”

[-FreeText <string>] #Full text search against any text within events

[-ResultSize <int>] #Top N records to return

[-Identity <UnifiedAuditLogEventIdParameter>] #Id to represent a record, if you want to re-search this exact events

We found the current O365 Activity report only returns 2,000 most recent events in the last 7 days are returned from Remote Powershell. The auditing is designed to keep just 30 days at this time. The powershell does not return the following  user login actions as you could get from UI.

• ForeignRealmIndexLogonInitialAuthUsingADFSFederatedToken
• PasswordLogonInitialAuthUsingPassword

I heard from Microsoft Ignight conference that Microsoft will have a plan to provide Management API we could use in the future to leverage REST calls to interact the O365 reports and it will provide service to keep audit data forever. These changes will be extremely helpful to automate the reports and provide solution for compliance and auditing requirements.

Create a Visual Studio Project, integrate with GitHub, run, and debug PowerShell Scripts just like other C# code

While we are using  Visual Studio 2015 with GitHub for all SharePoint C# projects,  we would like to manage the PowerShell or other scripts in the same way as Visual Studio projects. We could integrate the scripts with Visual Studio projects, GitHub, and be able to run and debug without leaving Visual Studio to improve developers’ productivity. Here are the procedure that will help you to set up.

1. First you need to install the Visual Studio 2015 with GitHub extension. See my previous blog for details.

2. Second you need to install PowerShell tool for visual studio 2015.

3. Third you can create the Powershell Project like the screenshot below. You can organize the folders, scripts, and documents same way as other SharePoint project as described here.

4. You can integrate the GitHub just like other C# code as described in previously blog. 

5. Now you can add command or shortcut key when right click the PowerShell script or somewhere else like the screenshot below.

You can see there is default command named "Run with PowerShell ISE" available. You can invoke the command and run PowerShell. However, everytime the code changed, you need to invoke this external UI again. Here is easy way to set up ti run the PowerShell and debug directly from Visual Studio just like the screenshot above with "Run powershell script in output window". 

The steps are described in Nick's blog.

6. Next, you need set the PowerShell execution policy using Set-ExecutionPolicy to avoid the exception. Please you need to run as admin and on the PowerShell version you are using like X86. You may also need to run the Visual Studio as admin.

Set-ExecutionPolicy RemoteSigned

7. You might need to update PowerShell config as described here to enable attaching PowerShell to some process for debugging.

Now you can enjoy developing, collaborating with GitHub, running, and debugging the PowerShell directly inside Visual Studio!

Powershell cmdlets to compare user's AD attributes with SharePoint user profile

When trouble shooting SharePoint user identity related issues such as UPN and SID values, it will be extremely helpful to compare the values from SharePoint with AD since SharePoint will synchronize users’ AD attributes through user profile services.  One example is some users changed their AD UPN from userid@domain.company.com to  userid@company.com and we are not sure whether this will have any impact to SharePoint. In this blog, we will provide the Powershell to query both AD and SharePoint user profile.

You could use Active Directory Explorer to view user’s AD attributes and it would be easy is to use Active Directory Cmdlets in Windows PowerShell for automation. If you have never used Active Directory Cmdlets, you should verify whether you have the feature installed by the following powershell.

Get-WindowsFeature | where displayname –like “*active dir*”

Get-WindowsFeature  rsat-AD-powershell

You could follow the instruction here to enable the AD remote powershell feature or use the powershell command to enable it.

Add--WindowsFeature  rsat-AD-powershell

This is simple example to get AD user through user CN. Please note, this will NOT work if you have multiple AD domains.

import-module activedirectory
get-module -listavailable

$wUser = "userid"

$aduser = Get-ADUser -Identity $wUser

$aduser

This is simple example to get AD user through user CN and it will work on multiple AD domains. This will also include the deleted users.

$usercn= "userid"

$ldapfilter= "(&(objectclass=user)(!objectclass=computer)(cn=" + $usercn  + "))"

$users = Get-adobject -ldapfilter $ldapfilter -server "ADController:portnumber" -IncludeDeletedObjects -properties DisplayName, userPrincipalName, cn

$users 

In order to get SharePoint user’s atttributes, you would need to configure the UPS and complete the full sync. You could use the following powershell to retrieve user’s UPN.

$userID=‘domain\userid’ 

$ca = Get-spwebapplication -includecentraladministration | where {$_.IsAdministrationWebApplication}

$spsite = $ca.url 

$site = Get-SPSite $spsite

$context = Get-SPServiceContext $site

$upsa = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

$profile = $upsa.GetEnumerator() |Where-Object {$_.AccountName -eq $userID}

$profile["SPS-UserPrincipalName"].Value 

If you found the UPN values are different after it changed form AD, you would need to run UPS full sync to sync the changes to SharePoint.