Backing up to a network share from SQL Server 2005

Hello-
I'm trying to back my database up to a network share. Using the same account
that I'm logged into SQL Server Management Studio with, I'm able to add and
delete files on the network share just fine. When I try to do a backup to the
same location, I get this message:
System.Data.SqlClient.SqlError: Cannot open backup device
'\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
denied.). (Microsoft.SqlServer.Smo)
Does anyone know why this might be or how best to troubleshoot it?
Regards-
EricIt is the SQL Server service account that need permissions on the share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> Hello-
> I'm trying to back my database up to a network share. Using the same account
> that I'm logged into SQL Server Management Studio with, I'm able to add and
> delete files on the network share just fine. When I try to do a backup to the
> same location, I get this message:
> System.Data.SqlClient.SqlError: Cannot open backup device
> '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> denied.). (Microsoft.SqlServer.Smo)
> Does anyone know why this might be or how best to troubleshoot it?
> Regards-
> Eric|||Tibor-
Thank you. Exactly the info I was looking for. One more question. I have the
SQL Server Agent running under the local system account. Do you know if it is
possible to give the local system account permissions on a network share? If
not, I can either backup to a local file and then copy it onto the network
share, or I can change the service the agent runs under. Any recommendations
or best practices?
Regards-
Eric
"Tibor Karaszi" wrote:
> It is the SQL Server service account that need permissions on the share.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> > Hello-
> >
> > I'm trying to back my database up to a network share. Using the same account
> > that I'm logged into SQL Server Management Studio with, I'm able to add and
> > delete files on the network share just fine. When I try to do a backup to the
> > same location, I get this message:
> >
> > System.Data.SqlClient.SqlError: Cannot open backup device
> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> > denied.). (Microsoft.SqlServer.Smo)
> >
> > Does anyone know why this might be or how best to troubleshoot it?
> >
> > Regards-
> > Eric
>
>|||It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
you probably want to talk to some Windows experts on this. I think you can elaborate with the
machine account and get LocalSystem to work, but I've never done it. I always use a domain account
when I need for my SQL Servers to reach outside the box.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
> Tibor-
> Thank you. Exactly the info I was looking for. One more question. I have the
> SQL Server Agent running under the local system account. Do you know if it is
> possible to give the local system account permissions on a network share? If
> not, I can either backup to a local file and then copy it onto the network
> share, or I can change the service the agent runs under. Any recommendations
> or best practices?
> Regards-
> Eric
> "Tibor Karaszi" wrote:
>> It is the SQL Server service account that need permissions on the share.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
>> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
>> > Hello-
>> >
>> > I'm trying to back my database up to a network share. Using the same account
>> > that I'm logged into SQL Server Management Studio with, I'm able to add and
>> > delete files on the network share just fine. When I try to do a backup to the
>> > same location, I get this message:
>> >
>> > System.Data.SqlClient.SqlError: Cannot open backup device
>> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
>> > denied.). (Microsoft.SqlServer.Smo)
>> >
>> > Does anyone know why this might be or how best to troubleshoot it?
>> >
>> > Regards-
>> > Eric
>>|||Tibor-
Are you sure there isn't a SQL Server setting that might be preventing me
from backing up to a network share? I set up a domain account for the Sql
Server Agent to run under and it still fails. I set permissions on the
network share to give Everyone full control. I also tried running the TSQL
for the backup via a query (logged in as a domain admin) and I still get the
same access denied error.
Regards-
Eric
"Tibor Karaszi" wrote:
> It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
> you probably want to talk to some Windows experts on this. I think you can elaborate with the
> machine account and get LocalSystem to work, but I've never done it. I always use a domain account
> when I need for my SQL Servers to reach outside the box.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
> > Tibor-
> >
> > Thank you. Exactly the info I was looking for. One more question. I have the
> > SQL Server Agent running under the local system account. Do you know if it is
> > possible to give the local system account permissions on a network share? If
> > not, I can either backup to a local file and then copy it onto the network
> > share, or I can change the service the agent runs under. Any recommendations
> > or best practices?
> >
> > Regards-
> > Eric
> >
> > "Tibor Karaszi" wrote:
> >
> >> It is the SQL Server service account that need permissions on the share.
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> >> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> >> > Hello-
> >> >
> >> > I'm trying to back my database up to a network share. Using the same account
> >> > that I'm logged into SQL Server Management Studio with, I'm able to add and
> >> > delete files on the network share just fine. When I try to do a backup to the
> >> > same location, I get this message:
> >> >
> >> > System.Data.SqlClient.SqlError: Cannot open backup device
> >> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> >> > denied.). (Microsoft.SqlServer.Smo)
> >> >
> >> > Does anyone know why this might be or how best to troubleshoot it?
> >> >
> >> > Regards-
> >> > Eric
> >>
> >>
> >>
>
>|||I think I've got this. It's a strage file share permission problem. I"ll move
this over to a Windows Server discussion group. Thanks for your help.
Regards-
Eric
"Eric Marthinsen" wrote:
> Tibor-
> Are you sure there isn't a SQL Server setting that might be preventing me
> from backing up to a network share? I set up a domain account for the Sql
> Server Agent to run under and it still fails. I set permissions on the
> network share to give Everyone full control. I also tried running the TSQL
> for the backup via a query (logged in as a domain admin) and I still get the
> same access denied error.
> Regards-
> Eric
> "Tibor Karaszi" wrote:
> > It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
> > you probably want to talk to some Windows experts on this. I think you can elaborate with the
> > machine account and get LocalSystem to work, but I've never done it. I always use a domain account
> > when I need for my SQL Servers to reach outside the box.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> > news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
> > > Tibor-
> > >
> > > Thank you. Exactly the info I was looking for. One more question. I have the
> > > SQL Server Agent running under the local system account. Do you know if it is
> > > possible to give the local system account permissions on a network share? If
> > > not, I can either backup to a local file and then copy it onto the network
> > > share, or I can change the service the agent runs under. Any recommendations
> > > or best practices?
> > >
> > > Regards-
> > > Eric
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> It is the SQL Server service account that need permissions on the share.
> > >>
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> > >> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> > >> > Hello-
> > >> >
> > >> > I'm trying to back my database up to a network share. Using the same account
> > >> > that I'm logged into SQL Server Management Studio with, I'm able to add and
> > >> > delete files on the network share just fine. When I try to do a backup to the
> > >> > same location, I get this message:
> > >> >
> > >> > System.Data.SqlClient.SqlError: Cannot open backup device
> > >> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> > >> > denied.). (Microsoft.SqlServer.Smo)
> > >> >
> > >> > Does anyone know why this might be or how best to troubleshoot it?
> > >> >
> > >> > Regards-
> > >> > Eric
> > >>
> > >>
> > >>
> >
> >
> >|||On Tue, 28 Nov 2006 16:10:02 -0800, Eric Marthinsen
<EricMarthinsen@.discussions.microsoft.com> wrote:
>Tibor-
>Are you sure there isn't a SQL Server setting that might be preventing me
>from backing up to a network share? I set up a domain account for the Sql
>Server Agent to run under and it still fails. I set permissions on the
>network share to give Everyone full control. I also tried running the TSQL
>for the backup via a query (logged in as a domain admin) and I still get the
>same access denied error.
>Regards-
> Eric
The Agent Service is not performing the backup, it is directing SQL
Server to back itself up. As was said in previous messages, the
account that needs the rights is the account the SQL Server service is
running under.
When I was up to date on this stuff a few years ago the only way to
allow the local system account access to a network resource amounted
to removing all restrictions and security on the target system. I
doubt it is any safer today, in which case you should look into
creating a proper account for the SQL Server service.
Roy Harvey
Beacon Falls, CT