Monday, June 19, 2017

Procedure to restore SharePoint 2013 access apps in the same farm from database without impacting other apps

In previous blog, I’ve provided the procedure to restore SharePoint 2013 access apps after deleted in the same farm from database backups. The approach is to overwrite all the four databases including site content, access app, app management service, and secure store services. The issue for that process is it will overwrite all the whole app management and secure store services database. As a result, any new or updated app might be impacted. For example, the newly added access will not be recover after this process.

In this article, I’ll provide a different way to restore SharePoint 2013 site with access apps. However, sicne this involves database insertion, this might not be supported by Microsoft SharePoint support contract. Please contact your Microsoft support before using the approach!!!


Before we explain the procedure to restore SharePoint 2013 site with access apps in the same farm, let’s create a team site with an access app. The site has the following url “http://spsbx15/sites/APP-DR” and the Access App is named “Harry-APP-Testing” as out of box Customers table. Before we delete the access app “Harry-APP-Testing” and start the restore, let's complete the preparation steps. We will need all this information during the restore.

1. Preparation step #1, we will need to get the access apps App Ids from the site collection. You could use the following Powershell.

         $appInstances = Get-SPappinstance -web $siteUrl

        if($appInstances -ne $null)
        { 
                foreach ($eachAppInstance in $appInstances)
                {        
                    $title = $eachAppInstance.Title
                    $version = $eachAppInstance.App.VersionString
                    $AppPrincipalId = $eachAppInstance.AppPrincipalId
                    $Id = $eachAppInstance.Id
                    $Status = $eachAppInstance.Status
                    $AppWebFullUrl = $eachAppInstance.AppWebFullUrl

                    Write-Output "$title; $version; $Id; $AppPrincipalId; $Status; $AppWebFullUrl"| Out-File $reportName -append 
                    Write-Output $eachAppInstance
    
                }
        } 

The result is listed below and please record the App Id.
  • Site Url                 http://spsbx15/sites/APP-DR
  • App Name            Harry-APP-Testing
  • App Id                  ddbe8814-5cdf-48a4-9322-a53cad392c17
  • App Type             Access App

2. Preparation step #2, we will need to get the access apps ReferenceIds, AppInstallationId from the site collection. You could use the following SQL query on the site content DB.

SELECT TOP 1000 [AppInstallationId]
      ,[DatabaseName]
      ,[ReferenceId]
      ,[TargetAppId]
      ,[AppSource]
      ,[SiteId]
  FROM [WSS_Content_APP-DR].[dbo].[AppDatabaseMetadata]

The result is listed below and please record the App ReferenceId. The AppInstallationId,  DatabaseName, and TargetAppId will also be used in other restore steps.
  • AppInstallationId  DDBE8814-5CDF-48A4-9322-A53CAD392C17
  • DatabaseName      db_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • ReferenceId           0C904934-D29E-48A2-893E-C37472CB07AD
  • TargetAppId          AppDBCredentials_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • AppSource            4
  • SiteId                        A44B0A1A-0FF0-4470-9D43-40DB0C6891CE

3. Preparation step #3, we will need to get the access apps secure store entries from the secure store service DB. You could use the following SQL query on the secure store service DB.

SELECT TOP 1000 [ApplicationId]
      ,[PartitionId]
      ,[ApplicationName]
      ,[FriendlyName]
      ,[ApplicationType]
      ,[TicketTimeout]
      ,[ContactEmail]
      ,[CredentialManagementUrl]
FROM [SVC_SecureStore].[dbo].[SSSApplication]
Where PartitionId = '0C904934-D29E-48A2-893E-C37472CB07AD'  

Please note the PartitionId is the ReferenceId form step #2. The result is listed below.

ApplicationId                                 C5FD28F7-41A9-4980-8F94-4EC1D4E478BF
PartitionId                                      0C37852B-34D0-418E-91C6-2AC25AF4BE5B
ApplicationName                           AppDBCredentials_ddbe8814_5cdf_48a4_9322_a53cad392c17
FriendlyName                                Credentials for App instance 'ddbe8814-5cdf-48a4-9322-a53cad392c17'
ApplicationType                             5
TicketTimeout                                NULL
ContactEmail                                  admin@admin.com
CredentialManagementUrl             NULL

4. Preparation step #4, we will need to get the access apps different entries from the app management store service DB. You could use the following SQL query on the secure store service DB.

4.1 Get AM_AppPrincipalAppInstances entries using the following SQL query.

