GBGY


We currently built a new SQL2005 server and have serveral sentitive Payroll database hosted in it. I managed to seperate some roles to various users to prohibit them to direct access the data but can access it via front-end application.

The problem is I setup a backup operator with the following rights

Server Roles -- Public

User Mapping

User Mapped to this login / Database membership for

PayrollDataBase_1 BackupOperatorName

db_backupoperator / db_denydatareader / db_denydatawriter / public

However, I try to login using the 'BackupOperatorName' in my workstation and Backup the PayllrollDataBase_1 to a server shared path says E:\SqlDataBacup\PayrollDataBase_1.BAK. Afterward, I copy it over the network back to my workstation and RESTORE in back to my local SQL2005 instant using the local SQL2005 SA user. All data CAN BE browse

Anyone please help to post how to restrict the Backup operator can only Backup the database but cannot restore to its local SQL2005 instant

Many Thanks in advance




Re: SQL2005--How to prohibit BackupOperator to restore the database to local workstation server

Laurentiu Cristofor


You should prevent the account of the backup operator from having read access to any locations to which the service account has write access (backups are taken using the service account credentials). You should also prevent the backup operator from having access to any functionality within SQL Server that would allow him to read data from disk (because that would allow him to read as the service account).

If this cannot be achieved for whatever reason, you may want to use encryption, so even if the database is copied, its sensitive data cannot be decrypted.

Thanks

Laurentiu







Re: SQL2005--How to prohibit BackupOperator to restore the database to local workstation server

GBGY

Please help me out how to prevent the BackupOperator by copying the BAK file and restore to his own SQL2005 local server in order to view every data inside the database

I followed some articles in the internet those demo. the encrypt a column in the table, however, I am still in the dark as I cannot understand after I encrypt the column but hasn't change the front-end application (which we cannot change) and it still gonna to work

Can anyone help me out please

Thanks






Re: SQL2005--How to prohibit BackupOperator to restore the database to local workstation server

GBGY

Thanks Laurentiu

We setup the SQL2005 using the 'SQL Server and Windows Authentication' mode and create a dedicated Operator (say BBB) to do the Backup which is a SQL login account (Say the Operator Windows 2003 account is AAA). You mean we need to setup the AAA can only have write access to the SQL Server Backup location or the SQL login BBB

Thanks





Re: SQL2005--How to prohibit BackupOperator to restore the database to local workstation server

Michael Hotek

You prevent the backup operator from copying the backup files to another machine and then restoring them to rip off your data, by not allowing the backup operator access to the files. You can grant the backup operator the authority to backup a database. You do not have to grant the backup operator the authority to access the directory that you store your backups in.






Re: SQL2005--How to prohibit BackupOperator to restore the database to local workstation server

Laurentiu Cristofor

AAA should not have any permission on the backup location. BBB should have no way of accessing that location (it cannot have permissions, but it shouldn't have access to functionality that would allow him to read from that location). Only the service account needs to have write access to the backup location (from BOL: "SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions."). So, to make things work, you only need to worry about the service account. To prevent unwanted access, deny permissions on the backup location to the AAA account and prevent BBB from being able to access it.

You could also encapsulate the backup commands in procedures that restrict the location where you can make the backup. You can sign these and then just grant permissions to execute them to the BBB account. These could relieve you from making BBB a member of the db_backupoperator role. See ADD SIGNATURE on Books Online. I also have some signing examples on my blog. If you want tighter control, then you should use signatures.

Thanks

Laurentiu






Re: SQL2005--How to prohibit BackupOperator to restore the database to local workstation server

Laurentiu Cristofor

GBGY - please don't open separate threads on the same issue. I merged your newer thread into this one.

Thanks

Laurentiu