Re: backing up to network share issue.

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/01/04 

Date: Tue, 1 Jun 2004 21:44:14 +0200

Jim,

You are on the spot, but for one thing (or I might just have misread you). SQL Server does not do
impersonation when it accesses the backup file on behalf of the SQL Server uses that executed the backup
command. If that was the case, no SQL Server login could perform a backup (as an SQL Server account doesn't
exist in the Windows environment). For most operations (the one exception are linked servers (*)), there is no
impersonation going on, SQL Server just uses the SQL Server service account when accessing the backup share.

One other exception is When Agent executed TSQL command (SETUSER if not sysadmin) or executed CmdExec or
ActiveScript jobsteps (Proxy account if not sysadmin). 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Young" <thorium48@hotmail.com> wrote in message news:%23hoh%231ASEHA.1388@TK2MSFTNGP09.phx.gbl...
> >In this scenario how can I assing the rights to my login to access the
> network shares.
>
> As Tibor stated, this is not an SQL Server issue, it is a network
> permissions issue. The SQL login that is accessing the MSDE instance is not
> a factor in whether the process doing the backup has the correct permissions
> or not. Every process running on a computer that is using the Windows NT
> architechture (NT/2000/2003/XP) runs with a particular security profile of a
> user account, either a local or network account. The process doing the
> backup to a network share must have write permission to the share in order
> for that backup to be successful. If the backup is run directly by a user
> (the user logged into the computer or domain) then the backup process runs
> with the security context of that user. If the backup runs as a scheduled
> job running under SQL Server Agent then the backup runs under the security
> context of the SQL Server Agent process, which is by default the SYSTEM
> account. The SYSTEM account has no access to any network resources or any
> resources beyound the local computer that the process is running on.
> Depending on how the backup job is being run, you will need to give write
> permissions to the user account running the backup. In the case of the
> backup being a scheduled job, you will need to run SQL Server Agent using a
> network account that has write permissions to the network share.
>
> Jim
>
>
> "whynot" <anonymous@discussions.microsoft.com> wrote in message
> news:6EAD66BF-F419-4A38-A055-5B5C96B08329@microsoft.com...
> > Thanks Tibor for the reply.  My application has 1 custom login mapped to 1
> user, for e.g. MyAppUser (same name for both user and login).  Also my
> application has just 1 custom database.  How can I assign this user/login
> the rights to access the network shares on my client's machine.  Have to
> make this as seemless as possible as clients will be small office users who
> will not have database administrators and are not database professionals.
> So my application has to make them feel as if there is literally nothing
> called SQL Server involved, means that they will not be expected to have any
> technical knowhow.  In this scenario how can I assing the rights to my login
> to access the network shares.
> >
> > Thank you so much.
>
>

Relevant Pages

  • Re: Unable to Backup on Network
    ... service accoutn from the 'Local System' account to a specific account. ... I like to log in to the SQL server console as the service account to test ... > 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. ...
    (microsoft.public.sqlserver.security)
  • 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: 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: 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: SQLServer cant backup remotely (on Workgroups) unless Administrator?
    ... I removed SQLAgent from the equation by backing up directly from Query ... > SQL Server Agent must be started under stronger account, ... >>SQL backup across the network to an NT Server. ...
    (microsoft.public.sqlserver.security)