Is sysadmin absolutely required for agent service?

brosatobrosato Posts: 89 Bronze 2
edited November 21, 2008 1:50PM in SQL Backup Previous Versions
Cause if it is, that's just retarded.
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    I think we have to credit the retardness to Microsoft. They require a logon to have the SYSADMIN role or a Virtual Device Interface (backup) device cannot be created. If we cannot create a VDI device, then we cannot get access to the database backup stream from SQL Server. Seeing as we have had many LUA arguments about this already, we have produced a big, scary document outlining all of the permissions you need to give the SQL Backup Agent user here.
    · Share on Twitter
  • brosatobrosato Posts: 89 Bronze 2
    Even after giving sysadmin rights to the account I still get an error

    BACKUPTHOR: Backup - Failed
    ===========================

    Performing full backups - Failed

    This operation failed with errors.

    Backing up ASPState (full database) to:
    E:\BACKUP\FULL_(local)_ASPState_20081121_111211.sqb
    VDI error 1000: Failed to recognize the SQL Server instance name. Check that the SQL Backup service startup user has read/write rights to access the SQL Server service information. You can do this by using the "sc sdshow/sc sdset" commands.
    Memory profile
    Type Maximum Minimum Average Blk count Total
    Commit 51642368 4096 102764 8211 843796480
    Reserve 4128768 8192 48890 7354 359542784
    Free 432054272 4096 4140696 228 944078848
    Private 51642368 4096 77288 14978 1157632000
    Mapped 1536000 4096 159326 49 7806976
    Image 5808128 4096 70446 538 37900288
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Looks like a Windows rights issue. SQL Backup's startup account needs to be able to query its' own state (running, starting, stopped, etc) and these ACLs are set using the "SC" tool or in Group Policy (gpedit.msc)
    · Share on Twitter
  • brosatobrosato Posts: 89 Bronze 2
    Well, I have it working right now without the account being a memeber of the sysadmin role. What's up with that?
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Don't know. But if you had a look at the article on our KB about why this is so you would have seen a bit of Microsoft's VDI documentation extrapolated there:
    The system objects used to implement the virtual device set are secured with an access control list. This list permits access to all processes running under the account used by the primary client. Access is also permitted to processes running under the account used by Microsoft® SQL Server™, as recorded in the system services configuration.
    The server connection for SQL Server that is used to issue the BACKUP or RESTORE commands must be logged in with the sysadmin fixed server role. For more information, see Microsoft SQL Server Books Online.
    The CreateEx (and Create) calls modify the security DACL on the process handle in the client process. Because of this any other modification of the process handle must be serialized with invocation of CreateEx.
    Owing to the age of the document, something may have changed in a later version of SQL Server, so maybe I'll get our guys to double-check the VDI documentation.
    · Share on Twitter
  • brosatobrosato Posts: 89 Bronze 2
    Never mind. BUILTIN\ADMINISTRATORS got me. It doesn't work.
    · Share on Twitter
Sign In or Register to comment.