Minimum permission to backup a MS-SQL database using NetBackup

[fadace]

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

[Mouse]

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.

[maj_rif]

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. 

[fadace]

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

[Mouse]

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.

[Mouse]

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.

[fadace]

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 ) 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