Post reply

Restoring database between servers using SQL Agent proxy account

  • January 19, 2018 at 9:30 am

    #341830

    Requirement:
    Restore database from server A to server B using an overnight SQL Agent job. In order to lockdown access to the backup directory, the backup location will be a fileshare on server A with permissions restricted to a domain account (DOMAIN\DBRestore_svc).

    Set up:
    My agent service is running as NT SERVICE\SQLSERVERAGENT on server B so I have set up a credential and proxy mapped to the DOMAIN\DBRestore_svc account and am using this proxy to run the agent job to avoid having to give read permissions to the Server B machine account. I am running the agent job using SQLCMD as it is not possible to run T-SQL commands using a proxy.

    NTFS and share permissions have been granted to the DOMAIN\DBRestore_svc account:

    The full command is as follows:
    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "RESTORE DATABASE XXXX FROM DISK = '\\XXXX\NonProductionCopy\XXXX.bak' WITH NORECOVERY, REPLACE, MOVE 'XXXX'TO N'XXXX.mdf', MOVE 'XXXX' TO N'XXXX.ldf'" -b

    Problem:
    When I execute the SQL Agent job it fails with an access denied error:

    Message
    Executed as user: DOMAIN\DBRestore_svc. Msg 3201, Level 16, State 2, Server XXXX, Line 1  Cannot open backup device '\\XXXX\NonProductionCopy\XXXX.bak'. Operating system error 5(Access is denied.).  Msg 3013, Level 16, State 1, Server XXXX, Line 1  RESTORE DATABASE is terminating abnormally.  Process Exit Code 1.  The step failed.


    What is strange is when I give the machine account (DOMAIN\SERVERNAME$) access to the share, the restore command is able to run. My understanding was that using the proxy should allow the command to run as the domain account and so bypass the need for the machine account to have access. Is that not the case?

    Thanks
    Mike


     

  • January 22, 2018 at 12:35 pm

    #1976789

    are you able to map the drive ?

    net use x:\\server\share /user:DOMAIN\DBRestore_svc password

  • January 22, 2018 at 12:36 pm

    #1976790

    try this

    sp_configure 'xp_cmdshell',1 -- you need this to map network drive
    reconfigure
    go
    exec xp_cmdshell 'net use x:\\server\share /user:DOMAIN\DBRestore_svc password' --map thur sql so drive is not visable to others expect sql server
    go
    sp_configure 'xp_cmdshell',1 -- you dont want to keep it open all the time
    go
    reconfigure
    go

    restore mydb from disk ='x:\database.bak'

  • January 23, 2018 at 9:24 am

    #1976930

    That's done it, thanks!

    The smiley face emoji threw me for a bit until I looked up the net use syntax!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply