Re: Unable to Backup on Network

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 05/19/04 

Date: Wed, 19 May 2004 14:35:48 -0400

Sounds like a security context issue. You are correct in changing the SQL
service accoutn from the 'Local System' account to a specific account.
There are a few more steps, though.
First, the service account should be a domain-level account. Both the
source and target servers should be in the same domain.
Second, the account will need 'FULL CONTROL" rights to the target directory.
I like to log in to the SQL server console as the service account to test
access rights to target resources.
Finally, you must use UNC names for consistant target resolution. Shares
may or may not be correctly seen by the SQL server. 

-- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Niel" <nandip2k4@vippowernet.com> wrote in message
news:eR9MZrcPEHA.252@TK2MSFTNGP10.phx.gbl...
> Hello folks,
>  I think this issue must have been talked before and i am sorry to raise
it
> again but after having read at number of places i could not find the
> solution and so i am posting the topic again. Hope i get some help.
> Now i want to backup my database on a network.
> The operating system on which my SQL Server 2000 is there is Windows 2000
> Server while i want to take the backup on another computer on network
which
> also has same operating system.
> Now whenever i try to take a backup on network with command
> Backup Database databasename to Disk='\\backup\tmp\networkdatabase.dat'
> i get following error in the Query Analyzer
>
> Server: Msg 3201, Level 16, State 1, Line 1
> Cannot open backup device '\\backup\tmp\networkdatabase.dat'. Device error
> or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
>
> When i check the error in the sql server error log this is what it says
>
> BackupDiskFile::CreateMedia: Backup device
> '\\backup\tmp\networkdatabase.dat' failed to create. Operating system
error
> = 1326(Logon failure: unknown user name or bad password.).
>
> Now as i read at various place they say that to enable backup on network
you
> need to run the MSSQLSERVER service run under particular account that has
> rights to take network backup.
> So i changes the "Log On" option from "Local System Account"  to "This
> Account" in which i add the account that has the rights to take the backup
> on the network.
> After doing this is restarted the server and then issued the command for
the
> backup.
> But still i am getting the same error.
>
> Also when i type in the \\backup\tmp in the run (Start - Run) then i am
able
> to access the server without asking for any username or password. Also i
am
> able to
> copy, write directly on that network server from SQL server but when it
> comes to backup up from Query Analyzer, it gives me error.
> Also i am connecting to QA under the "Windows Authentication" mode only so
> even that is not a problem
>
> If any one has any idea as to where i am wrong or what steps should i
> take/check to ensure that i am not missing anything please let me know.
> Any help would be appreciated.
>
> Thank you
>
>

Relevant Pages

  • Re: Trusted SQL Connections & NT AUTHORITYNETWORK SERVICE
    ... SYSTEM account in terms of the credentials it uses on the network. ... hitting a SQL Server on the same machine as the web app. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: User authentication
    ... There are 2 SQL Server 2005 ... 1 SQL Server 2000 installed on another server ... Windows account instead to run backup jobs. ...
    (microsoft.public.sqlserver.clients)
  • Re: backing up to network share issue.
    ... I guess I was confused by your later text where I read it as if you stated that it would be the Agent account ... Tibor Karaszi, SQL Server MVP ... >> impersonation when it accesses the backup file on behalf of the SQL Server ... >>> As Tibor stated, this is not an SQL Server issue, it is a network ...
    (microsoft.public.sqlserver.msde)
  • Re: Transaction log backup
    ... Main plan is totally reworked in SQL Server 2005. ... > to issue a BACKUP log to a db in SIMPLE recovery makes zero sense. ... >> account is the security context for jobs, ...
    (microsoft.public.sqlserver.server)
  • Re: backing up to network share issue.
    ... impersonation when it accesses the backup file on behalf of the SQL Server uses that executed the backup ... SQL Server just uses the SQL Server service account when accessing the backup share. ... > As Tibor stated, this is not an SQL Server issue, it is a network ...
    (microsoft.public.sqlserver.msde)