January 8, 2009 at 2:14 pm
Hi,
I have 2 servers on a workgroup and I am getting access denied while performing a backup
Error:
Syste.Data.SqlClient.SqlError: Cannot open backup device \\ServerB\Backup'. Operating system error 5(Access is denied.).(Microsoft.SqlServer.Smo)
I am running SQL Server Agent as a LocalSystem account on ServerA
http://support.microsoft.com/kb/207187
According to this Microsoft article, "You cannot back up databases to a network drive if your account have not sufficient permissions to access the network drive"
Both servers are on a workgroup.
I have a local user name (same user) on both servers with administrator rights, but I keep getting the same error.
Any ideas how can I make this to work on a workgroup environment?
Thanks in advance!
January 8, 2009 at 2:18 pm
Run the backup jobs under a domain login that has the rights to both locations (the rights to run the backup and the rights to create files).
If it's actually a Windows workgroup, not a domain, you'll need to make sure to set up file sharing and test it from Explorer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2009 at 2:24 pm
GSquared ,
Thank you for the promt reply, but yes I did create file sharing; and I tested it from explorer, but same error.
January 8, 2009 at 2:31 pm
I don't think I've ever tried to run a backup from SQL onto a remote drive without having a domain to control access.
I don't think a mapped drive will work, but have you tried it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2009 at 2:33 pm
apart from suggesting not to do network backups, I seem to recollect that you need to run your sql server service account as administrator to make this work. Local system won't have rights I don't think. set the share permissions, you did set share permissions after creating the share?, to full control for everyone as an absolute test.
Best suggestions really, workgroup stuff can be tricky that way.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2009 at 2:46 pm
Change the account that SQL runs under to a local user account instead of the Local System account. For example, create a normal user account named SQLSVC, then change SQL to use that using the SQL Server Configuration Manager.
On the other server/workstation create an identical user account (with same password). Make sure the share is setup to allow that account at both the NTFS and share level permissions.
January 8, 2009 at 2:48 pm
GSquared
Yes, I tried the mapped drive!
January 8, 2009 at 3:11 pm
Todd Engen,
Thank you ver much for your reply.
It works now!
January 8, 2009 at 3:42 pm
Glad to hear you got it working!
January 18, 2009 at 2:47 am
Todd Engen thank you for the solution. It resolved exactly the same issue with:
SQL Server 2005
Standalone/non-networked laptop
Windows Vista
Running under an administraor account
I suspect there must have been a Windows update around November 2008 that changed the way in which the directories are allowed to be accessed, because prior to that there was no problem at all.
Thanks again.
Paul
January 18, 2009 at 9:35 am
Todd Engen (1/8/2009)
Change the account that SQL runs under to a local user account instead of the Local System account. For example, create a normal user account named SQLSVC, then change SQL to use that using the SQL Server Configuration Manager.On the other server/workstation create an identical user account (with same password). Make sure the share is setup to allow that account at both the NTFS and share level permissions.
this is assuming a workgroup which will use pass through authentication. for a domain run the sql account under a domain user and give that same domain user rights to the network share. Bear in mind that setting the share permissions may not be enough you will need to set NTFS permissions too on the security tab
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply