Sql-server – Windows Server 2019 and SQL Server Backups issue

sql-server-2019windowswindows-server-2019

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 2019 Express.

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.).
BACKUP 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.

enter image description here

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

enter image description 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 Server 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?

Also, just to clarify, this is a local directory. SQL Server is on the server and is attempting to backup to a directory ON the server.

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.

Best Answer

this is a local directory. SQL Server is on the server and is attempting to backup to a directory ON the server

If SQL Server has access permissions on the directly, how can it NOT have permissions on the directory?

You're backing up to a share, not a directory. When accessing a share the account must have permissions both on the share and the underlying directory.

Also it's possible that a Credential is used for NTLM auth to the share and it's attempting to authenticate using some other configured identity.

Change the backup to point directly to the local folder.

Related Question