SQL Server 2000, LocalSystem, and Volume Shadow Backups
We have a customer who tries to be strict about SQL Server 2000 security. They had decided to remove Local System (NT AUTHORITY\SYSTEM) from the local sysadmin role on their production server. This was OK until moving to Windows Server 2003, Enterprise Edition on their SQL Server 2000, Enterprise Edition cluster. At that point, backups started to break. Because the Local System account is typically in the sysadmin role (in fact, I had never seen it removed), this is not something that comes up very often. However, it is nonetheless an issue; backups of any volume with SQL data, even if not backing up SQL data, lead to an error:
Event Type: Error
Event Source: VSS
Event Category: None
Event ID: 6013
User: N/A
Description:
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e09. SQLSTATE: 42000, Native Error: 229
Error state: 5, Severity: 14
Source: Microsoft OLE DB Provider for SQL Server
Error message: SELECT permission denied on object 'sysaltfiles', database 'master', owner 'dbo'.
The natural thought next was to assign db_backupoperator to the Local System account, and grant read access to the master database. However, that is not good enough, because next you get this:
Event Type: Error
Description:
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft OLE DB Provider for SQL Server
Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3265
Error state: 1, Severity: 16
Source: Microsoft OLE DB Provider for SQL Server
Error message: The login has insufficient authority. Membership of the sysadmin role is required to use VIRTUAL_DEVICE with BACKUP or RESTORE
So, this leads to the next question - how do we make this work? Well, here's the answer - you can't! Here's the appropriate part of the mail from MS regarding this issue (call SRX050418600880):
When any requestor uses SQL Writer, it has to login to SQL Server to do some work including taking SNAPSHOT BACKUP to a virtual device. We can see in the profiler trace that this call is what is generating the above message.
BACKUP DATABASE [master] TO VIRTUAL_DEVICE='{96CDFB69-5238-419C-825B-FCD9C5AC0DCF}1' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024
This is done using VDI or virtual device interface which veritas seems to be using in their backup application. When using VDI the call to Initialize the Backupdeviceset checks to see if the login is sysadmin if not it raises the above error.
For a normal backup it is not required for the user to be a sysadmin, but in the case of VDI, there is a requirement that the login be a sysadmin. A Design Change Request (DCR) has already been filed to change this behavior so that a non-sa can take VDI backups in SQL Server 2005
At this time this is by design and it is required that the Local System Account have sysadmin privileges in SQL Server for the backup functionality to work properly.
Notice that there is no answer for SQL 2000, but it's enough of a recognized issue to theoretically make it to SQL 2005. We'll see. For now, here's hoping someone looking for this same problem will be able to find this post, or if they call MS the PSS rep will find the same case.