Issue with SQL Backups

Question

Hello everyone!

I have an issue with SQL Server that is really driving me nuts so I wanted to ask if anyone has any suggestions.

I have a program that does a daily backup of the SQL Server database.  This has worked for two years.

Now, today, we had to upgrade our server and along with this update (Now Windows Server 2019 Essentials), we upgraded to SQL Server Express 2019.

Now, when I try to perform the backup, I get the error: Cannot open backup device '\\\\SERVER\\DIRECTORY\\BACKUPDIRECTORY\\Backup_Wed.BAK'. Operating system error 5(Access is denied.).\r\nBACKUP DATABASE is terminating abnormally.

I know that this error generally means that SQL Server doesn't have read / write access, but the issue I have is that it DOES.

This IS the SQL Server Instance, as you can see here:

AND, if I run a backup from within SQL Server Management Studio, this does succeed...

I also checked to make sure that the file and it's contents weren't set to "Read Only" and they're not.

Those are the only two causes I'm aware of and all I was able to find searching online over the past several hours.

There's plenty of room on the drive as well (As I said, it's brand new and there's little on it).

Has anyone else seen anything like this or know what else might cause this issue?  The old SQL Server version was the prior version, so I wasn't upgrading from some ancient release either.

And here's my SQL that has worked for several years.  I can't find anywhere where SQL 2019 changed the requirements here either: BACKUP DATABASE THEDATABASE To DISK = '\\\\SERVER\\DIRECTORY\\BACKUPDIRECTORY\\Backup_Wed.BAK' WITH INIT

I'm at a loss...  If SQL Server has access permissions on the directly, how can it NOT have permissions on the directory?

Is this a bug in Windows Server 2019?

I really appreciate everyone's help and sincerely hope that someone knows a solution here.  Thank you again.

Answer 1

Hi MLyons,

Per your description, you would like to backup on '\\\\SERVER\\DIRECTORY\\BACKUPDIRECTORY\\'.Is it a network share folder?

If yes, the SQL Server service account doesn’t have sufficient permission on the share folder. Please ensure the SQL Server service account is a domain account and it has sufficient permissions.

Please refer below for more information.

You cannot back up databases to a network drive if your account have not sufficient permissions to access the network drive

Best regards,

Melissa

-------------------------------------------

MSDN Community Support

Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]

Answer 2

BACKUP DATABASE THEDATABASE To DISK = '\\\\SERVER\\DIRECTORY\\BACKUPDIRECTORY\\Backup_Wed.BAK' WITH INIT

Have you really used 4/2 backslashes in the backup file name? SQL is not C++/C#, where you must quote backslashes, you have to use a valid UNC file name => 

BACKUP DATABASE THEDATABASE To DISK = '\\SERVER\\DIRECTORY\BACKUPDIRECTORY\Backup_Wed.BAK' WITH INIT

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

i have the same problem !

Answer 4

It seems like I'm also facing this particular issue.

Answer 5

I  recommend you to submit your issue to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

Best regards,

Melissa

-------------------------------------------

MSDN Community Support

Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]

Answer 6

i have the same problem !

What for a problem? SQL Server supports backup to UNC path and it works, when you doing it the right way.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 7

Overlooking the pleothora ov backslashes, it is quite obvious what the problem is.

You look at the share and say "Look MSSQLSERVER has permission!". Maybe it has, but it is not the same MSSQLSERVER. NT Service\MSSQLSERVER is a machine account, and not a domain account.
So NT Service\MSSQLSERVER on machine A is not the same NT Service\MSSQLSERVER on machine B.

One way to solve this is to grant access to the machine account, DOMAIN\MACHINE$, for the SQL Server machine.

But a better alternative is probably to use a gMSA, a group Managed Service Account. Alas, I can't give any details about them, as I have never used them myself. But these gMSAs are domain things, and not local to the machine.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Database engine service might be running under a granted service account not NT Service

Answer 9

Is this actually a local directory, or a network share?  You show a screen print of "C:\...".  Error 5 means "Access Denied"  it is definitely a rights issue.  Also, shares have share rights, in addition to security rights.

As Erland said, you are using a "virtual account" to access network resources, you must use the computer rights.

Please see "Virtual Accounts":

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#VA_Desc

Virtual accounts (beginning with Windows Server 2008 R2 and Windows 7) are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. If the default value is used for the service accounts during SQL Server setup, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$.

Answer 10

i have the same problem !

What for a problem? SQL Server supports backup to UNC path and it works, when you doing it the right way.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

