Workflow Manager cumulative update (February) error

After installing workflow manager cumulative update in February 2013, the workflow manager stopped working. When I was trying to access workflow settings in the list/library I was getting unexpected exception. Then I found the following error in the log file:

The EXECUTE permission was denied on the object 'InsertTrackingAndStatus'

To fond out how to enable and view the workflow manager log file, please follow the technet guide at: http://technet.microsoft.com/en-us/library/jj193526.aspx. From the error message it seems the problem is related to database permission related. So connected to the database (using SQL management studio) and found out the workflow manager windows service user's (under whose credentila's the workflow service is running) security settings. The original permission I found was "WFServiceOperators" and then I added dbo permission to the user also.  Then I restarted the SQL service and it all started working. I know this is not the best solution but for time being the hack solved the problem in my Dev.

Update From Workflow Team

I've posted the bug in Workflow forum: http://social.msdn.microsoft.com/Forums/en-US/wflmgr/thread/054d2a58-8847-4a6a-b1ab-05a79f49fe65  . As described in the forum, you need to run the following script against the database:

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'WFServiceOperators' AND type = 'R')
BEGIN
               -- Grant all permissions of stored procedures and tables
               DECLARE @ObjectName sysname, @ObjectType char(20), @Cmd varchar(300)
               DECLARE ObjectCursor CURSOR LOCAL FAST_FORWARD
               FOR SELECT name, type FROM sys.objects UNION SELECT name, 'WFUDT' FROM sys.types WHERE is_user_defined = 1

               OPEN ObjectCursor
               FETCH ObjectCursor INTO @ObjectName, @ObjectType
               WHILE (@@fetch_status <> -1)
               BEGIN                             
                              SET @Cmd =
                                CASE @ObjectType
                                              WHEN 'P' THEN N'GRANT EXECUTE ON [' + @ObjectName + N'] TO [WFServiceOperators]' 
                                              WHEN 'WFUDT' THEN N'GRANT CONTROL, REFERENCES ON TYPE::[' + @ObjectName + N'] TO [WFServiceOperators]' 
                                              ELSE ''
                                END

                              IF @Cmd <> ''
                              BEGIN
                                             EXEC(@Cmd)
                              END
                              FETCH ObjectCursor INTO @ObjectName, @ObjectType
               END
               CLOSE ObjectCursor
               DEALLOCATE ObjectCursor
END
GO
 

But still if the above script doesn't solve your problem, you can set the workflow user as dbo in the database

SharePoint 2013: Workflow Manager Installation and Configuration

SharePoint 2013 introduced a new way of workflow management. Now workflow can be hosted in separate server – so scalable and reduces load on SharePoint Web Front End server. However this introduce a new level of complexity that we need to be aware of.

 

Introduction

To use a windows server as workflow manager you need to install/configure Workflow Manager in the windows server. Workflow manager is a new kind of application server that host/manage workflow execution. SharePoint 2013 workflow is based on .net framework 4.0. Workflow Manager is designed with Window Azure hosting in mind, but now it supports on-premise installation. If organizations now want to host the workflow on-premise they can still use Workflow Manager but in future if they want to move to Windows Azure hosting for their workflow, the migration will be smoother with this Workflow Manager.

 

You should Know

Before start installing/developing SharePoint Manager you need to know few points:

• You should not use SharePoint ‘system account’ to test workflow. If you use ‘system account’ to develop/run workflow, the workflow will fail to run.
• You need to make sure User Profile Service (UPS) is running and the user who is running workflow has profile in UPS. Workflow Manager use UPS under the hood.
• Make sure App Management Service is created and running. You don’t need to configure SharePoint 2013 App settings in the server, just creating App Management service (with proxy) will do.
• SharePoint 2013 workflow is declarative – means you can only define workflow in XML. You can’t write any C# code inside workflow as you used to do before. All your custom logic should be put outside of SharePoint, inside WCF Service. Then you will call the service from workflow to implement your custom logic in code.
• To register workflow Server with SharePoint, a SharePoint site collection URL is provided (see the section Register Workflow Service with SharePoint later in the post). Apparently it seems, each and every site collection need to be registered with workflow server. But it’s not, registering a single SharePoint site  collection, will enable workflow manager for all SharePoint web applications/site collections.

 

Install/Configure Workflow Manager

The first step in workflow setup/configuration is to install workflow manager in the workflow server (either Web Front End or a separate server). To install the workflow Manager download it from Microsoft Site or alternatively you can download it from Web Platform Installer. The installation contains few components:

