1

I have been trying to backup an sql server database, and I understand that this error is in regards to drive write protection. I am at the end of my rope with this, as nothing that I have found online has helped with this at all.

I have set up permissions for what should be considered every imaginable user that the server may try to pose as, everyone, administrators, my user, mssqlserver, sqlservermsasuser$xxx$MSSQLSERVER, SQLServer2005SQLBrowserUser, all with permissions set to full control.

I've tried running visual studio as admin, i've tried running the built application as admin, I've changed the "log on as" for the mssqlserver service to "Local System Account", me, and admin.

Yet still am having the same error, Operating System error 5 "Access is denied". The folder is not read only, and is not in the root of the drive. Any ideas??

Thanks in advance

1
  • Offtopic. Not a programming question. Try the DBA site.
    – Marc B
    Jan 27, 2014 at 18:43

2 Answers 2

0

Troubleshooting idea: Change the folder permissions temporarily to allow "Everyone", make the backup, then look at the backup file: File -> Properties -> Details -> Owner. Hopefully it lists the exact user. However its possible you need more than just this (not sure).

Some typical's are:

  • NT Service\MSSQL$InstanceName (displays as MSSQL$InstanceName)
  • NT Service\SQLAgent$InstanceName (e.g. backup made from a job, disp as SQLAgent$InstanceName)
  • System,
  • Network Service,
  • machine_name$ (for remote backups)
  • and the user login doing the backup.
9
  • thanks, i did add all of those as permissions, and applied them to the default backup folder, and tested against that, still with os error 5. Am using windows 8 if that has anything to do with it.
    – TWorkIT
    Jan 28, 2014 at 8:11
  • Are you logged in as an Admin to the Database? Within SSMS, right click on Security -> Logins -> Properties and check under ServerRoles to verify you are listed as "sysadmin" Could you post your backup command in the original question? If you are using a GUI usually there is an option to output the command as "SQL Script."
    – crokusek
    Jan 28, 2014 at 17:42
  • I am logged in as an admin. I can create a backup using sql server management studio, i cross referenced the permissions on that file to the folders i have been tring to save to, and they all align fine. Still, despite all this, I cannot backup the database using c# code without getting the os error 5.
    – TWorkIT
    Feb 6, 2014 at 7:37
  • Is this a web based program? Those can run with/without impersonation turned on (see web.config). If you want to be you it would need to be "on" (but this is not recommended for deployment). Check the PID of the C# exe owner in Task Manager. I suggest running a trace using SQL Server Profiler which should be able to list the command being sent from the C# side. It will list the SQL Server User also. How about double checking path names (must be escaped): "C:\\abc\\a.txt"
    – crokusek
    Feb 7, 2014 at 0:12
  • Is not a web based application, but future development will lead to it being network based. Ran a trace with the sql profiler which returns under SQL:BatchStarting and SQL:BatchCompleted- BACKUP DATABASE [Budgeting] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup' WITH DESCRIPTION = N'full backup', EXPIREDATE = N'11/03/2016 00:00:00', NOFORMAT, NOINIT, NAME = N'database backup', NOSKIP, REWIND, NOUNLOAD, STATS = 10 Code to backup database: DatabaseBackup.BackupDatabase("C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Backup");
    – TWorkIT
    Feb 8, 2014 at 3:31
0

You can find the account that your SQL service runs as through the services dialog on the server. That's the account that needs write permission to the directory that you've specified.

3
  • this is done, but no cigar
    – TWorkIT
    Jan 27, 2014 at 21:05
  • Are you writing to a folder on the database server or your local machine? Backups are specified relative to the database server, not your client.
    – dpw
    Jan 27, 2014 at 21:38
  • I am writing to the local machine, and is currently both server and client.
    – TWorkIT
    Jan 28, 2014 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.