Friday, April 20, 2018

set Lock State (No Access, ready only, no additions, unlock) of all site collections in a SharePoint farm using powershell

Normally site lock functionality is available at site collection level only and not at farm level or even web application level, I came across a requirement where in I was asked to lock all site collections in farm. Since my farm has more than 20000 site collection it would have been a tedious task if I had to to it manually one by one.

Have created a Powershell script to set the all site collection in a farm to "NO Access" mode.

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue;
Start-SPAssignment -Global;
$webapps= Get-SPWebApplication;
$LockState= "NoAccess"; 
Write-Host "Setting all web applications to $($LockState)..." -ForegroundColor Yellow;
foreach ($webapp in $webapps)
$sites= Get-SPSite -WebApplication $webapp -Limit All;
$sites | ForEach-Object{
Write-Host "Setting site collection $_.Url to $($LockState)..." -ForegroundColor Yellow;
        Set-SPSiteAdministration -LockState $LockState -Identity $_.url;}
Stop-SPAssignment -Global;
Write-Host "Finished!" -ForegroundColor Green;


Below are the valid values for LockState variable 

  • Unlock to unlock the site collection and make it available to users.
  • NoAdditions to prevent users from adding new content to the site collection. Updates and deletions are still allowed.
  • ReadOnly to prevent users from adding, updating, or deleting content.
  • NoAccess to prevent users from accessing the site collection and its content. Users who attempt to access the site receive an error message.

Get Site collection count in a farm

Use the below script to get count of all site collections in a farm.
$AllWA = Get-SPWebapplication;
foreach($wa in $AllWA){$WA.Sites | Measure-Object | Format-List Count};

Thursday, December 7, 2017

Fix:Failed to Sql Query data XEvent collector on SQL server. The error is Object reference not set to an instance of an object.

This is a common error message you will see in ULS logs. The reason for this error are the diagnostics jobs which need Serveradmin/sysadmin role to be granted to Farm admin account. it would make sense to grant farm account server admin role on sql server if we are interested to get the data which these diagnostics jobs collect. But in most of the cases I have found that we would not need these jobs nor we would like to grant farm account sysadmin role on SQL server. This issue common to both SharePoint 2010 and SharePoint 2013.

To get list of diagnostics jobs execute the below powershell command

PS C:\Windows\system32> Get-SPDiagnosticsProvider

Name                                                    Retention  MaxTotalSi Enabled   
----                                                    ---------  ---------- -------   
job-diagnostics-blocking-query-provider                 15         6200000000 False     
job-diagnostics-sql-dmv-provider                        15         1000000... True      
job-diagnostics-uls-provider                            15         1000000... True      
job-diagnostics-performance-counter-sql-provider        15         6200000000 True      
job-diagnostics-performance-counter-wfe-provider        15         1000000... True      
job-diagnostics-event-log-provider                      15         6200000000 True      
job-diagnostics-changed-objects-provider                15         6200000000 True      
job-diagnostics-sql-blocking-report-provider            15         6200000000 True      
Search Health Monitoring - Trace Events                 15         1290000... True      
job-diagnostics-io-intensive-query-provider             15         1000000... True      
job-diagnostics-change-types-provider                   15         6200000000 True      
job-diagnostics-sql-memory-provider                     15         1000000... False     
job-diagnostics-sprequestusage-provider                 15         6200000000 True      
job-diagnostics-sql-deadlock-provider                   15         6200000000 True    
In order to get rid of the error message from ULS logs, either we need to grant farm account sysadmin role on SQL server or disable the diagnostics jobs.

Use the below script to disable the diagnostic jobs.

providers = Get-SPDiagnosticsProvider
foreach($provider in $providers)
   if(($provider.Name -eq "job-diagnostics-sql-blocking-report-provider") 
-or ($provider.Name -eq "job-diagnostics-io-intensive-query-provider") 
-or ($provider.Name -eq "job-diagnostics-sql-deadlock-provider"))
        $provider.IsDisabled = $true;
Now execute the Get-SPDiagnosticsProvider command to make sure that enabled attribute is set to false.

Tuesday, July 4, 2017

Change Top Bar text of Central Administration site.

Use the below powershell commands to change the top bar text of central admin site. I use this text to concur the farm environment, like test, QA or Prod

$ca= Get-SPWebApplication -IncludeCentralAdministration | ?{$_.IsAdministrationWebApplication -eq $true};

$ca.SuiteBarBrandingElementHtml = “<div class=’ms-core-brandingText’>Central Administration- QA</div>“;


Sunday, October 16, 2016