• Workflow Manager: This is the host that runs workflows.
• Workflow Manager Client: It contains the API to allow clients to communicate with Workflow host.
• Workflow Tool: It needs to be installed in the development server to develop SharePoint 2013 workflow. It supports the workflow development in Visual Studio 2012.

Workflow Manager client needs to be installed in every SharePoint WFE server.

After installing Workflow Manger, you can configure workflow manager with Workflow Manager Configuration Wizard. The configuration involves two steps: Workflow Manager Configuration and Service Bus Configuration.

• Workflow Manger Configuration (first step in the wizard) is the configuration related to work host server
• Service Bus configuration (second step in the wizard): Service Bus is used to manage communication between Workflow Server and it’s client (so, SharePoint). Service Bus queues the income request to workflow manage, provide REST interface for Workflow Manager etc.

In workflow configuration wizard don’t use any admin or SharePoint setup user, create a new service user for workflow and use that user:

Figure 1: Workflow Manager Service Account

 

If you want SharePoint Server to communicate with Workflow Server over HTTP, you can select the option shown below. But please make sure this is secure in your case. For public site, this might not be secure but in case of Local Intranet with firewall, this might be secure.

Figure 2: Communication over HTTP

 

If you want to use the same service account (as well as auto generated key for certificate), you can use so as shown below:

Figure 3: Same service account and certificate key is used for both Workflow Manager and Service Bus Configuration. 

In the final step of the workflow configuration wizard you can generate PowerShell script that you can reuse across different SharePoint Farms.

 

Register Workflow Service with SharePoint

Once you have installed/configured Workflow Server, you need to register the workflow service to SharePoint Server. The registration depends on how the SharePoint and Workflow server is connected to each other. You can find more details at technet site. The workflow manager creates an HTTPS endpoint at port 12291 and HTTP port at 12290. If you use HTTP for communication you need to provide ‘AllowOAuthHttp’ switch in the PowerShell command. The PowerShell command looks like below:

Communication over HTTP

Register-SPWorkflowService –SPSite http://sharepointsite –WorkflowHostUri http://workflowhost:12291 –AllowOAuthHttp

 

Communication over HTTPS

Register-SPWorkflowService –SPSite http://sharepointsite –WorkflowHostUri https://workflowhost:12290

 

PowerShell Script to Install/Configure Workflow Manager

I have modified the wizard-generated PowerShell script a bit to make it more reusable. The Script reads configuration values from xml file and apply the configuration. The script uses auto-generate key for certificate. Also the database name are hard-coded in the script, but you can add prefixes (like dev, test, prod) to the database from xml file. The script also configure App Management Service, if the service is not already created. The sample PowerShell Script is provided below:

#Get current user full login name
$CurrentUserLoginName=[Environment]::UserName + '@' + [Environment]::UserDomainName;
#Get current server fully qualified domain name
$HostFQDN="$env:computername.$env:userdnsdomain";

#Load SharePoint Snapin
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null ){  
    Add-PsSnapin Microsoft.SharePoint.PowerShell
}

#Get DB Connection String
function GetDBConnectionString([string]$connectionStringFormat, [string]$dbPrefix, [string]$dbName){
    if($dbPrefix -ne ""){
        $dbFullName=$(GetDBName $dbPrefix $dbName);
        return [string]::Format($connectionStringFormat,$dbFullName);
        }
    else {
        return $dbName;
    }
}

#Add Dev, Test etc. environment prefix, if needed
function GetDBName([string]$dbPrefix,[string]$dbName){
    if(($dbPrefix) -and ($dbPrefix -ne "")){
        return $dbPrefix + "_" + $dbName;
    }
    return $dbName;
}

#Get current Script directory
function Get-ScriptDirectory
{
  $Invocation = (Get-Variable MyInvocation -Scope 1).Value
  Split-Path $Invocation.MyCommand.Path
}

