Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.).

Question

SQL 2005 Enterprise

Even if I logon as Administrator and try to backup any of my databases to local partitions, I get this error below. I have been operating fine up until this point and doing backups all the time.  As far as I know, and after checking, no permissions have changed and of course Administrator has ntfs permissions to my local C and other partitions on this server:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Backup failed for Server 'servername'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

In the Event log for Windows Server 2003 I see this:

 

Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
Date:  3/6/2006
Time:  3:35:06 PM
User:  NT AUTHORITY\NETWORK SERVICE
Computer: servername
Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
 to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20).  This security permission can be modified using the Component Services administrative tool.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Answer 1

You need to check the security permissions on the folder you are trying to backup to. Make sure the service account (Network Service) has write permissions. It may well not do.

SQL on changing the service account changes the permissions on the critical drives it knows about but won't to it for all the drives in a server.

Answer 2

well I added Network Service with the right permissions.  Still same error.  Like I said before this was never a problem.  I am the only one who touches the server and I haven't changed permissions except to change what permissions Network Service had in logons but I changed that back wto what it was previous and what my other server has in SQL logins.

Answer 3

It doesn't matter who *you* are logged in as, it is the service account for SQL Server service that 
matters

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/ 

Answer 4

Thanks a lot NNTP, that was it. I added my domain account that I had created to run all SQL Services to the security of the partitions on my server and I have access now.  I'm not sure though what changed, I was able to do this before unless someone took this login out of the security..I guess it's possible but as far as I know nobody it touching my server

Answer 5

Tnks God I found this post... It really save me!!!

rgds

Answer 6

Hi,

I have the same problem. I have changed the SQLAGENT  service to run as domain\administrator but are you suggesting the sql server instance service also?

Thanks

Paul

 

 

 

Answer 7

On a Windows 2000 Server with SQL Express 2005 freshly installed, giving the "SYSTEM" user full control on the target folder seems to do the trick without the need to change the service user.

Answer 8

SQL Service account is DOMAIN\SQLAdmin

Have given the SQLAdmin AD account access to the Backup shares on the file servers (2 file servers, FILE1, FILE2)

and I still get the error

Operating system error 5(Access is denied.).

RESTORE FILELISTONLY

FROM DISK = N'\\SERVER\APPS\backups\UA\DB\DB.BAK'

I trid to attach it as device, which runs

sp_addumpdevice 'disk', 'Network_Share', '\\SERVER\backup\APPS\UA\DB\DB.BAK'

BUT when restoring, same error occurred (Access denied)

Only case I can backup/restore to/from network shares, is from the machines where SQL Server runs (SQL1, SQL2)

e.g. I copy the BAK file from FILE1 to SQL2, share the folder (add SQLAdmin account with access), then restore on SQL1 with file from SQL2 Share

RESTORE FILELISTONLY

FROM DISK = N'\\SQL2\backups\UA\DB\DB.BAK'

Answer 9

 

When you try to backup, SQL Server 2005 shows the default backup path i.e. c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ . This directory default has SQL user rights. Go to Backup directory and check the user name. Its very big name and I have not checked how SQL server adds it in allowed users list. You can do 2 things:

 

1. Let the Server take backup on default directory and copy the file in your desired folder. I did that.

2. Give same kind of permissions to your desired folder as Backup directory has.

 

Hope it solves the issue.

 

Moonguy

 

 

Answer 10

This problem occurs when you are attempting to backup to a UNC patch and the SQL server does not have "modify" rights to the backup path. With the backup path sharepoint, and on the backup directory itself, grant the SQL server "modify" rights - when doing this be sure that "computers" is selected under "object types"

 

 

Hopes this helps

Answer 11

You are a life saver...  This was driving me nuts.  I must add this to my blog.....

 

Answer 12

Thanks alot MoonGuy,

also i found that the installation path & Backup Directory of SqlServer Express can be found at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer in BackupDirectory key.

Answer 13

I ran into a similar scenario where I wanted to backup my Testing databases on to the E:\ of my Dev database server for storage.

 

I just added the service account (LOCAL\sql05svc)  for Sql Server into the local admin group on the Dev machine where I am writing the backups to and it worked out fine too. The account did have the same password on both machines though.

 

I am guessing you could do the same with a Domain account that you use to run Sql Server service with.

 

Answer 14

Hey wat is the solution?? Can u plz share with me, I dint get wat exactlly u guys are talking about...  I am getting the same problem, I have a user_account and i dont know what permisions it has, I took a backup from the system connected in LAN, and when i tried to restore the database after taking backup, I got this error.  Its troubling a lot yaar, plz get me a solution and take me out of this.....

 

Answer 15

great help..i had the same problem and i have resolve it doing the follow

 

my operating system is winXP pro.

and MSDE 2005

 

Control panel->administrative tools->services-> SQL Server

at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'

 

thanks for the help i have spent more than four hours googling for it

 

Answer 16

"Control panel->administrative tools->services-> SQL Server

at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'"

 

If I believed in a God I'd ask him/her/it to bless you. I love it when people explain things

 

Dominic

Answer 17

This happened to me when I attempted to create a new database from a .bak file using RESTORE.  I just right-clicked on the .bak file, selected Properties and then clicked the Security tab.  Then, I did a Check Name for "SQL" and added the following two SQL accounts from the list:

1)  SQLServer2005MSSQLUser$<machine name>$MSSQLSERVER

2)  SQL

