Tuesday, February 7, 2017

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.
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.

-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!

Backup and restore SharePoint 2013 site collection across different CU versions

We have a project that vendor provided a SharePoint solution in their environment, we would need to back up the site and restore to our environment. We identified that their SharePoint version is different than ours. Here is the quick way we could restore it to different version of SharePoint.
  • Download HXD editor
  • Open the backup file
  • Locate the version number and change it
  • Save the backup

Now you could restore it to different SharePoint version.

If you restore the backup to higher SharePoint version, you could also attache the database, mount the site, and upgrade it.

Monday, February 6, 2017

Tips to managing SharePoint SSRS Reports with Powershell

When you migrate one site collection with SharePoint SSRS Report solution from one environment to another, you will need to update the data source link to reflect the updated data source site URL. It’s extremely time consume to update the .rdl data source link though UI since out site has over 600 .rdl files.  After struggled few days, we are able to utilize the Powershell to automate the process updating the SSRS data source link based on this blog and instruction from Léon Bouquiet. The Powershell snippet is listed below.

$reportserver = "spsbxserver";
$reportServerUri = "http://$($reportserver)/_vti_bin/ReportServer/ReportService2010.asmx?wsdl";
$newDataSourcePath = "http://$($reportserver)/sites/ePlayBook1/SSRS/SPDS.rsds";
$reportFolderPath = "http://$($reportserver)/sites/ePlayBook1/";
#$newDataSourceName = "SPDS"; # No need to change data source name in our case

$ssrs = New-WebServiceProxy -uri $reportServerUri -UseDefaultCredential
$ssrs.Timeout=200000  # Set timeout in case large entries return to cause timeout

