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.

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.




Tuesday, January 31, 2017

Utilize Visual Studio 2015 with GitHub extension to improve development efficiency

After our source repository migrated to GitHub, SharePoint developers started to use either Git window commends or SourceTree to manage the source code while developing projects inside Visual studio.

The major issue for Visual studio will not be able to identify which branch you are and difficult to switch branches. Developers had hard time to jump around different tools during the development phase and merge release phase. Since Visual studio 2015 has much better GitHub extension, my goal is to education all developers to utilize the Visual studio 2015 with GitHub extension to manage the day to day development for GitHub projects. We will cover the following topics.
  • Install GitHub extension
  • Create local project and add to location GitHub
  • Create Github new project and publish local project to remote GitHub
  • Check whcih branch you are working on
  • Create local branch and sync to remote Github
  • Verify local changes and push local change code to remote
  • Tog between branches
  • Merge branches
  • Clone project and different branches to local
  • Pull changes from remote GitHub to local
  • Checkout from remote GitHub branch

1. Install GitHub extension.
If you have Visual Studio 2015 already installed, you could install it two different ways.
  • Install from GitHub extension for Visual Studio and install separately
  • Click Visual Studio 2015 Tools -> Extensions and Updates. Search Github and install “Github Extension for  Visual Studio”

If you have not install Visual Studio 2015, you can select Visual Studio custom installation and select GitHub component as in this blog.

2. Create local project and add to local GitHub
Create a SharePoint Ad-in SharePoint hosted demo project named “SharePointGitHubDemo”. Right click the project and click “Add Solution to  Source Control …”
You will find the files inside Visual Studio will have an locker icon on and local .git folder created to complete the local GitHub configuration.

In Team Explore view, you can also see local Git repository project listed.





3. Create Github new project and publish local project to remote GitHub
In Team Explore view, Click “Connect…” and then “GitHub Enterprise”. Under GitHub user name, password, and the GitHub Enterprise server url such as  “https://github.mycompany.com/”. You will connect to the remote Github.





Make some changes to some files and right click the "commit" from project explore on the file. You will be redirect to change page and “Commit All and Push” will push all changes to Github if you have the remote Github project created in advance. Otherwise, click “Sync” and then “Publish to GitHub”. Fill in the project name, description, and the organize name of the project. Click “Publish” button. New GitHub project will be created and code will be pushed.

4. Check which branch you are working on.
In Team Explore view, click “Home” icon and then “Branches”. The branch you are on is the one highlighted. The example below shows you are working on "develop" branch.



5. Create local branch and sync to remote Github
In Team Explore view, click “Home” icon and then “Branches”. All the branches will be displayed. Right click the branch you would  like to branch out like “master” branch and select “New local branch from”. Enter the name of the branch and create. The new local like “newBranch” branch will be created.

Update some files and then click “Sync”. Then the “Push” under “Outgoing Commits”. You may need to click “Publish” if this is the first time to commit to remove. The develop branch changes from local will be pushed to remote “newBranch” branch.


After the sync, the icon on the file will changed to "Checked in".

7. Tog between branches
In Team Explore view, click “Home” icon and then “Branches”. Double click the branch you like to go. The branch will be highlighted.

8. Merge branches
In Team Explore view, click “Home” icon and then “Branches”. Click the "merge" link. You can select the branch you like to merge into the current branch. In this example, we will merge "develop4" branch into "master".



9. Clone remote GitHub project and different branches to local
In Team Explore view, click “Home” icon and then “Manage Connections” icon. Click "Clone" under "Local Git Repositories". Enter the Github project URL and local GitHub project location. Click "clone" and you will have the project cloned in local.


The default branch on the local will be master branch. You will need to right click project and "New branch from..." and then select the branch from GitHub you like to create to your local. You can pull all different branches you are interested in to your local.

10. Pull changes from remote GitHub to local
After you clone the local branches, you might want to make sure you have the latest code. You can do this in Team Explore view, click “Home” icon and then "Sync". Click "Pull" to pull all changes from Github to local. 

Click "Home" icon and double click the project at the bottom. The project will be open in "Solution" explore from local.

11. Checkout file change history
In "Solution Explore" view, right click the file and then "View history". You can see the change history.



At this point, you should be able to use Visual Studio for your development and integrate with GitHub without leave Visual Studio. We can also use Visual Studio to ingrate with "Team Services' for CI and CD.

One action from GitHub that is normally we use at end of the development phase is to add release tag. I'm not able to find how to do it directly from GitHub extension at this time. You can find other Visual Studio 2015 update 3 new features here.