AX service won’t start after migrating to a new SQL Server
After migrating the AX databases to a new SQL server you might end up having issues starting the service. If you will search the even log you will probably find the following errors:
Object Server 01: Fatal SQL condition during login. Error message: “[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘domain\user’.”
Object Server 01: The database SQLSERVER\INSTANCE is not recognized as a model store.
The domain\user will be the service account you are using to run the AX service.
Providing this account even with sysadmin on the SQL server will not help you, as the problem is a missing stored procedure on the Master database that is created during the initial AX setup – This stored procedure is missing from your new SQL server and needs to be created manually.
1. Run the following query on your new SQL server (Backup first!)
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CREATETEMPDBPERMISSIONS_AX_SERVER_NAME_01] as begin exec (‘USE tempdb; declare @dbaccesscount int; exec sp_grantlogin ”domain\axserviceaccount”; select @dbaccesscount = COUNT(*) from master..syslogins where name = ”domain\axserviceaccount”; if (@dbaccesscount <> 0) exec sp_grantdbaccess ”domain\axserviceaccount”; ALTER USER [domain\axserviceaccount] WITH DEFAULT_SCHEMA=dbo; exec sp_addrolemember ”db_ddladmin”, ”domain\axserviceaccount”; exec sp_addrolemember ”db_datareader”, ”domain\axserviceaccount”; exec sp_addrolemember ”db_datawriter”, ”domain\axserviceaccount”;’) end
GO
EXEC sp_procoption N'[dbo].[CREATETEMPDBPERMISSIONS_AX_SERVER_NAME_01]’, ‘startup’, ‘1’
GO
* Change “AX_SERVER_NAME” to your AX server name
* Change domain\axserviceaccount to your domain and the account that runs the AX Service (If you are using NETWORK SERVICES for the service (Bad…) the use DOMAIN\MACHINENAME$ for the account)
2. In SSMS go to Databases -> System Databases -> Master -> Programmability -> Stored Procedures:
There you will find the newly created SP. Right click -> Properties
Under permissions – make sure to add the AX service account and grant it the Execute permission
The service should now start with no issues
– Don’t forget that the AX service account first needs to be added as a user under security logins and granted the db_datareader, db_datawriter & db_ddladmin roles!
Object Server 01: Fatal SQL condition during login. Error message: “[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘domain\user’.”
Object Server 01: The database SQLSERVER\INSTANCE is not recognized as a model store.
The domain\user will be the service account you are using to run the AX service.
Providing this account even with sysadmin on the SQL server will not help you, as the problem is a missing stored procedure on the Master database that is created during the initial AX setup – This stored procedure is missing from your new SQL server and needs to be created manually.
1. Run the following query on your new SQL server (Backup first!)
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CREATETEMPDBPERMISSIONS_AX_SERVER_NAME_01] as begin exec (‘USE tempdb; declare @dbaccesscount int; exec sp_grantlogin ”domain\axserviceaccount”; select @dbaccesscount = COUNT(*) from master..syslogins where name = ”domain\axserviceaccount”; if (@dbaccesscount <> 0) exec sp_grantdbaccess ”domain\axserviceaccount”; ALTER USER [domain\axserviceaccount] WITH DEFAULT_SCHEMA=dbo; exec sp_addrolemember ”db_ddladmin”, ”domain\axserviceaccount”; exec sp_addrolemember ”db_datareader”, ”domain\axserviceaccount”; exec sp_addrolemember ”db_datawriter”, ”domain\axserviceaccount”;’) end
GO
EXEC sp_procoption N'[dbo].[CREATETEMPDBPERMISSIONS_AX_SERVER_NAME_01]’, ‘startup’, ‘1’
GO
* Change “AX_SERVER_NAME” to your AX server name
* Change domain\axserviceaccount to your domain and the account that runs the AX Service (If you are using NETWORK SERVICES for the service (Bad…) the use DOMAIN\MACHINENAME$ for the account)
2. In SSMS go to Databases -> System Databases -> Master -> Programmability -> Stored Procedures:
There you will find the newly created SP. Right click -> Properties
Under permissions – make sure to add the AX service account and grant it the Execute permission
The service should now start with no issues
– Don’t forget that the AX service account first needs to be added as a user under security logins and granted the db_datareader, db_datawriter & db_ddladmin roles!
