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 disa

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

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.

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 ;)

I had dbo rights to workflow manager service account on the database WFResourceManagementDB and its solved the problem.

That is great that the workaround worked for you.  I have provided the details to the team and they are working on local repro.

You need to run the same script on the Management DB as well.

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?

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 doesnt 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

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.

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

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

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

Saved my butt!

Thanks!

This worked for me too.   :)

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

i am totally confused any one tell me with clear steps 

• Edited by vlnSharePoint Monday, August 17, 2015 3:27 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.

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!