The reason this happened to me is because I was attempting to create a database from a .bak file which was not created on my machine.  Whenever you backup an existing database, the above SQL accounts are already added to the security profile of the backup file.  Because I was using a .bak file created on another machine, the necessary SQL accounts on my machine hadn't been granted permissions.  Adding them manually helped. 

If you run into a similar problem at the folder-level, try right-clicking to see if the above two accounts exist within the Security tab. 

Answer 18

Thanks, that helped a lot.

 

AvenueStuart wrote:

This happened to me when I attempted to create a new database from a .bak file using RESTORE.  I just right-clicked on the .bak file, selected Properties and then clicked the Security tab.  Then, I did a Check Name for "SQL" and added the following two SQL accounts from the list: 1)  SQLServer2005MSSQLUser$<machine name>$MSSQLSERVER 2)  SQL The reason this happened to me is because I was attempting to create a database from a .bak file which was not created on my machine.  Whenever you backup an existing database, the above SQL accounts are already added to the security profile of the backup file.  Because I was using a .bak file created on another machine, the necessary SQL accounts on my machine hadn't been granted permissions.  Adding them manually helped.  If you run into a similar problem at the folder-level, try right-clicking to see if the above two accounts exist within the Security tab.

Answer 19

"Control panel->administrative tools->services-> SQL Server

at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'"

totally agree. otherwise i was still lost. Thanks heaps.

Answer 20

 

Make sure you dhould be having enough access on the drive. from SQL level and OS level too.

Answer 21

Arxigos wrote:

great help..i had the same problem and i have resolve it doing the follow   my operating system is winXP pro. and MSDE 2005   Control panel->administrative tools->services-> SQL Server at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'   thanks for the help i have spent more than four hours googling for it

 

Thank you for explaining, i was lost, and i didnt know wut else to do thanks

Answer 22

I was having the same problem trying to restore a backup file sent to me.  Heres how I fixed it:

Copied the backup file into SQL's default backup directory (Program files\SQL\MSSQL.1\Backup)

Right clicked the file, properties

Under Security clicked Advanced

Check "Inherit from parent...." apply and ok


Should do the trick....in my case the directory had the right permissions so if I did a backup the file would get the right permissions but on a file sent to me it did NOT have the right permissions.  Doing this assigns the same permissions and the backup folder and also then you don't have to make any other system changes since you just changed the file.  Hope this helps someone.

-Allan

Answer 23

Thanks, this solution worked for me

Answer 24

Thank you Simon Sa,
Even i had the same error while i was trying to backup to my local drive and I added my NETWORK SERVICES to allow to write onto my local Drive this was very helpful to me Thankuuu....

Answer 25

Arxigos wrote:

great help..i had the same problem and i have resolve it doing the follow   my operating system is winXP pro. and MSDE 2005   Control panel->administrative tools->services-> SQL Server at the properties i choose 'log on as: local system account and also choosed to allo service to interact with desktop'   thanks for the help i have spent more than four hours googling for it

This worked perfect for me.
Thanks!!!

Answer 26

Thanks for all who gave this great info. Save me a lot of time...

 

Answer 27

 

Hello all,

 

I had a similar issue with this problem while restoring a database backup file.

We were able to solve the problem by giving Read permissions to the SQLBrowserUser.

 

You can find the discussions at http://www.kodyaz.com/forums/thread/3515.aspx

 

Answer 28

Thank You Thank You Thank You - This saved me too. I'm so thankful for you taking the time to reply.

 

Answer 29

İt worked for me . Thank you very much Simon.

I added NETWORK and NETWORK SERVICE accounts to the copy folder permisssions and security tabs.

 

SimonS_ wrote:

You need to check the security permissions on the folder you are trying to backup to. Make sure the service account (Network Service) has write permissions. It may well not do. SQL on changing the service account changes the permissions on the critical drives it knows about but won't to it for all the drives in a server.

Answer 30

Hi,

Just a google searcher who found your post and wanted to say thanks!  Your directions solved my issue.  Appreciate you taking the time to write it down!

-RandyB

Answer 31

Hi  Arxigos,

Thanks...

---

alg

Answer 32

Same problem, same solution... thanks a lot.

Answer 33

Hi I seem to be running into a similar issue. Trying to use the DBCopyTool from codeplex, my issue is that I receive the error when the application is trying to copy the .bak file from my local machine to the backup folder on the remote machine...so I new to SQL any detailed response would be helpful seems like most users are receving a .bak file to restore...I'm sending a file to a remote machine to have it restored, thanks.

Error restoring the database. Cannot open backup device 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\QCTL_new_CopyTool.bak'.
Operating system error 2(The system cannot find the file specified.).
RESTORE FILELIST is terminating abnormally.

Answer 34

I tangled with the same problem.  I solved it by changing the SQL Server Service Logon to Local System.  Which is weird because all my other SQL Servers use the NetworkService account.  

I've backed up and restored dozens of databases on many different servers all over the place and I've only seen this problem on this one machine.  I'm not sure why this is like this.  

The machine was recently rebuilt due to a RAID failure but I'm not sure that was the problem.  Bizarre.  

If anyone finds out why this happens in the first place I'd really like to know.  

Answer 35

Thanks to  Arxigos,

Was also googling several hours to find an answer.


Thanks again for the solution.

DKV

Answer 36

you guys are real life-savers. you save my day.

---

Man may come and Man may go, but I go on forever

Answer 37

Saved me too.  This one made no sense.  I was working with an application called MediaBin which invokes the backup out of SQL 2005.  Well, I moved the repository for MediaBin to a new bigger location and the backup immediately failed.  Thank god I was able to find the error message "Operating system error 5(Access is denied.)" in a log file.  Thanks!