SELECT TOP 1000 [CompositePartitionKey]
      ,[AppInstanceId]
      ,[ProductId]
      ,[AssetId]
      ,[ContentMarket]
      ,[AppSource]
 FROM [SVC_APPS].[dbo].[AM_AppPrincipalAppInstances]
 WHERE AppInstanceId ='DDBE8814-5CDF-48A4-9322-A53CAD392C17'

The AppInstanceId is from the step#2. The result is listed below.

CompositePartitionKey 0x2B85370CD0348E4191C62AC25AF4BE5B0049003A00300049002E0054007C004D0053002E00530050002E0049004E0054007C00370032003700370041003900410032002D0034003000420041002D0034003000450038002D0042003500420033002D004300450038004500360046003900350039003700320042004000390038004500390042004100380039002D0045003100410031002D0034004500330038002D0039003000300037002D00380042004400410042004300320035004400450031004400
AppInstanceId               DDBE8814-5CDF-48A4-9322-A53CAD392C17
ProductId                       C181CBB8-0474-41C3-8C3C-6C86962E497D
AssetId                          NULL
ContentMarket              NULL
AppSource                    4

4.2 Get AM_AppPrincipals entries using the following SQL query.

SELECT TOP 1000 [CompositePartitionKey]
      ,[Title]
      ,[RedirectUrl]
      ,[Realm]
      ,[Flag]
      ,[AppInstanceId]
      ,[DeploymentData]
      ,[DelegateAppPartitionKey]
  FROM [SVC_APPS].[dbo].[AM_AppPrincipals]
 WHERE Title = 'Harry-APP-Testing'

Please note the Title is the "App Name" from step #1. Be aware the same app names in the farm. In this case, you need to compare the CompositePartitionKey with the value from step 4.1. The result is listed below.

CompositePartitionKey 0x2B85370CD0348E4191C62AC25AF4BE5B0049003A00300049002E0054007C004D0053002E00530050002E0049004E0054007C00370032003700370041003900410032002D0034003000420041002D0034003000450038002D0042003500420033002D004300450038004500360046003900350039003700320042004000390038004500390042004100380039002D0045003100410031002D0034004500330038002D0039003000300037002D00380042004400410042004300320035004400450031004400
Title                                             Harry-APP-Testing
RedirectUrl                                 access:AuthRequestComplete
Realm                                          NULL
Flag                                              0
AppInstanceId                           NULL
DeploymentData                      NULL
DelegateAppPartitionKey       NULL


4.3 Get AM_ProductOAuthRegistration entries using the following SQL query.

SELECT TOP 1000 [CompositePartitionKey]
      ,[AppId]
      ,[AppInstanceId]
FROM [SVC_APPS].[dbo].[AM_ProductOAuthRegistration]
WHERE AppInstanceId = 'DDBE8814-5CDF-48A4-9322-A53CAD392C17'

Please note the AppInstanceId is the value from step #2. The result is listed below.

CompositePartitionKey  0x2B85370CD0348E4191C62AC25AF4BE5B01B8CB81C17404C3418C3C6C86962E497D
AppId                      i:0i.t|ms.sp.int|7277a9a2-40ba-40e8-b5b3-ce8e6f95972b@98e9ba89-e1a1-4e38-9007-8bdabc25de1d
AppInstanceId         DDBE8814-5CDF-48A4-9322-A53CAD392C17

Now, we can delete the access app and start to restore the access app along with the site collection. Some steps are similar to we discussed in previous article.

1. Restore the access app logins - see previous blog.
2. Restore the access database
3. Restore the site collection content DB
4. Insert the entry from step #3 to secure store service database
5. Insert multiple entries from step #4 to three app management service database

This way, you should have the access app restored along with site collection in the same farm. At meantime, since we are not overwriting the whole secure store or app management services, instead  inserting entries related to the specific access app, this approach will restore only the access app deleted without impacting other apps!

Since we are query SharePoint databases and insert data into both secure store and app management databases, this might violate Microsoft SharePoint support contract. Be aware NOT to sue approach before consulting with your Microsoft support team.



Thursday, June 15, 2017

Procedure to restore SharePoint 2013 site with access apps in the same farm from database backups

In March 2017, Microsoft announced that they will no longer recommend Access Services for new apps and web databases. When Access Services in SharePoint is first introduced in SharePoint 2013, the mission was to enable both information workers and developers to quickly create data eccentric web applications with little or no programming. Over the last several years it has become clear that the needs of our customers have grown beyond the scope of what Access Services can offer. One of the major issue we are facing is one of our  Access Service App is bigger that could not be packaged as app package. We are not be able to make any changes that could be saved or packaged. The worst case is there is no disaster recover procedure we could  recover the Access Service App along with the site collection.


