Stopping SharePoint Service Instance on the Server

SharePoint Central Administration Portal provides a way to start/stop Services Instance on a particular server. It is accessible under Application Management/Service Applications/Manage Services on Server option. It is all good in the small environment but controlling the Service Instances in the big environment becomes cumbersome. PowerShell to the rescue. Bellow is a code to stop SharePoint Service Instance on the particular server:

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 11/24/2017
#  Description..: Stopping SharePoint Service Instance on the Server
#
#################################################################################################### 
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
$ServerName = "Server Name"
$ServiceType = "Services Application Name" 
$spService = Get-SPServiceInstance | where {$_.TypeName -eq $ServiceType -and $_.Server.name -eq $ServerName } 
$i = 0
if ($spService.Status -eq "Online") {
    Write-Host "Stopping $($ServiceType) on $($ServerName) Server..." -NoNewline
    ##$spService.Unprovision()
    $spService = Stop-SPServiceInstance $spservice.Id -Confirm:$false
    DO {
        write-host -NoNewline "."
        sleep 30
        $spService = Get-SPServiceInstance $spService.Id
        $i++
    } While (($spservice.Status -ne "Disabled") -and ($i -lt 10))
    if ($spservice.Status -eq "Disabled") {
        Write-Host " Success!" -ForegroundColor Green
    } else {
        Write-Host " Failed!" -ForegroundColor Red
    }
}

Services Application Names:

Access Database Service 2010
Access Services
App Management Service
Business Data Connectivity Service
Central Administration
Claims to Windows Token Service
Distributed Cache
Document Conversions Launcher Service
Document Conversions Load Balancer Service
Excel Calculation Services
Lotus Notes Connector
Machine Translation Service
Managed Metadata Web Service
Microsoft SharePoint Foundation Incoming E-Mail
Microsoft SharePoint Foundation Sandboxed Code Service
Microsoft SharePoint Foundation Subscription Settings Service
Microsoft SharePoint Foundation Web Application
Microsoft SharePoint Foundation Workflow Timer Service
PerformancePoint Service
PowerPoint Conversion Service
Request Management
Search Host Controller Service
Search Query and Site Settings Service
Secure Store Service
SharePoint Server Search
SQL Server PowerPivot System Service
SQL Server Reporting Services Service
User Profile Service
User Profile Synchronization Service
Visio Graphics Service
Word Automation Services

 

Service Application Instance Status:

Online
Unprovisioning
Disabled
Provisioning

 

That was another page in the Chronicles of SharePoint Bits, happy scripting!

Event ID 3351: SQL database login for ” on instance ” failed.

In certain environments you will see ‘Event ID 3355: SQL database login for ” on instance ” failed.’ in the event logs.
The errors are related to MOM/SCOM Agent trying to access some information from the SharePoint Configuration database under ‘Local System’ account and gets access denied.

