1

For SQL Server 2016 Express, I would like to backup/restore directly to/from network storage I have mapped to drive S:. SQL Server Express runs under the (virtual?) account NT SERVICE\MSSQL$SQLEXPRESS as it should, which of course does not have the permissions to access S:.

As explained in Broken NTFS Permissions for MSSQL$SQLEXPRESS I have tried to add NT SERVICE\MSSQL$SQLEXPRESS to the users of S:. This fails with a "Name Not Found" message-box:

An object named "NT SERVICE\MSSQL$SQLEXPRESS" cannot be found.

My machine is not on a domain and has been restarted since S: and SQL Server Express were installed.

Why does it not find that user? Is there another way to make individual folders accessible to the default SQL Server user?

Improve this question
2
  • 1
    NT SERVICE\MSSQL$SQLEXPRESS is a local account, it will exist on the server/computer where sql express is installed, but not on the network storage server. Did you actually map the S: drive locally, or are you just unc'ing (\\servername\share or \\servername\d$) to it?
    – Jason B.
    Commented Jul 26, 2016 at 20:52
  • I have used "This PC" / "Map network drive..." to constantly map the network storage under S:. Hope this answers your question.
    – AndreasS
    Commented Jul 27, 2016 at 15:18

1 Answer 1

Reset to default
1

When you map that S: drive to a network share, you are using your local Windows account to do it (the user you're logged in as). However, SQL Server Express is running under a different account.

There are a few things you can try to work around this.

  • Create a network share which has "Everyone" permission to write to, and then use the UNC path (\\server\path) as the target inside SQL Server for backups.

  • Run the SQL Server service under a different account, which happens to coincide with an account using the same name and password on the network resource.

  • Back up your databases to a local drive, and use some synchronization method (ROBOCOPY, XCOPY, etc.) to move those files to the network share, running under your normal Windows credentials. You can achieve this using the Windows Task Scheduler, running a PowerShell script or a Windows batch file.

Improve this answer
2
  • Thanks Randolph, for the extensive workaround guide! So in seems with SQL-Server you need to go through lots of hoops to do a simple best-practice thing: Directly save a backup on a DIFFERENT media. :-(
    – AndreasS
    Commented Jul 30, 2016 at 14:40
  • Yeah, I always save locally first and then use a PowerShell or ROBOCOPY method to move or copy it off. More moving parts, but under better control. In a larger environment with domain access, I give the SQL account access to network shares and then it works as you expect.
    – Randolph West
    Commented Jul 31, 2016 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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