Although Microsoft is recommending Microsoft PowerApps as alternative that offers a comprehensive set of application building tools, connection to custom web APIs, and a wide array of database options including SharePoint lists, SQL Azure databases, Common Data Service and third-party data sources, there is no clear way to convert existing Access Service App. While we are migrating existing Access Service Apps, we still need to support the current Access Service Apps in production. In this article, I’ll describe the way how you could restore the Access Service Apps from database backups along with the site collections using Microsoft way. You could use app package as access app backup and restore if the app is small enough to be saved as .app package. In different article, I’ll also provide a way that might not be supported by Microsoft but for your reference.

Before we explain the procedure to restore SharePoint 2013 site with access apps in the same farm, let’s create a team site with an access app. The site has the following url “http://spsbx15/sites/APP-DR” and the Access App is named “Harry-APP-Testing” as out of box Customers table. The disaster recover methods are different different scenarios of the site with access app restore.

Here is the summery of the site collection and farm information needed for restore. I'll explain the details how to get this information in the following sessions.

  • Site collection  URL                  http://spsbx15/sites/APP-DR
  • Site collection DB                  WSS_Content_APP-DR
  • Access App                            Harry-APP-Testing
  • Access App DB                      db_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • App manager service DB        SVC_APPS
  • Security Store service DB       SVC_SecureStore
  • Access App logins                    db_ddbe8814_5cdf_48a4_9322_a53cad392c17_dbo 
                                                         db_ddbe8814_5cdf_48a4_9322_a53cad392c17_custom
                                                         db_ddbe8814_5cdf_48a4_9322_a53cad392c17_ExternalWriter

All the SharePoint components related to access app for the site collection and the relationships are illustrated in the below diagram.



1. The first scenario is the site collection content corrupted or deleted, we want to recover the site along with Access App. 

In order to test this scenario, we delete the site collection “http://spsbx15/sites/APP-DR”. Here is the way to restore.
  • Get the site content database WSS_Content_APP-DR from backup
  • Restore the content database to SharePoint SQL database
  • Mount the database using the Powershell

         Mount-SPContentDatabase WSS_Content_APP-DR -DatabaseServer spsbxsql15 -WebApplication        
         http://spsbx15

If the site collection had been just deleted within few hours and the SharePoint daily graduate site deletion timer job has not started (~6AM), you can quickly restore the site using the following steps.

        # Get the site GUID using Powershell. Record the site GUID
        Get-spdeletedsite -webapplication http://spsbx15

        # Restore the site using Powershell by passing the site GUID
        Restore-SPDeletedSite -Identity 610857cb-8414-4a89-8bf3-ad3628f6c86c


2. The second scenario Access App content corrupted, we want to restore the Access App to the same site. Here are the steps.

         Get the Access App database from backup. Please note the naming conversion to the Access App DB
         Restore the Access App database like “db_ddbe8814_5cdf_48a4_9322_a53cad392c17”


3. The third scenario is Access App deleted, we want to restore the Access App to the same site. Here are the steps before and after the Access App deletion.

As daily SharePoint back up process, please back up the following databases and logins.
  • Back up the Access App logins daily using the script provided by Microsoft.
  • Back up the site collection databases
  • Back up the access app databases
  • Back up the app manager service database
  • Back up security store service database
Whence we need to restore the site collection with access app, here is the procedure.
  • Restore the access logins using the script provided by Microsoft. The logins will be recreated with same SID and passwords. The logins are prefixed as "db_" and then access app ID. db_ddbe8814_5cdf_48a4_9322_a53cad392c17_dbo, db_ddbe8814_5cdf_48a4_9322_a53cad392c17_custom, db_ddbe8814_5cdf_48a4_9322_a53cad392c17_ExternalWrite
  • Restore the access app database db_ddbe8814_5cdf_48a4_9322_a53cad392c17
  • Restore the app manager service database SVC_APPS. This can be done when SharePoint is running.
  • Restore the security store database SVC_SecureStore. You need to stop IIS  by IISRESET /stop before the database restore.
  • Restore the site collection database WSS_Content_APP-DR and mount the site again
