Sql-server – Does the SQL Server Service Account have to have access to a network share to perform a backup

backuppermissionsservice-accountssql server

I've been provided a specific domain account to handle backups from my sysadmins. I am slightly confused as I've never been provided a separate domain account to handle backups in the past. I still want to use Agent to handle this, so I am thinking that they are thinking the below:

If you are running the job under a proxy account, the proxy must be a
domain account. Add the account to the sysadmin server role and grant
it full control of the directory and the network share.

So I've done what I thought the above is describing, I added the domain account as a login, I created a credential & proxy, and I created the job step as CmdExec to run under the proxy, but I still get:

Operating system error 5(Access is denied.).

It isn't until I give access to the Service Account that is running the SQL Server Service that I am able to backup to this network share. I'm also concerned about restores and I wanted to utilize Agent to handle cleanup tasks & moving files around to various directories as they age.

So I'm not sure what's going on here, but I think the service account at a very minimum needs write access? One sysadmin said he was aware of someone doing the backup process as a "separate service", which I don't quite understand, unless its something like ApexSQL backup or something…

Best Answer

If you're performing native SQL Server backups, the service account running the SQL Server database engine must have access to the backup location. The Agent job can run under a proxy to fire off the job itself, but the engine still performs the backup so still needs access.

The only way to have the backup write to disk as another user would be to use a third-party tool that receives the backup data through the SQL VDI interface and writes it to disk as the appropriate user.

From Docs:

Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions.

For managing the files after they've been backed up, you can use a SQL Server Agent proxy for this to ensure you're using the correct account for file system access.

Potential Workarounds

Since you're running PowerShell through SQL Server CmdExec from an agent job, you could try calling a PowerShell script with your saved PS logic in it rather than running raw PowerShell commands to see if that makes a difference for a potential workaround.
Try explicitly putting the full OS path on the server to point to PowerShell.exe and then pass your PowerShell commands after that in case the issue has to do with environmental variables not working correctly for PowerShell within the CmdExec shell.

My question: Why does the same command fail as a job, but succeed from the console when both executed from the same user account on the same server?

Potential Reasons

It could be an issue when calling PowerShell.exe thru the SQL Server Agent job with the CmdExec shell and how it interprets security context of the account it's run from or as (e.g. the service account) when it tries to run PowerShell.exe that way so it's not able to authenticate the execution access to the EXE due to this.
Check and read up on the version of PowerShell installed, the version of SQL Server installed, and the Windows OS all these are on and run from for "known" issues related to version incompatibilities in this respect - it may be a known issue depending on ALL the specifics in this environment.

A working way I run PowerShell from SQL Agent Jobs

You will need to:

Create the [user account object] service account in AD (<DomainName>\<ServiceAccount>)
Create a SQL login i.e. the service account in #1 (<DomainName>\<ServiceAccount>)
Create a SQL credential with that same (<DomainName>\<ServiceAccount>) account credentials,
Create a SQL Server Agent Proxy account to use for the "Run As" from the SQL Agent job to execute PowerShell

Below is a script of the T-SQL part of this process which I run after doing the above 4 steps, and it always works in my environment to accomplish what seems to be similar to what you've explained that you're trying to accomplish. I also have to ensure the AD account has a strong/complex password, and set it to never expire.

--- // Create login on SQL Instance for domain\PSSQLJobs service account if it does not exist
IF NOT EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
BEGIN
    CREATE LOGIN [domain\PSSQLJobs]
    FROM WINDOWS WITH DEFAULT_DATABASE = [master]
        ,DEFAULT_LANGUAGE = [us_english]
END

USE [master]

IF NOT EXISTS (
        SELECT *
        FROM sys.database_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
    CREATE USER [domain\PSSQLJobs]
    FOR LOGIN [domain\PSSQLJobs]
    WITH DEFAULT_SCHEMA = [dbo]

EXEC sp_addrolemember N'db_datareader'
    ,N'domain\PSSQLJobs'

GRANT CONNECT
    ON DATABASE::[master]
    TO [domain\PSSQLJobs]

USE [msdb]

IF NOT EXISTS (
        SELECT *
        FROM sys.database_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
    CREATE USER [domain\PSSQLJobs]
    FOR LOGIN [domain\PSSQLJobs]
    WITH DEFAULT_SCHEMA = [dbo]

EXEC sp_addrolemember N'db_datareader'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentOperatorRole'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentReaderRole'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentUserRole'
    ,N'domain\PSSQLJobs'

GRANT CONNECT
    ON DATABASE::[msdb]
    TO [domain\PSSQLJobs]



--- // Create Credential *** Type in password of account into value of SECRET ***
USE [msdb]

CREATE CREDENTIAL PSSQLJobs
    WITH IDENTITY = 'domain\PSSQLJobs'
        ,SECRET = '*******'


--- // Create PowerShell Proxy account and give PSSQLJobs access to it
USE [msdb]

EXEC msdb.dbo.sp_add_proxy @proxy_name = N'ExecutePowershell'
    ,@credential_name = N'PSSQLJobs'
    ,@enabled = 1

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'ExecutePowershell'
    ,@subsystem_id = 12

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name = N'ExecutePowershell'
    ,@login_name = N'domain\PSSQLJobs'

Best Answer

Related Solutions

Sql-server – How to copy backup files to remote share in SQL Server Agent job without AD/domain accounts involvement

Sql-server – Command fails as CmdExec job but succeeds from console

Potential Workarounds

Potential Reasons

A working way I run PowerShell from SQL Agent Jobs

Related Question