Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Protecting the SQL Server Backup folder


By:   |   Last Updated: 2012-09-19   |   Comments (3)   |   Related Tips: More > Security

Problem

I want to backup my SQL Server databases to a folder, but I want to minimize who has access to the folder. In other words, I want to make sure that members of the Windows Local Administrators group don't get to the backups without intentionally trying to bypass the security. How do I do that?  Check out this tip to learn more.

Solution

If you look at the permission for the BACKUPS folder that the Microsoft SQL Server install creates, you've probably noted that it has permissions where members of the Administrators group has full control, meaning they can do anything they want. Actually, this is true of most every location, including a folder I just created off the root of the C:\ drive.

Advanced Security Settings for a Windows Directory

In a lot of cases the Windows Local Administrators group has inherited the permissions from a higher level folder. The solution is to break inheritance and set our own permissions. Without breaking inheritance we can only add permissions to a file or folder. We can't remove permissions, so this is a necessary step. However, to make sure you don't lock yourself out, choose to copy the existing inheritable permissions.

Windows Security - Click Add to convert and add inherited parent permissions as explicit permissions on this object

Before you remove any permissions, the first step I'd take is to ensure you have permission to get back and manage the folder. For instance, if all DBAs should be able to get to the appropriate folder, add the appropriate Windows group for the DBAs and give it Full Control. This allows you to modify the folder and any files contained in it as well as manage the permissions. It's this last aspect of Full Control you'll find essential. I typically grant this permission, then click OK to confirm and only then do I start removing or reducing permissions.

When I go to remove permissions, I tend to automatically eliminate the following accounts or groups:

  • SYSTEM
  • Authenticated Users
  • Users

Note I didn't strike the Administrators group. Instead, I tend to reduce permissions.  I still grant the local Windows Administrators the ability to see what's in the folder. Unless the local security policy has been changed, they can backup the files and folders regardless of permissions, but this doesn't typically give them the ability to view what's in the folder without the use of a 3rd party product. When it comes to disk space issues and the like, it can often be helpful for an administrator to be able to see the files. We can accomplish this by granting the following permission: List folder contents. This gives them the ability to see the files, but not read from them.

Backups Folder Properties

Most importantly, don't forget to give Microsoft SQL Server access. It will need Modify access for the folder. This gives SQL Server the ability to read files, write to them, modify them, and delete them. In SQL Server 2005-2008R2, there will be a SQL Server User group corresponding to the instance name. In SQL Server 2012 the access will be configured based on the service's virtual account name. Check the appropriate version of Books Online for this. Here's the pattern for SQL Server 2008 and 2008R2:

Type of Instance Pattern for Group Name
Default Instance SQLServerMSSQLUser$ComputerName$MSSQLSERVER
Named Instance SQLServerMSSQLUser$ComputerName$InstanceName

If you don't have the appropriate access, you'll get the following error trying to run a backup through SQL Server Management Studio:

Access is denied error message from SQL Server Management Studio when you do not have permissions to the backup directory

If you get this error, you're likely missing the Modify permission for SQL Server.

Assign the Modify permission for SQL Server service account on the Backup Folder

And that's all there is to it. While a member of the Windows Local Administrators group could still get in, he or she would have to intentionally seek to bypass security. You can't completely prevent an administrator from gaining access, however, by making it difficult you keep out the merely curious. That's the intent of altering file and folder permissions like we did here.

Next Steps


Last Updated: 2012-09-19


next webcast button


next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 28, 2013 - 9:31:54 AM - opc.three Back To Top

Adding backup passwords has been deprecated for a while and was removed in the new version. Per Books Online:

 

Beginning with SQL Server 2012 the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. It is still possible to restore backups created with passwords.


Wednesday, September 19, 2012 - 9:41:58 PM - K. Brian Kelley Back To Top

Sonomarik, it depends. If you're talking about a 3rd party system that pickups the backups and then writes them encrypted to tape, then in order to do the restore, they have to go into the system. And then you have your audit trail.

The point here is to try and inhibit the merely curious or lesser skilled system administrator from grabbing the backups at rest on disk. A skilled system administrator, by nature of his or her rights is going to be able to bypass the controls, but will probably leave tracks in doing so.


Wednesday, September 19, 2012 - 6:02:47 PM - sonomarik Back To Top

I must ask that this is a strange request.  Admins, since they can backup up the folder/file can also restore it to another location and therefore unless encrypted can view its data/contents too.

the security towards the administrators seems moot.


Learn more about SQL Server tools