Forum Discussion

fadace's avatar
fadace
Level 2
15 years ago

Minimum permission to backup a MS-SQL database using NetBackup

 

Any bad-developed application is avoiding the permission issues using the most intrusive role. 

In a security strategy, the main goal is to assign the minimum necessary roles for each login.

The RDBMS MS-SQL Server allows to maintaing several specific databases in one specific instance, with a correct permission granularity.

To maintain this granularity, SQL Server 2005 splited the system admin role in several less intrusive server roles and  db roles.

Now, a backup operator is able to backup a database without accessing to the data of this database. The db_backupoperator database role is only necessary. The list of the databases is available through the public permission in the master..sysdatabases system table.

In the document "Veritas NetBackup™ for Microsoft SQL Server Administrator’s Guide", the permission issue is evasive :

"When the NetBackup scheduler is used, backup operations are launched from a daemon process called the NetBackup Client Service. You must ensure that the Client Service has SQL Server privileges to perform backup and restore operations. If you are using integrated security, then you must ensure that the NetBackup Client Service specifies a Windows account that has been granted SQL Server administrative privileges."

In the technote http://www.symantec.com/docs/TECH43512, you're specifying 

"Any logon that requires permissions to perform backup or restore operations should be provided membership in the following SQL Server roles:

Server Role : sysadmin
DB role : db_backupoperator, dbo_owner
Permissions required for performing restore -
Server role : sysadmin, dbcreator
DB role : db_owner"

My question  : do you really understand something about the MS-SQL permission ?

Why do you need the sysadmin permission ? 

If it's really necessary, why do you need the dbcreator (included in the sysadmin) ? Why the db_backupoperator and the db_owner ?

In fact, only the db role db_backupoperator is necessary, in a MS-SQL point of view. 

We experimented some hard failure on production applying the sysadmin permission in the Netbackup Logins (client VaultAdmin) : a modification in the sysusers tables is done, replacing the db_owner database role with and changing the db owner to an non-existent dbo user (technically, the dbo'sysusers.sid was replace on each database from 0x01 to the vaultadmin'sid.)

Did you plan to implement a correct security in a future version of your product ? Did you already provide a patch for this issue ?

 

Thanks in advance for your answer.

Fadace

 

 

  • Why don't to ask Microsoft why all these permissions required for their VDI interface? NBU is nothing else than a VDI client, as many others, SQL Management Studio to name just one.

  • Hi,

    Normally DBA people raise these detailed questions and some time these are really awful. As some one has already suggested to contact microsoft. i do endorse him.

    All backup softwares like NetBackup and Backup Exec take backup with application's supported API and follows the backup requirements of API. if you are chaning in security settings i believe that would be according to microsoft recommendations. 

  • Hi,

    Thanks for your feedbacks.

    The problem is not on the Microsoft side (for once ;-))

    Microsoft SQL Server is providing the correct permissions/strategy since v.2005 : the db_backupoperator database role is technically enough to execute the backups.

    Source : http://msdn.microsoft.com/en-us/library/ms186865.aspx

    Unfortunately, NetBackup is requesting the sysadmin permission, and I don't understand its technical reason.

    One of the reason should be that Symantec didn't implement the new behavior of the MS-SQL2005 permission strategy in into Netbackup.

    I wish to know which version of Netbackup is using these new roles, replacing the intrusive sysadmin. 

    Reproduction:

     

    CREATE LOGIN [backuptest] WITH PASSWORD=N'testtestŸ', DEFAULT_DATABASE=[master], CHECK_POLICY=OFF
    GO
    USE [AnyDB]
    GO
    CREATE USER [backuptest] FOR LOGIN [backuptest] WITH DEFAULT_SCHEMA=[dbo];
    GO
    EXEC sp_addrolemember N'db_backupoperator', N'backuptest'
    GO
    exit
     
    sqlcmd -Ubackuptest -Ptesttest 
    select * from master..sysdatabses 
    GO
    -- => works due to the guest access on master
     
    BACKUP DATABASE AnyDB to disk='C:\temp\AnyDB.bak'
    GO
    Processed 480 pages for database 'AnyDB', file 'justice' on file 2.
    Processed 1 pages for database 'AnyDB', file 'justice_log' on file 2.
    BACKUP DATABASE successfully processed 481 pages in 1.194 seconds (3.300 MB/sec).
    -- => works fine with minimum permission
     
    Using Netbackup MS SQL Client
     
    At startup => Please select login parameters from the "SQL Server connection properties" window.
    Using then backuptest => Connection error
  • Well, Symantec has a reference to a particular Microsoft (not Symantec) article that specifies the permissions required for VDI operations. If you haven't read the technote you've cited above, the link is the following http://technet.microsoft.com/sv-se/library/cc966495%28en-us%29.aspx#E0EB0AA

    According to that link (from microsoft.com), backup software should have the following permissions:

    ===

    Permissions Required for Backup and Restore

    Any logon that requires permissions to perform backup or restore operations should be provided membership in the following SQL Server roles:


    Server Role : sysadmin
    DB role : db_backupoperator, dbo_owner
    Permissions required for performing restore -
    Server role : sysadmin, dbcreator
    DB role : db_owner

    ====

     

    It does not seem you understand what is your vendor requesting from Symantec to comply with.

  • There is one thing that DBAs and security do not care about, but a software development company does, it's a backward compatibility. If your latest and greatest sql backup interface does not work the same way between different versions, it is a no brainer that any normal backup vendor will only support that API that works for all versions.

  • Hello,

    Please, you're mixing 2 different issues. These issues are quite linked, but in a security point of view, they're really different.

     The backup step

    This is a day-to-day (or minutes2minutes enlightened) task, only requesting the db_backupoperator role

    The restore step

    this is an emergency issue requesting more roles/privileges 

    1. sysadmin to be able to kill the existing connexions in the database to restore it (because exlusive access is necessary) + dbcreator, to be able to restore a databasem replacing the old one
    2. (optionaly) create database role fi the database doesn't already exist

     

    So my question is only regarding the backup step. 

    In a secure environment, the restore procedure provide the more intrusive roles to the Netbackup user, but only during the restore process.

    Backward compatibility is not the current issue. As specified above, Netbackup is using the MS-SQL API, so I assume that it's managing its backup/restore processes according the RDBMS version. I already noticed that it's a modern tool, able to apply the new behaviour of the RDBMS (verification with dbcc, Point in time recovery, etc)... why not in the permissions layer ?

    Thanks in advance for any interessting feedback