function ConfigureWFManager([string]$settingsFile){
    [xml]$wfsettings = Get-Content $settingsFile
    $settings=$wfsettings.Settings;
    $SharePointSiteUrl=$settings.SiteUrl;
    $dbPrefix=$settings.DBPrefix;
    $CertificateKey=$settings.CertificationKey;
    $databaseServer=$settings.DBServer;
    $ConnectionStringFormat="Data Source=$databaseServer;Initial Catalog={0};Integrated Security=True;Encrypt=False";
    $RunAsAccount=$settings.WFManagerRunAsUser;
    $RunAsPasswordPlain=$settings.WFManagerRunAsPassword
    $WorkflowNamespace=$settings.WorkflowNamespace;
    if(ShouldIProvision($settings.AppManagementService))
    {
        ProvisionAppManagementService($settings);
    }

    # To be run in Workflow Manager PowerShell console that has both Workflow Manager and Service Bus installed.
    # Create new Service Bus Farm
    $SBCertificateAutoGenerationKey = ConvertTo-SecureString -AsPlainText  -Force  -String $CertificateKey -Verbose;

    New-SBFarm -SBFarmDBConnectionString $(GetDBConnectionString $connectionStringFormat $dbPrefix 'SBManagementDB')  -InternalPortRangeStart 9000 -TcpPort 9354 -MessageBrokerPort 9356 -RunAsAccount $RunAsAccount -AdminGroup 'BUILTIN\Administrators' -GatewayDBConnectionString $(GetDBConnectionString $connectionStringFormat $dbPrefix 'SBGatewayDB') -CertificateAutoGenerationKey $SBCertificateAutoGenerationKey -MessageContainerDBConnectionString $(GetDBConnectionString $connectionStringFormat $dbPrefix 'SBMessageContainerDB') -Verbose;

    # To be run in Workflow Manager PowerShell console that has both Workflow Manager and Service Bus installed.

    # Create new Workflow Farm
    $WFCertAutoGenerationKey = ConvertTo-SecureString -AsPlainText  -Force  -String $CertificateKey -Verbose;


    New-WFFarm -WFFarmDBConnectionString $(GetDBConnectionString $connectionStringFormat $dbPrefix 'WFManagementDB') -RunAsAccount $RunAsAccount -AdminGroup 'BUILTIN\Administrators' -HttpsPort 12290 -HttpPort 12291 -InstanceDBConnectionString $(GetDBConnectionString $connectionStringFormat $dbPrefix 'WFInstanceManagementDB') -ResourceDBConnectionString $(GetDBConnectionString $connectionStringFormat $dbPrefix 'WFResourceManagementDB') -CertificateAutoGenerationKey $WFCertAutoGenerationKey -Verbose;

    # Add Service Bus Host
    $SBRunAsPassword = ConvertTo-SecureString -AsPlainText  -Force  -String $RunAsPasswordPlain -Verbose;


    Add-SBHost -SBFarmDBConnectionString $(GetDBConnectionString $connectionStringFormat $dbPrefix 'SBManagementDB') -RunAsPassword $SBRunAsPassword -EnableFirewallRules $true -CertificateAutoGenerationKey $SBCertificateAutoGenerationKey -Verbose;

    Try
    {
        # Create new Servie Bus Namespace
        New-SBNamespace -Name $WorkflowNamespace -AddressingScheme 'Path' -ManageUsers $RunAsAccount,$CurrentUserLoginName -Verbose;

        Start-Sleep -s 90
    }
    Catch [system.InvalidOperationException]
    {
    }

    # Get Service Bus Client Configuration
    $SBClientConfiguration = Get-SBClientConfiguration -Namespaces $WorkflowNamespace -Verbose;

    # Add Workflow Host
    $WFRunAsPassword = ConvertTo-SecureString -AsPlainText  -Force  -String $RunAsPasswordPlain -Verbose;


    Add-WFHost -WFFarmDBConnectionString $(GetDBConnectionString $connectionStringFormat $dbPrefix 'WFManagementDB') -RunAsPassword $WFRunAsPassword -EnableFirewallRules $true -SBClientConfiguration $SBClientConfiguration -EnableHttpPort  -CertificateAutoGenerationKey $WFCertAutoGenerationKey -Verbose;

    Write-Host "Registering workflow host (HTTP) to site: $SharePointSiteUrl";
    Register-SPWorkflowService –SPSite $SharePointSiteUrl –WorkflowHostUri $("http://$HostFQDN" + ":12291") –AllowOAuthHttp
}

