Dynamics Community Forum Thread Details

Management reporter - problems to regenerate datamart

Hi,

I´m doing the process to regenerate datamart, but I getting problems with the application service and I can not complete the process, in the Implementation log I found the next... I can start the service but then it fails and can not complete the configuration.

24/05/2021 03:10:01 p.m. - Error durante la comprobación de compañías existentes en el servicio de aplicación de Management Reporter. Información adicional: Microsoft.Dynamics.Performance.Reporting.Common.ReportingDatabaseConnectivityException: El usuario de la base de datos no tiene los permisos necesarios para ejecutar esta aplicación. ---> System.Data.SqlClient.SqlException: Cannot open database "MRDM2021" requested by the login. The login failed.
Login failed for user 'sa'.
en System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
en System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
en System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
en System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
en System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
en System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
en System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
en System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
en System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
en System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
en System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
en System.Data.SqlClient.SqlConnection.Open()
en System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
en System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
en System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
en System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
en System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)
en Microsoft.Dynamics.Performance.Reporting.Security.SecurityAccess.GetPrincipal(String securityIdentifier)
en Microsoft.Dynamics.Performance.Reporting.Security.Service.ServerSecurityContext.SetCurrent(ISecurityProvider provider)
en Microsoft.Dynamics.Performance.Reporting.Security.Service.SecureServiceBase.SecureExecuteInternal[TResult](Func`1 hasPermission, Func`1 executor)
en Microsoft.Dynamics.Performance.Reporting.Common.Service.ServiceBase.BaseExecute[TResult](Func`1 executor, String callerName)
--- Fin del seguimiento de la pila de la excepción interna ---
Machine: GCI-C01VM-020

Regards

Julia Gallegos

Categories:

Microsoft Dynamics GP

Hi Julia,

Does the Application Service start, but then stop again? If is does, you'll need to remove it and reinstall it.

The error is saying that it failed to retrieve the companies and that the database user does not have permissions to run the application. If you start the service and then it stops, you'll want to remove it and reinstall it. I would recommend removing both the Process and Application services and then reinstalling them.

If the services stay running and it is just the data mart that is failing, you'll want to run the scripts below. These will delete the integration records that exist in the MR database. There may be an old integration record that is causing this to fail. You'll want to remove the existing data mart integration, if it exists, and delete the data mart database in SQL. Then run these scripts and then add the data mart. Run the scripts one at a time. Do not run all six at once.

--1
Alter table [Connector].[Integration] DISABLE trigger ALL
Alter table [Connector].[Map] DISABLE trigger ALL
Alter table [Scheduling].[TaskCategory] DISABLE trigger ALL
Alter table [Scheduling].[Task] DISABLE trigger ALL
Alter table [Scheduling].[Trigger] DISABLE trigger ALL

--2
DELETE FROM [Connector].[IntegrationGroup]
DELETE FROM [Connector].[Map] WHERE [MapId] IN (SELECT [Id] FROM [Scheduling].[Task] WHERE [CategoryId] IN (SELECT [Id] FROM [Scheduling].[TaskCategory] WHERE [ParentId] IN (SELECT [Id] FROM [Scheduling].[TaskCategory] where ParentID is null)));
DELETE FROM [Connector].[MapDefinition] WHERE [DefinitionId] NOT IN (SELECT DISTINCT DefinitionId FROM Connector.Map)

--3
DELETE FROM [Scheduling].[Task] WHERE [CategoryId] not IN (SELECT [Id] FROM [Scheduling].[TaskCategory] where ParentID is null)
DELETE FROM [Scheduling].[Trigger] where Id NOT IN (SELECT distinct TriggerId from [Scheduling].[Task])
DELETE FROM [Connector].[MapCategoryAdapterSettings]

--4
Alter table [Connector].[Integration] ENABLE trigger ALL
Alter table [Connector].[Map] ENABLE trigger ALL
Alter table [Scheduling].[TaskCategory] ENABLE trigger ALL
Alter table [Scheduling].[Task] ENABLE trigger ALL
Alter table [Scheduling].[Trigger] ENABLE trigger ALL

--5
DELETE FROM [Connector].[Integration]

--6
DELETE FROM [Scheduling].[TaskCategory]

Quick Links