Install SharePoint 2013 with Product key that has number of allowed installation exceeded--SharePoint 2013-AutoSPInstaller product bug(not recommended)

Note-: Please try this at your own risk. this is no means to promote piracy. I hope Microsoft fixes this bug with future patches/installation media.

Recently I had a situation where in customer had already initiated purchase of SharePoint license key as the number of installations had exceeded the number of permitted installation with the existing product key. if we try to use this key in SharePoint installation wizard, it will promptly give us an error message stating the key is invalid. since number of valid/allowed installations is already over.

I normally use AutoSPInstallater for SharePoint installation as I prefer having meaningful database names and also having one single script to install and provision sharePoint services.

So here come the bug part. I used this license key in AutoSPinstaller and SharePoint does not complains about it and proceeds with installation and provisioning of service application.

No issues detected while running sharepoint. After few days we got a new valid product key and we replaced it in farm.

Always please use genuine products as product developers deserve to be paid for hard work which has gone into making these softwares.

This article is just to highlight the product bug which I discovered in SharePoint 2013 and I hope MS will improve SharePoint by fixing this.

Restore Site collection backup from Lower to Higher SharePoint CU version 2013 and vice versa

Recently I had faced an issue where QA and test were on May 16 CU where as Prod was on March 15 CU. With SharePoint 2013, it does not allows to restore site collection backups if both source and destination farms have different SharePoint version like in above case and we will get error when use Restore-SPSite command.

There are two way to solve this issue....

1) Database copy approach 

This approach is pretty simple. you need to take backup of database having site collection you want to restore and restore database in destination SQL server. Mount database with desired web application and upgrade database using powershell.

2) Hack site collection backup file

  • Backup your site collection from Source farm using Backup-SPSite command.
  • Download HXD editor for editing backup file which is in hexadecimal format.
  • Post installing HXD, Run it.
  • Open site collection backup file in above tool
  • Site collection backup file should look something like below...
  • image
  • If we try to find the version, which we got from error message from the log, we will find that it is located in the beginning of the file
  • best things about backup file version is that version is stored only once and that too at beginning of file.
  • change source version to the target in the hex editor now.
  • image
  • If you unsure about target SharePoint version, you can get from central admin-->Upgrade and Migration-->Check product and patch installation status
  • Or you can take site collection backup of any blank site in destination farm and open with HXD to get SP version number.
  • Once source SP version is replaced with destination SP version save the backup file in HXD.
  • Run the Restore-SPSite command again and site collection would be restored without any issues.

Wednesday, August 3, 2016

This page can't be displayed - Turn on TLS 1.0, TLS 1.1, and TLS 1.2.....SharePoint-problems with SSL negotiation.

This is one of the issues I have faced lately where in users used to get error (mentioned in subject) when they tried to browse the SharePoint site hosted over https. In my case we had a two server farm (app and WFE). upon initial investigation we found that whenever the load balancer was forwarding request to WFE, user used to get TSL error, Pages displayed properly when the request was forwarded to app server. Strange things was the TSL protocols were already enabled in user's internet explorer.

IIS settings were same on both server (when I have checked/compared with IISCrypto tool). user request did not even reached the IIS or SharePoint layer as I could not see any log entries in either IIS or SharePoint logs. from IIS console prospective its had same SSL certificate and all settings just looked fine.

Decided to use  WireShark Tool to see the network traffic and to check is the request were reaching server or not. I had modified host file of my laptop for site URL to point it directly to server (which was having issues), bypassing the load balancer.

Important thing which I noticed with Wireshark packet capture was that Client's PC sent request (client hello packet) and server sends ACK packet (acknowledging the the web request). but next packet just after that ACK is a FIN and ACK packet, which indicated the server was gracefully closing/finishing the session. And post that client also acknowledges the FIN flag.

In order to understand why server was setting FIN flag was time since one has to understand SSL negotiation architecture and understand how it internally works. You can follow this link to understand SSL architecture.

So in my case first phase of SSL negotiation (client hello) went successfully. but did not see server hello in wireshark logs. instead saw FIN flag.

So my investigation initiated with 2nd phase where http.sys does reading before server sends out a server hello packet.

Normally if you have a site with SSL binding and an IP assigned to it. there will be a corresponding registry key as well in the registry which Http.sys read to know what SSL cert is assigned to the IIS site. What I noticed was in my case was this registry specific to my site was missing from registry even though I have tried rebinding the SSL certificate to site in IIS. IIS was just not creating the key.

Registry key that HTTP.sys check looks like below format

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\HTTP\Parameters\SslBindingInfo\<IP address:SSLport number>

Finally I had to manually add this key to registry and site started working after this.