Posted on:
Categories: SharePoint;SQL
Description:

Scenario

You need to patch Workflow Manager to CU2/Refresh.

Solution

  1. Download Workflow Manager CU2 - http://support.microsoft.com/kb/2902007
    1. WorkflowManagerClient_x64.msi - Don’t forget this file or you will have lots of fun :)
    2. WorkflowManager-KB2902007-x64-EN.exe
  2. Download Service Bus CU Pre-Requisite - http://support.microsoft.com/kb/2799752
    1. ServiceBus-KB2799752-x64-EN.exe
  3. Install the Service Bus CU
    1. ServiceBus-KB2799752-x64-EN.exe
  4. Install the Workflow Manager CU2
    1. WorkflowManagerClient_x64.msi
    2. WorkflowManager-KB2902007-x64-EN.exe
  5. Fix SQL Permissions
      • After Patching you will notice the following errors in the Event Viewer:
          • Failed SQL command after 1 tries with error '229'. Exception: System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'InsertTrackingAndStatus', database 'PS2013_STG_Services_WFInstanceManagementDB', schema 'dbo'.
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
            at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
            at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
            at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
            at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
            at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)
            at Microsoft.Workflow.Management.ExecuteNonQueryAsyncResult.OnEndExecuteSql(IAsyncResult result)
            at Microsoft.Workflow.Management.ExecuteSqlAsyncResult.EndAsyncResult(IAsyncResult result)
            at Microsoft.Workflow.Common.BackoffRetryAsyncResult.IsolateWithRetry(IAsyncResult result)
            at Microsoft.Workflow.Common.AsyncResult.AsyncCompletionWrapperCallback(IAsyncResult result)
            ClientConnectionId:176fd008-644c-43e5-bd5b-cf383611170e Command Details: SQL Text : InsertTrackingAndStatus
            SQL Parameters :
            > @StatusRecords = Microsoft.Workflow.Management.SqlDataAccessProvider+InstanceStatusValuesCollection
            > @MetadataWithInstanceId =
            > @Variables = Microsoft.Workflow.Management.SqlDataAccessProvider+VariablesCollection
            > @TrackingRecords =
            > @StatusHistoryRecords =
            > @DebugTraceRecords =
            > @RollingStatusHistoryWindowSize = 1000
            > @RollingDebugTraceWindowSize = 5000
              • Failed SQL command after 1 tries with error '229'. Exception: System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'GetInProgressScopeSnapshots', database 'PS2013_STG_Services_WFResourceManagementDB', schema 'dbo'.
                at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
                at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
                at System.Data.SqlClient.SqlDataReader.get_MetaData()
                at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
                at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
                at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod)
                at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
                at System.Data.SqlClient.SqlCommand.EndExecuteReader(IAsyncResult asyncResult)
                at Microsoft.Workflow.Management.ExecuteReaderAsyncResult.OnEndExecuteSql(IAsyncResult result)
                at Microsoft.Workflow.Management.ExecuteSqlAsyncResult.EndAsyncResult(IAsyncResult result)
                at Microsoft.Workflow.Common.BackoffRetryAsyncResult.IsolateWithRetry(IAsyncResult result)
                at Microsoft.Workflow.Common.AsyncResult.AsyncCompletionWrapperCallback(IAsyncResult result)
                ClientConnectionId:09bfa67f-c511-4b2f-8612-f5ad28197157 Command Details: SQL Text : GetInprogressScopeSnapshots
                SQL Parameters :
                > @BatchSize = 20
              • To resolve this, execute the following SQL Query on WF_InstanceManagement_DB and WF_ResourceManagement_DB
                                  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