Re: Unable to Backup on Network

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 05/20/04 

Date: Thu, 20 May 2004 06:32:00 -0600

You may want to keep in mind that changing the service
accounts through the services applet will not set the
necessary permissions and rights for the service account.
The correct permissions will be set if using Enterprise
Manager to change the service accounts.
But if you changes these through the services applet, you
would need to ensure the correct permissions manually as
indicated in the following article:
HOW TO: Change the SQL Server or SQL Server Agent Service
Account Without Using SQL Enterprise Manager in SQL Server
2000
http://support.microsoft.com/?id=283811

-Sue

On Thu, 20 May 2004 07:10:32 +0530, "Hari Prasad"
<hari_prasad_k@hotmail.com> wrote:

>Hi Niel,
>
>To solve the issue you have start the SQL Server service using an Domain OS
>user which got write access to the remote share.
>
>So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
>Double click and select the "Log on" option.
>There you give a Valid Domain OS user and password to start the service. Now
>stop and start the MSSQL Serevr service.
>
>Note: That domain user should have previlages in the remote share to write
>the file as well as prev. to start the SQL server.
>
>After this you try to execute the Backup database command in Query
>Analyzer:-
>
>
>Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
>init ( With init will overwrite the backup file every time)
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Niel" <nandip2k4@vippowernet.com> wrote in message
>news:uM#38JePEHA.2704@TK2MSFTNGP10.phx.gbl...
>> Thank you for a quick reply,
>> As per what you have said i checked and yes both the source and target
>> servers are in the same domain.
>>
>> Also we have given FULL CONTROL rights to the target directory.
>>
>> However i did not understand what do you mean when you say that
>> "I like to log in to the SQL server console as the service account to test
>> access rights to target resources."
>> I mean which console are you talking about? EM? and how do we test the
>> access rights to target resouces?
>> I did not understand that last sentence.
>>
>> Also i am backing up through UNC only and not through Network Mapped
>Drive.
>>
>>
>>
>> "Geoff N. Hiten" <SRDBA@Careerbuilder.com> wrote in message
>> news:e09hbAdPEHA.3328@TK2MSFTNGP09.phx.gbl...
>> > 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: Security Question
    ... You will give the clients connect rights to the server. ... Give that application role permissions to the tables. ... In your application before you run the report active the application role ... As a dept we are transitioning out of Access and into SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: Determining Users Effective Rights
    ... thanks for the reply - PERMISSIONS only works for the ... only shows the Roles' rights. ... >> I am using Windows 2000 and SQL Server 2000 in Windows ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie question on SQL connection
    ... > Let me educate you about SQL Server security. ... granting permissions to these accounts in SQL ... > maintain its own user accounts, which are separate from domain user ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: "Requested registry access is not allowed" and Web Service cal
    ... web service is called from a desktop application that requires a user ... workstation, they have less rights than me, and the program as well as ... sql server would take on the rights of that user, ... procesudes - no generic accounts that allow access to all stored procs. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Login with no Fixed Server Role and DB Role can stop SQL Agent Service?
    ... Yup - somehow the user has windows security rights to control services - we ... This posting is provided "AS IS" with no warranties, and confers no rights. ... >> a Window 2000 Login with Domain User default permissions, ... Forget about SQL Server for the moment. ...
    (microsoft.public.sqlserver.security)