Windows Server 2019 and SQL Server Backups issue?

Question

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 '\SERVERDIRECTORYBACKUPDIRECTORYBackup_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.

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

David Browne - Microsoft · 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.

CalabreseFRJ · Answer

Try to Change the admin user and pass of the computer:
[Computer]Administrator
or
[Domain]Administrator.
with this you will solve, but do not stop following these tips ...
;-)
Look in Windows Services. Start > Administration > Services
Find the Service in the list called: SQL Server (MSSQLSERVER) look for the "Log On As" column (need to add it if it doesn't exist in the list).
This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)
NOTE: Remember to give permissions to the actual directory AND to the share if you are going across the network.
Apply and wait for the permissions to propogate, try the backup again.
NOTE 2: if you are backing up across the network and your SQL is running as "Local Service" then you are in trouble ... you can try assigning permissions or it may be easier to backup locally and xcopy across outside of SQL Server (an hour later).
NOTE 3: If you're running as network service then SOMETIMES the remote machine will not recognize the network serivce on your SQL Server. If this is the case you need to add permissions for the actual computer itself eg. MyServer$.

Windows Server 2019 and SQL Server Backups issue?

2 Answers

Add your own answers!

Ask a Question