SQL Server Runs as Local System - Cant Backup to External Server

SQL Server Administration
1

Hi everyone,
This is MSSQL 2008 R2 on Windows Server 2008 R2
I'm trying to backup a database ---> a path on another SQL Server.
The database service on the SOurce server runs as Local System. The backup fails with Access is denied.

  1. Can I grant Local System account permissions (on the target server)?

  2. Is my only option to change the SQL service to run as a domain account, then grant that account permissions on the destination folder?

Thanks

2

Any ideas?

3

is this a one time back up or continuous ?
how are you doing this backup?

4

I will automate the backups to run as an Agent job. The database and agent services run as Local System now. But it also fails with 'Access is denied' when I run it in Management Studio under my domain account, even though I granted my account Full permissions to the folder on the destination path.

5

What fails when you run "it" in Management Studio? What is that "it"

6

backup database ABC to disk = 'path......'

get "Access is denied" when it runs as a Job or when I run the above statement in SSMS

7

can you access the destination folder just manually via a file explorer

Thanks

8

Yes I can access the path manually

9

can you create a simple file in the destination folder by accessing this folder from the sql server.

10

Backup runs in the context of the account running SQL Server - and since that account is local system it does not have access to the UNC path. It doesn't matter who is logged into SSMS or how you are logged into SSMS (Windows or SQL authentication) - the command still runs in the context of the account that is running the SQL Server service.

You should also be aware that access is now setup/granted using the per-service SID accounts (NT Service\MSSQL$SQLSERVER). Since you cannot grant access to a UNC path to this account - you need to be able to identify a domain account that will be used.

The simplest method is to run SQL Server with a dedicated domain service account - then you can grant access to the domain account for those network resources. The other option is to create a proxy account and then run the job from SQL Server Agent using the proxy account.

Using a proxy account will not allow access to the UNC path from SSMS though...it will only work through SQL Server Agent jobs.

Here is a document on how to grant access to the per-service SID: Configure File System Permissions for Database Engine Access - SQL Server | Microsoft Learn

1 Like
11

Have you tried running your SQL Server as Network Service? I can't remember the exact name but it's one of the options in Configuration Manager. You may have to grant permissions on the share to the machine account (SERVER$). If it fails, look at the error message in event viewer on the target and see what it tried to log in as.

12

I changed sql services, including the Agent service, to run under a domain account. Then of course I was able to backup to a network drive. It was painful though, because you have to bounce the database engine service to get the new account into effect which in my case caused a brief outage to a production system.

1 Like