Recently we did a CRM Organization import where the SQL Database dump was provided by the client. Restoring the database and Importing it into CRM happened smoothly, but when we tried to access the new organization 404 – Not Found’ error was displayed.
Restarted the machine as the database restoration and organization import took considerable time and CPU/Memory. This time got an error as shown below:
ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.
Checked the Microsoft Asynchronous Service, as it misbehaves some times. It is running normally, but restarted it just for the heck of it, but still got the same error.
Checked the Event Viewer for the cause and found the below:
error while processing organization 881538ba-5ae6-df11-a912-7071bc76bf3f. Exception: System.Data.SqlClient.SqlException: The SELECT permission was denied on the object ‘AsyncOperationBase’, database ‘****_MSCRM’, schema ‘dbo’.
The UPDATE permission was denied on the object ‘AsyncOperationBase’, database ‘****_MSCRM’, schema ‘dbo’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command)
at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate)
at Microsoft.Crm.Asynchronous.DataAccessSharedBase.ExecuteSqlCommand(IDbCommand command, Nullable`1 expectedAffectedRecords)
at Microsoft.Crm.Asynchronous.QueueManager.b__1(IQueueDataAccess queueDataAccess)
at Microsoft.Crm.Asynchronous.QueueManager.ExecuteForAllOrganizations(OrganizationQueueDataAccessHandler handler)
Above got us thinking and we found that the imported database came from a system that uses an alternative service account for CRM / Application Pool. Somehow the default user (network\service) was stripped on the ***_MSCRM database.
So opened the ORGANIZATION_MSCRM database in SQL Manager,
Treeviewed to Security -> Users
Then opened the database user NT AUTHORITY\NETWORK SERVICE and checked role member dbowner.
Reset the IIS and voila the problem is solved and we were able to access the imported CRM organization.