$reports = $ssrs.ListChildren($reportFolderPath, $true)
$reports | Where-Object {$_.TypeName -eq "Report"} | ForEach-Object {

       $reportPath = $_.path
       Write-Host "Report: " $reportPath 
       $dataSources = $ssrs.GetItemDataSources($reportPath)
       $dataSources | ForEach-Object {

        Write-Host "Report's existing DataSource Reference ($($_.Name)): $($_.Item.Reference)";

             $proxyNamespace = $_.GetType().Namespace
             $myDataSource = New-Object ("$proxyNamespace.DataSource")         
             $myDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference")
             $myDataSource.Item.Reference = $newDataSourcePath
             #$myDataSource.Name = $newDataSourceName  # No need to change data source name
             $_.item = $myDataSource.Item 
             $ssrs.SetItemDataSources($reportPath, $_)
             Write-Host "Report's DataSource Reference ($($_.Name)): $($_.Item.Reference)";

       Write-Host "-----------------------------------------------------"

We had few issues when we come up the final solution to manage the SSRS Reports with Powershell. Here are few tips that will help you to avoid the issue we encountered.

1. The first issue you might have is the exception for SSRS "Object reference not set to an instance of an object. ".  

The complete ULS log like this below.

01/19/2017 14:19:17.62         w3wp.exe (0x261C) 0x35CC    SQL Server Reporting Services               Report Server Catalog            00000                Unexpected             Throwing Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: , Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.ReportingServices.SharePoint.Objects.RSSPImpUser.get_LoginName()     at Microsoft.ReportingServices.SharePoint.Utilities.CatalogItemUtilities.CreateCatalogItem(RSSPFile file)     at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPFileProperties(RSSPFile file)     at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.InternalFindObjects(ExternalItemPath wssUrl, CatalogItemList& children)     at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.FindObjectsRecursive(ExternalItemPath wssUrl, CatalogItemList& childList, Security secMgr, IPathTranslator pathTranslator, Boolean appendMyReports)     --- End of inner exception stack trace ---;         abafcc9d-e8f3-00e4-e462-39437ade3034

This issue is normally cause by the miss match SSRS add-in on SharePoint server and old SSRS add-in version as described in this KB article.

The solution is to upgrade the SSRS add-in on SharePoint server. In our case with SQL database version as 11.0.6567.0, we have to upgrade SSRS add-in to from version 11.0.3000.0 to SSRS 2012 SP3 with version 11.0.6020.0. Please refer my precious blog on the detailed procedure to upgrade.

2. The second issue you might have is the timeout issue. The error looks like this below.

Exception calling "ListChildren" with "2" argument(s): "The operation has timed out"

The solution is to increase the SSRS proxy timeout. Here is example we added in the Powershell as below.


3. The third issue is the Powershell performance is extremely slow since most SSRS Powershell examples will loop through the whole SharePoint farm sites and lists recursively. You can imaging the time to go through all lists of the far. The Powershell script is like this below.

$reports = $ssrs.ListChildren("/", $true)

The solution is to pass the site collection URL to the ListChildren function. As a result, it will loop though only that one site collection. Here is the updated Powershell script.

$reports = $ssrs.ListChildren("http://mysharepointserver/sites/ePlayBook1/", $true)

If you only need to update the SSRS report on the top level site not recursively, you can also pass the send parameter as $false. There are few other blog you could refer on this topic.

New you could enjoy migrating the site collection from environment to another without have to change all the data source links manually.

Friday, February 3, 2017

SharePoint server reporting service configuration menu missing for report definition files .rdl files

When you started to utilize SQL Server Reporting Services for SharePoint, you could upload report definition file (.rdl) configure it.  Normally you will see the configuration like this below.

However, we run into issues few times the configuration menu missing for .rdl files. After debugging through the issues, we identified few tricks that could resolve this issue. Here are the steps you might check in order to resolve this issue.

1. First you need to verify whether the report definition files uploaded to SharePoint still have extension as “.rdl”.

When you use few IDEs to download the report definition file (.rdl) and modified it, it might change the file extension to xml since they are indeed xml format. The file with .xml extension uploaded to SharePoint will not be recognized as report definition file. As a  result, the reporting service configuration menu will not be displayed.

2. Second you need to verify if Reporting Services Site Collection Features are enabled.

One of the three key features is Report Server Integration Feature site collection feature. If the feature is not enabled, you will not be able to use the SSRS features. As site collection admin, you can verify the feature from setting setting. If you are not site collection admin, you can verify this by adding a content type to your library. You should see the "SQL Server Reporting Services Content Types".

3. The third step you need to verify if the correct version of Reporting Services Add-in for Microsoft SharePoint installed correctly. You would like to install the same version of add-in as SQL server.

The latest version now in 2017 is Microsoft SQL Server 2012 SP3. There is issue for previous SSRS as mentioned in Microsoft blog. You can verify the SSRS version installed under installed programs.

You need to verify if the running service is also in the same version by open the reporting service log file under \15\WebServices\LogFiles directory. The version should look like this.

<Product>Microsoft SQL Server Reporting Services Version 11.0.6020.0</Product>

If the version ins the log is different as installed software, you will need to restart the reporting service.

If you need to upgrade your Reporting Services Add-in for Microsoft SharePoint, you will need to download the installer, uninstall, and then install again. If you run into issue that the installation is always rolling back with the following error. You need to make two changes before reinstall.

  • Make all web.config writable including web.config under reporting service web service
  • Remove all comments like below as described in MSDN blog. This sounds ridiculous but I mean remove ALL comments!

     <!--<remove name="Session" />-->

4. The forth step you need to verify reporting service is running successfully on the server.

Verify if Services on Server is running on SharePoint serer. Check SharePoint central admin and service.

You can view the reporting service web service from IIS and browse the service form browser. The URL for below server is like.

You can also browse the reporting service wdsl for the site you will work on as the following url.

You might need to restart the service in order to bring the service back online.

5. If you still could not resolve the issues by the step #1-4, the nest step is to reinstall or upgrade the SSRS and add-in. 

You should also find the version that is same version as your SQL database. The current version we used is SSRS 2012 SP3. During the installation, you need to select the add-in component.

The reporting service for SharePoint is not very complicated. However, there are limited documentation and examples. Hope this instruction will help.