That string is out of Visual Studio, which I'm sure you know formats strings in that format.  That is NOT being done "the wrong way"...  I copied and pasted that out of the debug breakpoint and didn't correct the formatting because I didn't think it would be an issue.  Sorry for the confusion, but please don't dismiss this issue over Visual Studio's formatting.

Answer 11

I apologize if there's some confusion.  This is a LOCAL directory.

SQL Server is running on the Server, and is performing a backup to a directory on the server.  This is not a situation where SQL Server is backing up the database to another machine...

SQL Server and the database reside on the server.

The Backups directory is on the server.  On the C drive.

I hope that clarifies... I feel like there might have been some misunderstanding about the setup, so I apologize for that.  I thought that I was clear with my description of the issue.

This worked without any issue in Windows Server 2016.  I figured this would need the same permissions under Windows Server 2019, but I have noticed several bugs in this version already, so I had concerns that this may be something like that.

Also, to clarify, there are not multiple backslashes, that was copied out of the debug breakpoint and Visual Studio added additional backslashes, etc. as it likes to do.  I didn't edit that because I figured that was pretty normal, but here is the actual code:

                String sql = "BACKUP DATABASE " + DatabaseName + " " +
                    "To DISK = '" + BackupLocation + @"\" + DatabaseName + "_" + NameDesignation + ".BAK' " +
                    "WITH INIT"; //Specifies to overwrite existing

Thank you again for your help.

Answer 12

Thank you for your help Melissa, I read through what you shared and it looks like that is referring to situations where SQL Server is performing backup to another computer. This is a local directory on the server. SQL Server is running on the Server, and is performing a backup to a directory on the server. This is not a situation where SQL Server is backing up the database to another machine... SQL Server and the database reside on the server. The Backups directory is on the server. On the C drive. If I'm misunderstanding you or what you shared, please let me know. This worked without any issue in Windows Server 2016. I figured this would need the same permissions under Windows Server 2019, but I have noticed several bugs in this version already, so I had concerns that this may be something like that. Thank you again for your help.

Answer 13

The Backups directory is on the server.  On the C drive.

In the code you posted, you had \\SERVER\DIRECTORY. SERVER may be the same machine as SQL Server is running on, but I seem to recall that I have run into that also in this case, this counts as an external access, so you would need to set up permission for the machine account.

Or fix the program to actually use a local path.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 14

The Backups directory is on the server.  On the C drive.

In the code you posted, you had \\SERVER\DIRECTORY. SERVER may be the same machine as SQL Server is running on, but I seem to recall that I have run into that also in this case, this counts as an external access, so you would need to set up permission for the machine account.

Or fix the program to actually use a local path.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

The code doesn't even work ON the server...  So, in this case, it is not an issue of permissions from other computers on the network.

This identical code, with the identical permissions, has worked for years under Windows Server 2012 R2 and 2016...  It may date back to when we had Windows Server 2008, but I can't recall.

How would I use a local path?  I want the workstations to be able to call for a backup too.  If a Workstation asks to backup at "C:\\Shares\ProgramDirectory\Backups", wouldn't that then backup to the workstation instead of to that directory on the server?

Thanks for your help!

EDIT: Thank you for your help, I got it working.  I had NT Service\MSSQL$~ added under Security, but I didn't have it under the Share Permissions.  Once I added it to both, it works.

Thank you for pointing that out, I really appreciate your help!

Answer 15

The code doesn't even work ON the server...  So, in this case, it is not an issue of permissions from other computers on the network.

What I'm saying that is that it does not really matter that the access is from the local computer, since you are accessing a share.

This identical code, with the identical permissions, has worked for years under Windows Server 2012 R2 and 2016...  It may date back to when we had Windows Server 2008, but I can't recall.

If it worked on other computers, this may be because you had set up permissions or policies differently. It does not necessarily have to do with the Windows version.

How would I use a local path?  I want the workstations to be able to call for a backup too. If a Workstation asks to backup at "C:\\Shares\ProgramDirectory\Backups", wouldn't that then backup to the workstation instead of to that directory on the server?

Yes, if SQL Server is running on the workstation and you still want the backup to land on the server machine. But in that case, the service account for SQL Server on that workstation needs to have permission to the share. Or, if that is a virtual account like NT Service\MSSQLSERVER, the machine account of the workstation needs to have access to the share.

If you mean that the workstation users are running this program, but are connected to the database server, C:\Shares\ProgramDirectory\Backups will refer to the folder on the machine where SQL Server runs. All paths you give to SQL Server always related to the machine where SQL Server is running.

---

Erland Sommarskog, SQL Server MVP, [e-mail]