0

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.

1

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.

4
  • OK, how would I change the backup to point directly to the local folder? I want all of the workstations to be able to request a backup to be performed. 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? I did set the permissions on the directory where I also set the sharing permissions. Do you mean that I have to add NT Service\MSSQL$~ under Sharing Permissions as well? Thanks for your help!
    – M_Lyons10
    Jul 23 '20 at 21:44
  • 1
    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!
    – M_Lyons10
    Jul 23 '20 at 21:50
  • 1
    The path in BACKUP is always relative to the SQL Server, not the client. Jul 23 '20 at 21:50
  • Good to know, thank you for your help!
    – M_Lyons10
    Jul 23 '20 at 21:59
0

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

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.