0

I am sorry if this has already been answered in the past.

We have SQL Server 2016 which is our Dynamics NAV Database. Currently all backups are stored on local drive of the server. I have been given a task to change the destination of backups to a NAS box network drive. On the maintenance plan I changed the destination path to the network drive \10.##.#.##\databasebackup. I received following error:

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device '\10.##.#.##\databasebackup\navdatabase.bak'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

After having a look at SQL Server configuration manager, I found that the Log On As account for SQL Server (MSSQLSERVER) is NT Service\MSSQLSERVER but Log On As account for SQL Server Agent (MSSQLSERVER) is domain service account. The service account used for SQL Server Agent (MSSQLSERVER) has read/write access to the network drive \10.##.#.##\databasebackup. In this case I believe I need to change the Log On As account for SQL Server (MSSQLSERVER) to a domain account, am I correct? If I change the NT Service\MSSQLSERVER to a domain account which has read/write access to the network share, does it break anything?

Thank you in advance for your help.

1

Correct, it is the service account for the database engine that matters here. Agent is irrelevant (it only tell SQL Server to produce the backup). It shouldn't break anything, but every change should be done with care. And make sure you do the change with the right tool (SQL Server Configuration Manager).

  • 2
    also make sure you address UNC paths with a "\\" instead of a "\", the OP's error message doesn't make it clear that is the case. – Jonathan Fite Dec 10 '18 at 15:51
  • Thank you McNets,Tibor & Jonathan for the information. I successfully changed the SQL Server Log On As account using SQL Server Configuration Manager. Backup has been successful to the network drive but SSRS report (Client Transaction Summary which pulls data from NAV DB to CRM) running from Dynamics CRM stopped running generic error message: Reporting Error: The report cannot be displayed. (rsProcessingAborted) NAV DB Error Log: 2018-12-11 12:34:53.26 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided.[CLIENT: xx.xx.xx.xx] – Puru Dec 11 '18 at 21:37
  • You would need to dig into what this report is doing, in the end. Perhaps linked servers are involved and some login to the remote system is attempted using the service account for the local SQL Server? – Tibor Karaszi Dec 12 '18 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.