March 3, 2016 at 9:42 am
Ok, so i just need to clarify something. When i run a backup is there any way to do this under an account different than the one used by MSSQL Service?
thanks,
Tom
March 3, 2016 at 11:04 am
Are you talking about executing from a Job or command line or SSMS...?
From BOL:
"BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. "
So anyone with permission can do it.
March 3, 2016 at 11:36 am
djj (3/3/2016)
Are you talking about executing from a Job or command line or SSMS...?From BOL:
"BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. "
So anyone with permission can do it.
Yes anyone can perform the backup command but even with the highest level of permission on the network and sql server it still fails because the service account doesn't have permission to network location.
So my question is: Will a backup always use the service account permissions to create the .bak file or can this be overridden to another account?
BACKUP DATABASE MyDB TO DISK = N'\\MyNetwork\SQLBackupShare\MyDB.bak'
WITH NOFORMAT, NOINIT,
NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
In this example if the SQL Service account doesn't have any permission to "\\MyNetwork\SQLBackupShare\" how can i do this backup aside from doing them local and copying them.
From an agent job or SSMS - but I'm open to other options.
thx
March 3, 2016 at 11:42 am
The account that runs the backup has to have permission to run the backup and it has to have permission on where the backup is going. If you create a job inside of SQL Agent, unless you specifically specify within the job, or the step, that it's owned by a different account, it's going to run with the Agent account. The Agent account, or the account that you assign, must have access to the file location. It's two levels of permissions, but this kind of thing is done all the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2016 at 12:08 pm
Grant Fritchey (3/3/2016)
The account that runs the backup has to have permission to run the backup and it has to have permission on where the backup is going. If you create a job inside of SQL Agent, unless you specifically specify within the job, or the step, that it's owned by a different account, it's going to run with the Agent account. The Agent account, or the account that you assign, must have access to the file location. It's two levels of permissions, but this kind of thing is done all the time.
Here is my tsql inside the agent job (job owner is domain\Mysysadminacct)
EXECUTE AS LOGIN = 'domain\Mysysadminacct'
go
BACKUP DATABASE [SSIS] TO DISK = N'\\NetworkServer\sqlbackup\ssis.bak' WITH NOFORMAT, NOINIT, NAME = N'SSIS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Error:
Message
Executed as user: domain\SqlServiceAcct. Cannot open backup device '\\NetworkServer\sqlbackup\ssis.bak'. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Even being the job owner and explicitly stating "Exec As" it still runs the backup as the SQL Service account.
I'm only trying to verify that the SQL Service account MUST have access to the backup location for a backup to work. I've always operated under that assumption and just trying to verify that.
thx
March 3, 2016 at 12:13 pm
The job owner, but which account owns the step?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2016 at 12:36 pm
Grant Fritchey (3/3/2016)
The job owner, but which account owns the step?
Ok i had nothing specified for the run as user option on the advanced tab.
There is not options for exec as when doing TSQL.
I set the run as user to my AD account.
Job failed now saying I dont have access to the network location (which i do).
I gave the SQL Service AD account modify on that network directory and it worked fine.
So in my opinion the bottom line is in order to do a native sql backup the service account must have access to the backup location.
March 3, 2016 at 12:42 pm
Tom Van Harpen (3/3/2016)
Grant Fritchey (3/3/2016)
The job owner, but which account owns the step?Ok i had nothing specified for the run as user option on the advanced tab.
There is not options for exec as when doing TSQL.
I set the run as user to my AD account.
Job failed now saying I dont have access to the network location (which i do).
I gave the SQL Service AD account modify on that network directory and it worked fine.
So in my opinion the bottom line is in order to do a native sql backup the service account must have access to the backup location.
But that's not true. Something else must still be going on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2016 at 12:51 pm
OK that is weird then. I'll do some more experimenting then and see what i can figure out. Maybe i'm just missing something obvious.
March 3, 2016 at 11:18 pm
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply