Intermittent email failure in SharePoint 2010 Workflow

Recently I had a production Workflow issue. Everything works in Dev, QA & UAT but not in production.

In our case, for the site content type we have enabled the retention from information management policy settings. And declared record to start the custom workflow to manage the retention on records.

Problem:

The problem we are having is that we use Information Management Policy timerjob to start the workflow asynchronously and its working for some time and suddenly emails are missing for new file uploads, also not creating the workflow history.

Root Cause:

I was reviewing and comparing the configuration of all the individual SharePoint servers (We have 4 web servers and 4 application servers in this landscape) and I found one inconsistency. One of the component, SharePoint Foundation Workflow Timer Service (SFTS) is enabled on 6 servers and it is disabled on the other two servers. 

Based on my research(see below) this component should be disabled on application servers. When we had 2 web servers and 2 app servers, this service is disabled on application servers correctly. After some time as part of new application Go live, we added 2 additional web servers and 2 application servers to this farm and this service was not disabled on these two additional application servers. To my knowledge the first notification failure happened after we added additional capacity. After stopping this service in newly added 2 APP servers ,the workflow works perfectly.

We have observed workflow behavior for couple of weeks and we haven't seen any issue so far due to stopping the Microsoft SharePoint Foundation Workflow Timer service on app Servers.

There is no error message in the log files that indicates that this is the root cause, but based on various technical forums this is the root cause for intermittent workflow issues.

Summary of the solution:

Analysis:

During processing of delay activity by workflow, Information management policy timer job is scheduled on the servers where the SharePoint Foundation Workflow Timer Service(SFTS) is running. To execute the SFTS job, server(WFE/APP) will try to process the workflow execution and this requires workflow assembly to be available on the server.

So workflow assembly is missing from the server, HOW ?

This service(SFTS) is automatically configured to run on all Web servers in the farm and it is recommended to run on the Web server according to the Topologies for SharePoint Server 2010.

https://www.microsoft.com/en-us/download/details.aspx?id=6096 

When we deploy the WSP solution, workflow assemblies will be copied to those servers which has WFE role(SharePoint foundation web applicant service=True) see below link.

http://msdn.microsoft.com/en-us/library/ms412929.aspx

In my case SFTS service is running on 2 Application servers where WFE role is not running. So to fix the issue I have stopped the SFTS service on App servers.

Retrieve Credentials from Secure Store Service

What is Secure Store Service ?

The Secure Store Service Application (SSS) was added in SharePoint 2010 as a replacement for 2007’s Single Sign On feature.

Secure Store Service is a shared service that provides storage and mapping of credentials such as account names and passwords. It enables you to securely store data that provides credentials required for connecting to external systems and associating those credentials to a specific identity or group of identities.

It is commonly used for access to data for Business Connectivity Services, Excel Service Applications and Visio Service Applications.

https://technet.microsoft.com/en-us/library/ee806889.aspx 

For example, if a user named JOHN has one account on the SharePoint server and another in a CRM application, the Secure Store mechanism enables his CRM credentials to be stored with his user profile in SharePoint Server. As a result, if he uses a Microsoft Business Connectivity Services (BCS) solution in SharePoint Server to obtain data from the CRM application, SharePoint Server looks up the Secure Store Service database on the server and provides his credentials to CRM. In in this manner, JOHN will automatically logs on to the CRM application without having to log onto the CRM application separately.

Don't remember Credentials ?

There is no way to retrieve the credentials from Central Admin that you set up in one of your Secure Store Application entries.

Just run the script below on your SharePoint server and this will list all Secure Store User Names and Passwords.

$serviceCntx = Get-SPServiceContext -Site http://SiteURL

$sssProvider = New-Object Microsoft.Office.SecureStoreService.Server.SecureStoreProvider
$sssProvider.Context = $serviceCntx

$marshal = [System.Runtime.InteropServices.Marshal]

try
{
    $applications = $sssProvider.GetTargetApplications()
    foreach ($application in $applications)
    {
       Write-Output "`n$($application.Name)"
        Write-Output "$('-'*80)"
        try
        {
            $sssCreds = $sssProvider.GetCredentials($application.Name)
            foreach ($sssCred in $sssCreds)
            {
                $ptr = $marshal::SecureStringToBSTR($sssCred.Credential)
                $str = $marshal::PtrToStringBSTR($ptr)

                Write-Output "$($sssCred.CredentialType): $($str)"
            }
        }
        catch
        {
            Write-Output "Error getting credentials!"
        }
        Write-Output "$('-'*80)"
    }
}
catch
{
    Write-Output "Error getting Target Applications."
}

$marshal::ZeroFreeBSTR($ptr)

Refer the below link to Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services

http://www.fabiangwilliams.com/2010/04/16/how-to-create-configure-consume-sharepoint-2010-secure-store-in-business-connectivity-services/

PowerShell Script for Password encrypt, decrypt using secure key

Create/generate a key beforehand in separate script:

Advanced Encryption Standard(AES) encryption only supports 128-bit (16 bytes), 192-bit (24 bytes) or 256-bit key (32 bytes) lengths, so we’ll need to create or generate an appropriate key. Let’s create a byte array of ascending numbers. We will use a 192-bit key, so we’ll need a 24-byte array. 

Generate Secure Key :

generate_securekey.ps1

param
(
 [parameter(Mandatory=$true, Position=0)]
 [string] $File = $null,
 [parameter(Mandatory=$false, Position=1)]
 [string] $length = 24
)
$Key = New-Object Byte[] $length
[Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($Key)
$Key | out-file $File

Once you generate the secure key, you will be able to use the key file to encrypt or decrypt the password from any machine.

Encrypt Password using key:

encrypt_password.ps1

param
(
 [parameter(Mandatory=$true, Position=0)]
 [string] $PW = $null,
 [parameter(Mandatory=$true, Position=1)]
 [string] $SecureKeyFile = $null
)
$Key = Get-Content $SecureKeyFile
$Encrypted_PW = $PW | ConvertTo-SecureString -AsPlainText -Force
Write-Host "Encrypted password with $SecureKeyFile" -ForegroundColor "Cyan"
Write-Host "******************************************" -Foregroundcolor "Yellow"
$Encrypted_PW | ConvertFrom-SecureString -key $Key | Write-Output
Write-Host "******************************************" -Foregroundcolor "Yellow"

Decrypt Password using key:

decrypt_password.ps1

param
(
 [parameter(Mandatory=$true, Position=0)]
 [string] $EncryptedPW = $null,
 [parameter(Mandatory=$true, Position=1)]
 [string] $SecureKeyFile = $null
)
$Key = Get-Content $SecureKeyFile
$account = $env:userdomain + "\" + $env:username
$clearpw = (New-Object -TypeName System.Management.Automation.PSCredential -Argumentlist $account, (ConvertTo-SecureString -String $encryptedPW -key $key)).GetNetworkCredential().Password;
Write-Host "Password decrypted with $SecureKeyFile" -ForegroundColor "Cyan"
Write-Host "******************************************" -Foregroundcolor "Yellow"
$clearpw | write-Output
Write-Host "******************************************" -Foregroundcolor "Yellow"