The last step to restore the site collection is tricky since the database cannot be restored if the current database is still be used. Here is the way to do that.

  • Delete the site collection from CA
  • Remove the deleted site from pool using Powershell
           get-spdeletedsite -webapplication http://spsbx15 | Remove-SPDeletedSite 

  • Run “Gradual Site Deletion” immediately to delete the sites that were marked for deletion 
  • Close any browser hitting the site, close all SQL query using the database
  • Conduct a IISRESET
  • Delete the site collection content database from SQL - You may still need to try few time until all connections dropped to the database
  • Restore the previous site collection content database WSS_Content_APP-DR 
  • Mount the database to SharePoint using Powershell
       Mount-SPContentDatabase WSS_Content_APP-DR -DatabaseServer spsbxsql15 
       -WebApplication http://spsbx15

Now you have the site collection along with working access app from previous back up!

Please note there are potential issues for the third scenario. The restore will overwrite the entire app manager service and security store service!  What will be impacted? Here are the potential risks you need to manage.

  1. If new access apps have been added to some site during the time, these will NOT be recovered. These access app will be orphan on the sites!
  2. If new security store entry has been added or existing security entry has been modified, these will NOT be recovered. You will have previous values!

Since we will disable access app creation, we can eliminate the risk #1. We control the security store entry update and have Powershell script to retrieve the entries, we can eliminate the risk #2. However, if you like my access restore procedure, please be aware the risks.

At this time, you might ask, is there anyway we could recover the site along with access without overwrite the entire app management service and security store service?  The answer is YES! I'll provide the details in another blog. However, that approach will involve SQL database insert to both app management service and security store service databases. This seems to be unsupported by Microsoft.

I'm working with Microsoft to see if there is anyway we can restore app and site without overwrite the app management service and security store service.




Monday, April 17, 2017

How to preserve certain values or features when migration sharepoint content using Sharegate

When document are migrated from one site to another through Sharegate, the timestamp and user (Modified, Modified By, Created, Created By) are not preserved for draft versions. To preserve these, Sharegate provide a Server extension to be installed. The Server Extension allows Sharegate to provide you with additional functionality that might be needed in order to preserve certain values or features as indicated in Sharegate support site.


Here is one simple testing result before and after install the Sharegate Server Extension when migrating the same document. Please note the “Modified By” value will be preserved ONLY after Sharegate server extension installed.

Here is the version history for the original document on source site document library.


Here is the version history for the document on destination site document library when Sharegate server extension not installed. Please note the “Modified By” is not preserved and becomes migration account.


During the migration, Sharegate will prompt the following message.


Here is the version history for the document on destination site document library when Sharegate server extension already installed.


The installation of the Sharegate server extension is simple and no server bounce required.
  1. Download the Extension installer from here.
  2. Copy the Extension installer on your SharePoint web front-end server. If you have more than one Web Front-End server, copy the installer on all of them. 
  3. Launch the Extension installer. It will not cause any downtime on your SharePoint server. It doesn't require a server reboot.

You can verify the installation from GAC as the following screenshot.

Here is the table of the feature that required Sharegate server extension.

Value to preserve Source SharePoint Destination SharePoint Must be installed on
Authors and timestamps Any 2007 Destination
Modified by on Draft Versions Any 2010, 2013, and 2016, or Office 365 (when using normal mode) Destination
Approval status (with authors and timestamps) Any 2007, 2010, 2013, 2016 Destination
Business data (BDC) Any 2007 Destination
Content type OOB (Out-Of-the-Box) workflow configuration 2007 Any Source
Items declared as Records 2010, 2013, 2016, Office 365 2010, 2013, 2016 Destination
Items with a high version number on the first version Any 2007, 2010, 2013, 2016 Destination
List OOB (Out-Of-the-Box) workflow 2007 Foundation (WSS 3.0) Any Source
Nintex workflow constants 2007, 2010, 2013 2010, 2013 Both
Nintex workflow history 2007, 2010, 2013 2007, 2010, 2013 Both
User alerts 2007, 2010, 2013, 2016 2007, 2010, 2013, 2016 Both
Records Enabled All versions of Office 365 or Sharepoint Office 365 (when using normal mode) Both
Features      
Insane Mode 2007, 2010, 2013, 2016, Office 365 2007, 2010, 2013, 2016 Both

You can check all the features that server extension Sharegate required for migration here. You might need to install the server extension on both source and destination SharePoint servers or O365 site.

You can remove the Sharegate server extension from add/remove features.

Now you can enjoy using Sharegate to migrate contents.

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.


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!

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.

$ssrs.Timeout=200000 


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.