SQL Server database backup: Network Service file access

Question

When trying to run the following database backup command from my code I get an "Operating system error 5(Access is denied.)" error. This is because the log on account for the SQL Server Windows Service is 'Network Service' and that does not have access to right to this folder.

BACKUP DATABASE [AE3DB] TO DISK = 'c:\AE3\backup\AE3DB.bak'

My question is, from my code how would I go about figuring out where on the C drive 'Network Service' is allowed to right the backup to?

NOTE: This is a distributed application so I cannot easily change the log on for the SQL Server Windows Service to the 'Local System' account that would be able to right to that folder.

Answer 1

The whole point of Network Service account is to not have rights to the local disk. This prevents network based security holes.

My guess is your server is locked down which means you have to log in locally to do a backup or use the administrative account to do so remotely.

Answer 2

You don't go about searching for random places on C:\ where the SQL Server service account has write access...

You can choose between:

• place the backups in the SQL Server backup location. This is specified during setup and it is properly ACL'ed so that the service account has all the necessary permissions. See 'Backup Directory' in http://msdn.microsoft.com/en-us/library/cc281941.aspx
• place the backup in a well known location that you create and you ACLit for proper permissions for the SQL Server service account. You should not grant permission to the service account itself (NETWORK SERVICE in this casE) but instead grant to the SQL Server administrative service group: SQLServerMSSQLUser$ComputerName$MSSQLSERVER. See http://msdn.microsoft.com/en-us/library/ms143504.aspx