Hi ,
For an audit purpose we would like to restore a NAV2015 database and make it read only from SQL side so that no one can make any changes to the database. This is what we’ve been doing in the past with NAV2009 and 3.7 databases so that users can login but can’t make any changes to the data within the database.
When we do this in 2015, the service tier can’t connect to the database with the following error. One way to fix this is to assign all users with super read only role except the service tier account so that no one can make changes to data.
I’m wondering if anyone has come across this issue and found any workarounds?
Date 09/05/2017 09:34:56
Log Windows NT (Application)
Source MicrosoftDynamicsNavServer$DynamicsNAV80GB
Category (13)
Event 216
Computer
Message
Server instance: DynamicsNAV80GB
User: xxx
Type: Microsoft.Dynamics.Nav.Types.Exceptions.NavSqlException
ErrorNumber: 3906
SuppressMessage: False
SuppressExceptionCreatedEvent: False
FatalityScope: None
Message:
The following SQL error was unexpected.
Failed to update database “Demo Database NAV (8-0) GB” because the database is read-only.
SQL statement:
UPDATE [Demo Database NAV (8-0) GB].dbo.[Server Instance] SET [Status] = 2 WHERE [Server Computer Name] = @1 AND [Server Instance Name] = @2 AND [Status] <> 1;INSERT INTO [Demo Database NAV (8-0) GB].dbo.[Server Instance] ([Service Name],[Server Computer Name],[Last Active],[Server Instance Name],[Server Port],[Management Port],[Status])VALUES (@0,@1,GETUTCDATE(),@2,@3,@4,0) SELECT CAST(SCOPE_IDENTITY() AS INT)
StackTrace:
at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
at System.Environment.get_StackTrace()
at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ThrowNavSqlException(SqlExceptionAdapter exceptionAdapter, NavSqlCommand command, Boolean isLastExceptionARollbackCause, Boolean logExceptions)
at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunction[T](Func1 function, NavSqlCommand command) at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunctionWithTrace[T](EventTask task, Func1 function, NavSqlCommand command)
at Microsoft.Dynamics.Nav.Runtime.NavSqlCommand.ExecuteScalar()
at Microsoft.Dynamics.Nav.Runtime.Heartbeat.CreateServiceInstanceRecord(NavDatabase database, String sqlCommand)
at Microsoft.Dynamics.Nav.Runtime.Heartbeat.WriteHeartbeatToServiceInstanceTable()
at Microsoft.Dynamics.Nav.Runtime.PeriodicScheduler.RunAction()
at Microsoft.Dynamics.Nav.Runtime.ThreadPeriodicScheduler.DoScheduledAction()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
HResult: -2146233088
Type: System.Data.SqlClient.SqlException
ClientConnectionId: b45aa8d9-a473-4c8c-a20a-666e6b50fb33
Class: 16
LineNumber: 1
Number: 3906
Server:
State: 1
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: Failed to update database “Demo Database NAV (8-0) GB” because the database is read-only.
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunction[T](Func`1 function, NavSqlCommand command)
HResult: -2146232060