function ProvisionAppManagementService([System.Xml.XmlNode] $settings){

    $appManagementServices=Get-SPServiceApplication | Where-Object { $_.GetType().ToString() -eq "Microsoft.SharePoint.AppManagement.AppManagementServiceApplication"}
     If($appManagementServices -ne $null)
     {
         Write-Host "An App Managemetn service is already running. Returning.." -ForegroundColor Yellow
        return;
     }    
    
    Write-Host "Provisioning App Management Service";
    $appManagementService=$settings.AppManagementService;
    $appPool=$(GetAppPool $appManagementService)
    $dbName=$(GetDBName $settings.DBPrefix $appManagementService.DBName);
    $appAppSvc = New-SPAppManagementServiceApplication -ApplicationPool $appPool -Name $appManagementService.Name -DatabaseName $dbName
    New-SPAppManagementServiceApplicationProxy -ServiceApplication $appAppSvc
}

function GetAppPool([System.Xml.XmlNode] $appManagementService){
    $pool = Get-SPServiceApplicationPool -Identity $AppManagementService.AppPoolName -ErrorVariable err -ErrorAction SilentlyContinue
    If ($err) {
        # The application pool does not exist so create.
        Write-Host -ForegroundColor White " - Getting $($appManagementService.ManagedAccountUserName) account for application pool..."
        $managedAccount = (Get-SPManagedAccount -Identity $appManagementService.ManagedAccountUserName -ErrorVariable err -ErrorAction SilentlyContinue)
        If ($err) {
            If (($appManagementService.ManagedAccountPassword -ne "") -and ($appManagementService.ManagedAccountPassword -ne $null)) 
            {
                $appPoolConfigPWD = (ConvertTo-SecureString $appManagementService.ManagedAccountPassword -AsPlainText -force)
                $accountCred = New-Object System.Management.Automation.PsCredential $appManagementService.ManagedAccountUserName,$appPoolConfigPWD
            }
            Else
            {
                $accountCred = Get-Credential $appManagementService.ManagedAccountUserName
            }
            $managedAccount = New-SPManagedAccount -Credential $accountCred
        }
        Write-Host -ForegroundColor White " - Creating applicatoin pool $($appManagementService.AppPoolName)..."
        $pool = New-SPServiceApplicationPool -Name $appManagementService.AppPoolName -Account $managedAccount
    }
    return $pool;
}

Function ShouldIProvision([System.Xml.XmlNode] $node)
{
    If (!$node) {Return $false} # In case the node doesn't exist in the XML file
    # Allow for comma- or space-delimited list of server names in Provision or Start attribute
    If ($node.GetAttribute("Provision")) {$v = $node.GetAttribute("Provision").Replace(","," ")}
    ElseIf ($node.GetAttribute("Start")) {$v = $node.GetAttribute("Start").Replace(","," ")}
    ElseIf ($node.GetAttribute("Install")) {$v = $node.GetAttribute("Install").Replace(","," ")}
    If ($v -eq $true) { Return $true; }
    Return $false;
}

Write-Host "Configuring WF Manager"
$location=Get-ScriptDirectory
ConfigureWFManager "$location\WFFarmSettings.xml"

Code Snippet: PowerShell Script to configure Workflow Manager

The following XML file provides the input settings for the above PowerShell script (named as WFFarmSettings.xml in the above PowerShell script). Though you will use a site collection to register the workflow and SharePoint communication, I’ve found that workflow work for all others site collections/web application in the SharePoint Server.

<Settings>
  <SiteUrl>http://siteulr</SiteUrl>
  
  <!--Delete DBPrefix tag, if you don't want any prefix-->
  <DBPrefix>DEV</DBPrefix>
  
  <!--Key used to generate certificates-->
  <CertificationKey>CertificationKey</CertificationKey>
  
  <!--Database server name, database names are hardcoded in powershell-->
  <DBServer>DBServer</DBServer>
  
  <!--Format should be USERNAME@DOMAIN-->
  <WFManagerRunAsUser>user@domain</WFManagerRunAsUser>
  <WFManagerRunAsPassword>Password</WFManagerRunAsPassword>

  <!--dot (.) not allowed-->
  <WorkflowNamespace>WorkflowNamespace</WorkflowNamespace>
  
  <!--To work with workflow, app management service need to be provisioned-->
  <AppManagementService Provision="true">
    <Name>App Management Service Application</Name>
    <DBName>AppManagementServiceDB</DBName>
    <!--If managed account already exists with the same name, the existing one will be used-->
    <ManagedAccountUserName></ManagedAccountUserName>
    <ManagedAccountPassword></ManagedAccountPassword>
    <AppPoolName>App Management Service App Pool</AppPoolName>
  </AppManagementService>
</Settings>