Remote SQL Backup getting Operating system error 5(Access is denied.).

I created a remote backup device with no problem using the following script.

USE master;
GO
EXEC sp_addumpdevice 'disk', 'networkdevice',
    '\\SQLADMIN\REMOTESQLBAKUPS\AdventureWorks2012.bak';

however, when I tried to run the following backup script I am getting the error below.


BACKUP DATABASE AdventureWorks2012
   TO DISK = '\\SQLSERVER2\REMOTESQLBAKUPS\AdventureWorks2012.bak';
GO

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\SQLSERVER2\REMOTESQLBAKUPS\AdventureWorks2012.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

When I map to this shared folder from the source SQLSERVER1 to the target (\\SQLSERVER2\REMOTESQLBAKUPS) I am able to copy and create files with no problems.

I made sure that the shared folder on \\SQLSERVER2\REMOTESQLBAKUPS is using the same domain account (DOMAIN\ACCOUNT) with full permissions.  So both SQLSERVER1 and SQLSERVER2 SQL server agent are both using the same account as well.

What am I doing wrong?
RayManAaaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayManAaaAuthor Commented:
OK, this is an update to my last post.  I was able to get the remote backup to work by modifying the NTFS permissions of the shared folder on SQLSERVER2.  I gave "Everyone" group full control, then ran my backup script which ran without any problems.

I am split here and would like to hear what everyone has to say about this.  I really want this to work without using the "Everyone" group.  Also, I am not sure why this is working, as I said in my original post I made sure to give my (DOMAIN\ACCOUNT) full control to the shared folder.

This would lead me to believe that the account that I am assuming that I am connecting with is not the same account that my SQLSERVER1 server is using to connect to SQLSERVER2.  Is there a way that I can tell which account it is using at the time when I run the SQL backup script?

Please keep in mind that I am login with the same (DOMAIN\ACCOUNT) on SQLSERVER1 which has local admin and SQL SA permissions.
0
Randy Knight, MCMPrincipal ConsultantCommented:
The SQL Server service account is the security context the backup will run under, not the Agent.  You need the account that the SQL Service is running under to have permissions to the share.
0
RayManAaaAuthor Commented:
I am not sure if I follow you.

The SQL service account that I am using is the same account that the SQL agent is using in the "log on as" settings.  I made sure that this account, lets call it (domain\sqlsecurity) has local admin on SQLSERVER2.  This account also has "owner" permission level set in the  "network file and folder sharing" configuration tab.  

To clarify, when I say using, I am logging into the SQLSERVER1 with the (domain\sqlsecurity) account and starting SSMS.  Once SSMS has connected to SQLSERVER1, I try to run the script above.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Randy Knight, MCMPrincipal ConsultantCommented:
The account you log in as is not the issue.  You need that account to be the SQL Server service account that is set in SQL Server Configuration Manager or the Services control panel.  Your account has to have permissions to execute the BACKUP command.  The service itself needs to be able to write the backup to the share.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RayManAaaAuthor Commented:
Yes,

The (domain\sqlsecurity) is configured to run both the SQL engine and agent, this account has local admin on both servers and SA permissions on both SQL instances. This account also has full control of the shared folder as well.
0
Randy Knight, MCMPrincipal ConsultantCommented:
If you're getting the Access Denied error, the permissions are not being applied somewhere.  Perhaps there is an explicit deny somewhere?  You might try using AccessChk from sysinternals to see the effective permissions for that account on the share.

http://technet.microsoft.com/en-us/sysinternals/bb664922
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Legacy OS

From novice to tech pro — start learning today.