Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.).
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 athttp://go.microsoft.com/fwlink/events.asp.
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.
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
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' 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
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