Resolution:
To resolve the issue we need to grant SPShellAdmin access to Local System account to Configuration DB for each server in the farm:

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 11/01/2017
#  Description..: Fixing Event ID 3355: SQL database login for '<Config DB Name>' on instance '<SQL Instance Name>' failed. 
#
#################################################################################################### 
if ((Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin Microsoft.SharePoint.PowerShell
}
Add-PSSnapin Microsoft.SharePoint.Powershell

$domainName = ((gwmi Win32_ComputerSystem).Domain).Split(".")[0]

$Servers = get-spserver | ? { (($_.Role -eq "Application") -or ($_.Role -eq "SingleServer")) }
foreach($Server in $Servers) 
{
    $serverName = $Server.Name
    Get-SPDatabase | where {$_.name -match "config"} | Add-SPShellAdmin -username "$domainName\$serverName$"
}



That was another page in the Chronicles of SharePoint Bits, happy scripting!

Accessing SharePoint online through proxy

The bellow PowerShell script will allow you to access SharePoint Online using PowerShell through proxy:

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 10/6/2017
#  Description..: Connecting to SharePoint Online thru Proxy 
#
#################################################################################################### 
$proxyString = "http://proxyaddress:proxyport"
$proxyUri = new-object System.Uri($proxyString)
[System.Net.WebRequest]::DefaultWebProxy = new-object System.Net.WebProxy ($proxyUri, $true)
$userName ="user@domain" #SPO User
$SecurePWD = read-host -assecurestring "Enter Password for $userName" 

$credential = new-object -typename System.Management.Automation.PSCredential -argumentlist $userNameProxy, $SecurePWD

[System.Net.WebRequest]::DefaultWebProxy.Credentials = $credential

$tenantName="tenantName"

$AdminURI = "https://$($tenantName)-admin.sharepoint.com"

Try {
    Connect-SPOService -Url $AdminURI -credential $credential
    Write-Host "Connected" -ForegroundColor Green
    Disconnect-SPOService
    Write-Host "Disconnected" -ForegroundColor Green
}
catch {
    Write-Host "Not able to connect" -ForegroundColor Red
    $Error 
}

That was another page in the Chronicles of SharePoint Bits, happy scripting!

SharePoint 2013: The case on orphan database

Issue:
Trying to create new site collection generates the following error:
New-SPSite : Object reference not set to an instance of an object.
At line:1 char:
+ New-SPSite -Url “http://company/sites/NewSiteName&#8221; -OwnerAlias “UserID”
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (Microsoft.Share…SPCmdletNewSite:SPCmdletNewSite) [New-SPSite], NullReferenceException
+ FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletNewSite

You still can create a new site collection if you explicitly specify the Content DB name in the New-SPSite command

Also, subsequently if you run SharePoint Product Configuration Wizard fails on step 9 with the following error:
09/14/2017 16:43:43.45 OWSTIMER (0x23E4) 0x178C SharePoint Foundation Upgrade SPHierarchyManager ajyw5 ERROR Attempt to register null pointer at:    at Microsoft.SharePoint.Upgrade.SPHierarchyManager.AddNextLevelObjects(Object current, IEnumerable nextObjects)     at Microsoft.SharePoint.Upgrade.SPHierarchyManager.Grow(SPTree`1 root, Boolean bRecursing, SPDelegateManager delegateManager)     at Microsoft.SharePoint.Upgrade.SPHierarchyManager.Grow(SPTree`1 root, SPDelegateManager delegateManager)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.NeedsUpgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveNeedsUpgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.NeedsUpgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveNeedsUpgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.NeedsUpgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveNeedsUpgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.NeedsUpgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Administration.SPServerProductInfo.DetectLocalUpgradeStatus()     at Microsoft.SharePoint.Administration.SPServerProductInfo.DetectLocalProductVersions(SPProductVersions prodVer)     at Microsoft.SharePoint.Administration.SPServerProductInfo.UpdateProductInfoInDatabase(Guid serverGuid)     at Microsoft.SharePoint.Administration.SPUpgradeJobDefinition.Execute(Guid targetInstanceId)     at Microsoft.SharePoint.Administration.SPAdministrationServiceJobDefinition.ExecuteAdminJob(Guid targetInstanceId)     at Microsoft.SharePoint.Administration.SPTimerJobInvokeInternal.Invoke(SPJobDefinition jd, Guid targetInstanceId, Boolean isTimerService, Int32& result)     at Microsoft.SharePoint.Administration.SPTimerJobInvoke.Invoke(TimerJobExecuteData& data, Int32& result)  . 9c44199e-c716-7014-4bcb-6f3921ecc9f6

Identifying the issue:

Getting content databases shows the $null value as one of the content DBs. Yes, is a case of an orphan DB.

 
Get-SPWebApplication | SELECT ContentDatabases

ContentDatabases
—————-
{$null, Content_DB1, Content_DB2…}

All we need is to get a GUID of the DB and delete it from the configuration, but PowerShell does not show any  $null content db:

$webapp = Get-SPWebApplication http://company
$webapp.ContentDatabases | Select Id, Name
Id Name
—-
7ed114d7-6b90-4fae-8f1a-81ea0fc744a9 Content_DB1
f638999c-9075-4726-aeaa-c4ea6dd6d8f4 Content_DB2
fea346c5-1f47-485f-8959-47ebe3996469 Content_DB3

 

Solution:
To get the GUID of the null database we need to go dipper into the belly of the beast called SharePoint and get that information.
1.  we need to get the GUID of the Web Application with the orphaned database:

 
$webapp = Get-SPWebApplication http://company
$webapp.Id 

2. we need to get the Web Application configuration in XML format directly from SQL Server by running the following query against the Configuration DB:

 
SELECT TOP [Id],[ClassId],[ParentId],[Name],[Status],[Version],cast([Properties] as XML) FROM [SharePoint_Config].[dbo].[Objects] (nolock) where Id = ‘GUID of the Web Application found in Step 1’ 

3. Search for null value in SPContentDatabaseCollection type. The GUID of it is the GUID of an orphaned DB.

4. Back to PowerShell. Delete the Database with the GUID found in step 3 in configuration:

 
$webapp = Get-SPWebApplication -identity http://company
$webapp.ContentDatabases.Delete("GUID found in step 3")

Problem solved.

That was another page in the Chronicles of SharePoint Bits, happy scripting!

Use PowerShell to compile SharePoint 2010 and 2013 Audiences list

Audiences is a feature of User Profile Service Application that allows list or document library items in SharePoint to be targeted to appear only to people who are members of a particular group or audience. An audience can be identified by using SharePoint groups, distribution lists, or security groups or by using a rules-based system based on User Profile Properties.

For more information see the Target content to specific audiences article.

You can change the Audience Compilation Schedule using Central Administration under: Application Management/Manage Service Applications/User Profile Application/People/Schedule Audience Compilation or use PowerShell (tested in SharePoint 2010 and 2013). The PowerShell will also come in handy when you have Compilation Job stock in progress.

The script bellow will find User Profile Properties ID and stop and restart Audiences Compilation Job.

 
#####################################################################################################
# Author.......: David Shvartsman 
# Date.........: 3/13/2017 
# Description..: Use PowerShell to compile SharePoint 2010 and 2013 audience list 
##################################################################################################### 

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
$runjob=[Microsoft.Office.Server.Audience.AudienceJob]::RunAudienceJob($args);
#Get User Profile Service Application Id
$appUPA = Get-SPServiceApplication | Where {$_.TypeName -eq 'User Profile Service Application'}
if ($appUPA) {
    #Stop Audiences Compilation Job
    Audiencejob.exe $appUPA.Id 0
    #Start Audiences Compilation Job
    Audiencejob.exe $appUPA.Id 1
}

That was another page in the Chronicles of SharePoint Bits, happy scripting!

How To: SharePoint 2013 / MS Project Server 2013 Install and Patch Version

msproject2013pogramandfeatures

Looking at Installed software list on the server, it might create a confusion about what is the patch level of your SharePoint 2013 /MS Project Server 2013 installation. That is because Program And Features shows information from the registry: The version of SharePoint 2013 / MS Project Server 2013 that you originally installed. Stefan Goßner has a good article about how to use PowerShell script to display version info for installed SharePoint product and language packs.

The actual level of patching can be found from Get-SPFarm object with the small script:

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 12/13/2016
#  Description..: SharePoint 2013 / MS Project Server 2013 Install and Patch Version
#
####################################################################################################
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

$listApps=Get-WmiObject -Class Win32_Product | Where {$_.Name -like “*SharePoint*”}
$listApps | SELECt Description, IdentifyingNumber, PackageCode, PackageName , Version | Sort -Property Name | Export-csv d:\temp\Output.csv -force  -NoTypeInformation 

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 12/13/2016
#  Description..: SharePoint 2013 / MS Project Server 2013 Install and Patch Version
#
####################################################################################################
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
$ShowComponents = $true
$RegLoc = Get-ChildItem HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall

# Get SharePoint Products and language packs

write-host "Products and Language Packs"
write-host "-------------------------------------------"

$Programs = $RegLoc |
       where-object { $_.PsPath -like "*\Office*" } |
       foreach {Get-ItemProperty $_.PsPath}
$Components = $RegLoc |
       where-object { $_.PsPath -like "*1000-0000000FF1CE}" } |
       foreach {Get-ItemProperty $_.PsPath} 

# output either just the info about Products and Language Packs
# or also for sub components

if ($ShowComponents)
{
       $Programs | foreach {
              $_ | fl  DisplayName, DisplayVersion; 

              $productCodes = $_.ProductCodes;
              $Comp = @() + ($Components |
                     where-object { $_.PSChildName -in $productCodes } |
                     foreach {Get-ItemProperty $_.PsPath});
              $Comp | Sort-Object DisplayName | ft DisplayName, DisplayVersion -Autosize
       }
}
else
{
       $Programs | fl DisplayName, DisplayVersion
}

$ver = (get-spfarm).buildversion
Write-host ("Patch Level: $($ver.Major).$($ver.Minor).$($ver.Build).$($ver.Revision)") 

Combining the 2 scripts together we can get the full picture of software installed and its patch level:
msproject2013patchlevel

To find out version numbers and patch levels of other SharePoint 2013 related product see the following articles:
Checking Office Web Application Server farm version and SSL Certificate
How to find out the version of Workflow Manager components

The list of all SharePoint 2013 CU can be found at SharePoint 2013 Build Numbers page, maintained by Todd O. Klindt

That was another page in the Chronicles of SharePoint Bits, happy scripting!

SharePoint 2013 – Changing Log Retention Period

The number of days to retain change logs can be set with this small script. The default value is 60 days.

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 11/23/2016
#  Description..: Changing Log Retention Period for SharePoint 2013 Web Application
#
#################################################################################################### 
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
$webApps = Get-SPWebApplication
ForEach ($webapp in $webApps) {
    $LogPeriod = New-TimeSpan $(Get-Date) $(Get-Date).AddDays(10)
    $webapp.ChangeLogRetentionPeriod = $LogPeriod
    $webApp.Update()
}

That was another page in the Chronicles of SharePoint Bits, happy scripting!

Problem with 2013 Workflow Instance geting suspended with “There has been an error authenticating the request” massage

From time to time we are having an issues with SharePoint 2013 Workflows going to Suspended stage with the following error:

RequestorId: 647a2bdb-7a39-cb3c-0000-000000000000. Details: An unhandled exception occurred during the execution of the workflow instance. Exception details: System.ApplicationException: HTTP 401
{"error_description":"The server was unable to process the request due to an internal error.  For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the  configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework SDK documentation and inspect the server trace logs."}
{"x-ms-diagnostics":["3001000;reason=\"There has been an error authenticating the request.\";category=\"invalid_client\""],"SPRequestGuid":["647a2bdb-7a39-cb3c-8ffa-98b63f3bf3a3"],"request-id":["647a2bdb-7a39-cb3c-8ffa-98b63f3bf3a3"],"X-FRAME-OPTIONS":["SAMEORIGIN"],"SPRequestDuration":["11"],"SPIisLatency":["0"],"Server":["Microsoft-IIS\/7.5"],"WWW-Authenticate":["Bearer realm=\"043a66d5-9a15-4b10-aa86-217119f4b03a\",client_id=\"00000003-0000-0ff1-ce00-000000000000\",trusted_issuers=\"00000005-0000-0000-c000-000000000000@*,00000003-0000-0ff1-ce00-000000000000@043a66d5-9a15-4b10-aa86-217119f4b03a\"","Negotiate","NTLM"],"X-Powered-By":["ASP.NET"],"MicrosoftSharePointTeamServices":["15.0.0.4771"],"X-Content-Type-Options":["nosniff"],"X-MS-InvokeApp":["1; RequireReadOnly"],"Date":["Mon, 24 Oct 2016 16:20:33 GMT"]}
   at Microsoft.Activities.Hosting.Runtime.Subroutine.SubroutineChild.Execute(CodeActivityContext context)
   at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

We also see the following error in the event log on Workflow Manager servers:

Log Name:      Application
Source:        Microsoft-SharePoint Products-SharePoint Foundation
Date:          10/24/2016 6:09:00 PM
Event ID:      8306
Task Category: Claims Authentication
Level:         Error
Description:   An exception occurred when trying to issue security token: ID3242: The security token could not be authenticated or authorized..

For some reason Security Token Service Application stops issuing tokens or the token gets expired.

Workaround 1:
Recycle SecurityTokenServiceApplicationPool pool in IIS, terminate and resubmit all Suspended workflows. It will solve the immediate problem. For how to find out all 2013 Workflow instances in Suspended Status see the SharePoint 2013 – List of 2013 Workflow Instances (Suspended, Canceled) blog post
Workaround 2:
While is not a permanent solution, it is more systematic workaround. It seems that Claims provider breaks when for some reason or other the App Pool “SecurityTokenServiceApplicationPool”  account logs off unexpectedly. The solution is as suggested in the following blog: A COM+ application may stop working on Windows Server 2008 when the identity user logs off is to modify Local Group Policy setting ‘Do not forcefully unload the user registry at user logoff’ to not forcefully unload the registry and waits until no other processes are using the user registry before it unloads it.

“The policy can be found in the group policy editor (gpedit.msc)
Computer Configuration->Administrative Templates->System-> UserProfiles
Do not forcefully unload the user registry at user logoff

Change the setting from “Not Configured” to “Enabled”, which disables the new User Profile Service feature.

‘DisableForceUnload’ is the value added to the registry

Note issue applies happens on Vista, Windows 7 and Windows 2008 R2.”

That was another page in the Chronicles of SharePoint Bits, happy scripting!

How to find out the version of Workflow Manager components

For Workflow Manager Farm to function correctly all servers in the farm need to have the same version of DLLs installed. The bellow script will show version of Workflow Manager, Microsoft® Service Bus, Windows Fabric, and Workflow Manager Client installed on the server. It will check for both Service Bus version 1.0 and 1.1.

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 10/14/2016
#  Description..: Workflow Manager components versions
#
#################################################################################################### 

$VersionInfo = @() 
if (Test-Path "C:\Program Files\Workflow Manager\1.0\Workflow\Artifacts\Microsoft.Workflow.Service.dll") {
    $VersionInfo += (Get-ChildItem -Path "C:\Program Files\Workflow Manager\1.0\Workflow\Artifacts\Microsoft.Workflow.Service.dll").VersionInfo 
}
if (Test-Path "C:\Program Files\Service Bus\1.1\Microsoft.ServiceBus.dll") {
    $VersionInfo += (Get-ChildItem -Path "C:\Program Files\Service Bus\1.1\Microsoft.ServiceBus.dll").VersionInfo
} elseif (Test-Path "C:\Program Files\Service Bus\1.0\Microsoft.ServiceBus.dll") {
    $VersionInfo += (Get-ChildItem -Path "C:\Program Files\Service Bus\1.0\Microsoft.ServiceBus.dll").VersionInfo
} 
if (Test-path "C:\Program Files\Windows Fabric\bin\FabricHost.exe") {
    $VersionInfo += (Get-ChildItem -Path "C:\Program Files\Windows Fabric\bin\FabricHost.exe").VersionInfo 
}
if (Test-path "C:\Program Files\Reference Assemblies\Microsoft\Workflow Manager\1.0\Microsoft.Workflow.Client.dll") {
    $VersionInfo += (Get-ChildItem -Path "C:\Program Files\Reference Assemblies\Microsoft\Workflow Manager\1.0\Microsoft.Workflow.Client.dll").VersionInfo
}
CLS 
$VersionInfo | FT FileDescription, ProductName, ProductVersion


If you removed Workflow Manager make sure the following all the underlying directories are gone as well. The UnInstaller is infamous for it and the subsequent reinstall will fail.

That was another page in the Chronicles of SharePoint Bits, happy scripting!