none
Workflow Manager Cumulative Update (February) error

    Question

  • Hi,

    I've downloaded and installed Workflow Manager 1.0 Cumulative Update and Service Bus 1.0 Cumulative Update. But after installing the updates, I can't access the Library workflow settings page. I'm getting unexpected error. Then after investigating the workflow manager log in event viewer, I've found the following error messages:

    The asynchronous operation has completed with an exception. Exception message is: System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'InsertTrackingAndStatus', database 'DEV_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:2f93882a-8d4c-440d-a3ff-0bd9d1682ad2.

    The issue I've found in my Dev environment with Windows Server 2008 R2 SP 1. This is standalone SharePoint farm with DC, SQL Server and SharePoint all installed in the same server. One workaround I have found that if I add the WF user as dbo to the database as well as  'WFServiceOperators' then the error disappear.


    Thanks,
    Sohel Rana
    http://ranaictiu-technicalblog.blogspot.com

    Tuesday, March 05, 2013 4:25 AM

All replies

  • Sohel, can you expand on "access library Setting"?  We would like to repro your situation on our side so if you could provide us details of your setup and steps to see this error that would be really helpful.  Also, we believe a workaround may be running the following script from SQL Management Studio on Instance Management 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

     
    • Proposed as answer by Mysonemo Wednesday, March 06, 2013 12:50 PM
    Tuesday, March 05, 2013 6:49 PM
  • Hi Chris,

    I download the Workflow Manager update and ran it first accidentally, without installing Service Bus update. Anyway, the Workflow manager update installer failed to install, showing me warning that I need to run Service bus update first. Then I installed Service Bus and after that installed Workflow Manager Update.

    Before Installing the updates, I had a document Library in SharePoint and a custom developed workflow was associated with the document library. Everything was working fine before the update installation.

    After installing the update, I navigated to the document library settings page. Then click 'workflow settings' link in the document library settings page. And then I was getting unexpected error page.

    The Server is Single standalone SharePoint Farm with

    • Windows Server 2008 R2 SP1,
    • SQL Server 2012 Express (Version 11.0.2100.60),
    • SharePoint 2013 Server (Version 15.0.4420.1017)
    • Visual Studio 2012 (Version 11.0.60226.0)
    • Workflow Manager 1.0 (Version 2.0.20922.0)
    • Workflow Manager Client 1.0 (Version 2.0.20922.0)
    • Service Bus 1.0 (Version 2.0.20922.0)

    I've run the sQL script you provided and now the workflow is working again.

    Thanks,
    Sohel Rana
    http://ranaictiu-technicalblog.blogspot.com

    Wednesday, March 06, 2013 3:14 AM
  • Hello,

    Your Workaround its also ok for me, but in the event viewer log for Microsoft-Workflow, i have another error Event ID 18 (65516).

    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 '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:4b7cf6c0-756c-4f9f-8bab-a76c7c71be06 Command Details: SQL Text : GetInprogressScopeSnapshots

    SQL Parameters :

    > @BatchSize = 20

    Thanks for another Workaround ;)


    Mysonemo Consultant SharePoint

    Wednesday, March 06, 2013 12:54 PM
  • I had dbo rights to workflow manager service account on the database WFResourceManagementDB and its solved the problem.

    Mysonemo Consultant SharePoint

    Wednesday, March 06, 2013 1:35 PM
  • That is great that the workaround worked for you.  I have provided the details to the team and they are working on local repro.
    Wednesday, March 06, 2013 4:40 PM
  • You need to run the same script on the Management DB as well.
    Wednesday, March 06, 2013 4:41 PM
  • I am also having this same problem and am getting the EXECUTE error after applying the SB and WF updates. I would rather not have to use a workaround to make this work. Are there any new updates available to address this?
    Thursday, June 27, 2013 4:45 AM
  • Hi Chris,

    We are also experiencing the issue Mysonemo has detailed above, after applying the Feb CU and then executing workaround script that you posted above against the "Instance Management DB" database. We are now having the following errors logged:

    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 'WFResourceManagementDB', schema 'dbo'.

    You stated above that "You need to run the same script on the Management DB as well.".  However, the WFManagementDB doesn’t have a Role called "WFServiceOperators" but instead has a role called "Store.Operators".

    - Is there a patch to the Feb CU available to correct the issues in a supported manner?
    - If there is not a patch, which method would you prefer us use to resolve this:
    a) grant dbo rights
    b) grant the role “Store.Operators” rights to the stored proc
    c) Other?

    Thanks very much

    Thursday, November 21, 2013 10:16 AM
  • I've seem to have encountered the exact same error on our SharePoint farm. We noticed the Microsoft.Workflow.ServiceHost had a near constant CPU utilization of 22%. Also the Microsoft.ServiceBus.Gateway process sat around 11%.

    When I finally found the Workflow event log I noticed the error message Sohel reported.

    I'm gonna try to apply the SQL fix and will report back if it solved the error.

    Wednesday, April 02, 2014 9:00 AM
  • I ran the provided SQL script on our Workflow Manager databases. I commented out the EXEC line and added a 'PRINT @Cmd'. This allowed me to view the actual commands the script would execute. These commands were generated:

    GRANT EXECUTE ON [AddDbUserToRole] TO [WFServiceOperators]
    GRANT EXECUTE ON [AddLoginAndUser] TO [WFServiceOperators]
    GRANT EXECUTE ON [AddUserToServiceAdmins] TO [WFServiceOperators]
    GRANT EXECUTE ON [AddUserToServiceOperators] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[DebugTraceRecordValues] TO [WFServiceOperators]
    GRANT EXECUTE ON [DeleteExpiredInstances] TO [WFServiceOperators]
    GRANT EXECUTE ON [DeleteInstances] TO [WFServiceOperators]
    GRANT EXECUTE ON [DeleteInstancesByScopeId] TO [WFServiceOperators]
    GRANT EXECUTE ON [DeleteSystemInstances] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[EntityIds] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[EntityNames] TO [WFServiceOperators]
    GRANT EXECUTE ON [FormatNTGroupName] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetDebugTraces] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstanceCount] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstanceDebugTraces] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstanceDebugTracesByWorkflowId] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstanceMetadata] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstances] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstancesById] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstancesByWorkflowId] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstanceStatusHistory] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstanceStatusHistoryRecordsByWorkflowId] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstancesToRestore] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetInstanceTelemetryData] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetMappedVariables] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetStatusHistory] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetStillReferencedWorkflows] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetStoreVersion] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetSystemInstances] TO [WFServiceOperators]
    GRANT EXECUTE ON [GetUpdatedInstances] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[Guids] TO [WFServiceOperators]
    GRANT EXECUTE ON [HasPermissionToPerform] TO [WFServiceOperators]
    GRANT EXECUTE ON [InsertInstanceDebugTraceRecords] TO [WFServiceOperators]
    GRANT EXECUTE ON [InsertInstanceStatusHistoryRecords] TO [WFServiceOperators]
    GRANT EXECUTE ON [InsertNotStartedInstanceRecord] TO [WFServiceOperators]
    GRANT EXECUTE ON [InsertTrackingAndStatus] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[InstanceStatusValues] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[MetadataValues] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[MetadataValuesWithInstanceId] TO [WFServiceOperators]
    GRANT EXECUTE ON [OnInstanceEndedWithStatusDetail] TO [WFServiceOperators]
    GRANT EXECUTE ON [OverrideTrackingAndStatus] TO [WFServiceOperators]
    GRANT EXECUTE ON [ResetInstancesToNotStarted] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[StatusHistoryRecordValues] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[TrackingRecordValues] TO [WFServiceOperators]
    GRANT EXECUTE ON [UpdateInstanceWorkflowStatus] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[Variables] TO [WFServiceOperators]
    GRANT CONTROL, REFERENCES ON TYPE::[WorkflowIds] TO [WFServiceOperators]
    

    I ran the script against both 'WFInstanceManagementDB' and 'WFResourceManagementDB'. I also ran it against 'WFManagementDB', but the script didn't generate any GRANT commands for that database.

    So I enabled the EXEC command and reran the script against both databases. Almost immediately I could see a drop in CPU utilization on our workflow server. Also the errors disappeared from the Event Log. To be safe I also rebooted the server.

    This seems to have fixed our problem. I'd still like to have an official KB article or update for this problem, though.

    • Edited by LZandman Wednesday, April 02, 2014 10:09 AM
    Wednesday, April 02, 2014 10:08 AM
  • So - it is over a year later...do we have a fix for this yet?  What was the outcome?  The cumulative update 1.0 still being distributed on the download site is still the same broken version from a year ago.

    I am able to reproduce this time and again...and effictively granting full access to the database when it is likely not required is not what I consider a viable production outcome - who knows what vulnerabilities are being introduced.

    Do we have anything official?

    Regards,

    Greg.

    • Proposed as answer by GregAndo1 Tuesday, April 08, 2014 5:30 AM
    • Unproposed as answer by GregAndo1 Tuesday, April 08, 2014 5:30 AM
    Monday, April 07, 2014 11:40 PM
  • Okay, so after a lot of further investigation, the script supplied my Mysonemo seems to be all that is required.

    It needs to be run against two databases: WFResourceManagementDB and WFInstanceManagementDB.

    I found that if this script was to be run against the database(s) before the cumulative update, the resultant permissions are an exact match to the pre-updated permissions.

    I then ran the cumulative updates for Service Bus and Workflow.  Obviously everything broke.

    I then found that running the script against the upgraded (and at that point broken) database(s) resulted in new permissions being added.  I have summarised them.

    WFInstanceManagementDB - 3 additional permissions being:
    - InsertTrackingAndStatus (Stored procedure. Permission is lost after the SP is dropped and re-created during the upgrade process)
    - GetInstances (Stored procedure. Permission is lost after the SP is dropped and re-created during the upgrade process)
    - EntityNames (Workflow User Defined Table or WFUDT - a new object created during the upgrade process)

    WFResourceManagementDB - 11 additional permissions, all stored procedures added during the upgrade process:
    -ApplyScopeSnapshot
    -CaptureScopeSnapshot
    -CleanupScopeSnapshots
    -DeleteScopeSnapshot
    -GetInProgressScopeSnapshots
    -GetOriginalWorkflowsToDeleteRules
    -GetScopeSnapshot
    -GetWorkflowSnapshotsToAddRules
    -InsertScopeSnapshot
    -UpdateApplyScopeSnapshotStatus
    -UpdateCaptureScopeSnapshotStatus

    As a result of this information, I am going to utilise this script on both databases going forward.  My expectation is that the script was likely used as part of the original deployment of Workflow, and should have been run again as part of the upgrade process but has either been left out or lazily ignored as it would require SQL database permissions in excess of what is normally available to Service Bus / Workflow when running day to day.


    • Proposed as answer by GregAndo1 Tuesday, April 08, 2014 5:45 AM
    • Edited by GregAndo1 Tuesday, April 08, 2014 5:48 AM
    • Unproposed as answer by Sohel RanaMVP Tuesday, April 08, 2014 7:19 AM
    Tuesday, April 08, 2014 5:44 AM
  • Saved my butt!


    mike

    Tuesday, September 09, 2014 1:52 PM
  • Thanks!
    Wednesday, October 01, 2014 2:17 PM
  • This worked for me too.   :)

    I applied it against the Instance Management Database as instructed...and that is all it took.


    Steve Hahn


    • Edited by Steven Hahn Thursday, August 13, 2015 7:05 PM
    Thursday, August 13, 2015 7:04 PM
  • i am totally confused any one tell me with clear steps 
    Monday, August 17, 2015 2:12 PM
  • i executed lzandMAn script 

    script is executed on "WRFInstanceManagementDB"

    but failed to execute on "WFResourceManagementDB"  and "WFManagementDB"  ther error was 


    Cannot find the object 'ABCD', because it does not exist or you do not have permission.

    Monday, August 17, 2015 3:26 PM
  • I was having the exact same problem on a new setup--a Windows Server 2012 R2 server running SharePoint 2013 SP1, Service Bus 1.0 Cumulative Update 1, and Workflow Manager CU2 to get to Refresh 1.0.

    This script worked perfectly!

    Friday, September 04, 2015 4:14 PM