8

Background

Users who cannot be stripped from server administrator rights tend to forget that hell will break lose when they save database backups (.bak) on C: and fill the drive.

Question

Is it possible to stop users who are server administrators from saving certain type of files in directories on C:? They should still be allowed to save any other file types on C:, and their rights over all other drives should remain unchanged.

Alternative

If it is impossible to ban only certain file types, then is it possible to at least display a pop-up warning every time someone tries to save a .bak file in certain directories on C:?

Other Considerations

  1. For a variety of reasons, it is essential for these users to keep their server admin privileges.

  2. I don't mind whether I'm using file level permissions, scripts, or GPOs. All solutions that work are welcome.

Community
  • 1
QWE
  • 261
  • 1
  • 3
  • 6
  • 13
    This is a human problem that you're trying to solve using technical means. Speak with the business owners and get their buy-in for enforcing consequences for users that violate policy and put business functions at risk. – EEAA Sep 22 '16 at 12:49
  • 8
    Where are they supposed to store the backups? Do you not have an established procedure? What happens if they have a non-database backup to store locally? What stops them from changing the file extension? – JAB Sep 22 '16 at 16:37
  • @EEAA I agree and I tried. Unfortunatelly despite my continous attempts we are still in the same situation as before. – QWE Sep 23 '16 at 10:03
  • @JAB They have another 4 drives and one of them is even called "Backups". Procedures are useful only if they are followed and I do not have the authity to enforce them by using non technical means. They save backups on C: out of lazyness, they wouldn't bother to change extension just to annoy me - I hope ;) – QWE Sep 23 '16 at 10:06

4 Answers4

16

You can use the 'File Resource Server Manager' role.

Installation of this role is done from the "Server Manager".

After the installation, enter the 'File Resource Server Manager' mmc console, and follow these steps:

  1. Create a new file screen rule. 1

  2. Choose the second option, and click on "Custom Options". 2

  3. Choose the path you want this rule to apply to, and add the file extension. enter image description here

Alternatively, you can use quotas (included in the same role) to limit the space each user can use.

EliadTech
  • 1,240
  • 9
  • 14
  • 1
    Server backups are created by the database runas user. I'm pretty sure that disk quotas do something dumb here. Oh yes, the backup runs until the quota is reached and bombs out in the middle, consuming all space for the DB until it is manually cleaned. – joshudson Sep 22 '16 at 22:35
  • 1
    A word of caution: I've never tested it, but I have heard it might burden your CPU, some be sure to test it first. – EliadTech Sep 23 '16 at 11:36
  • The OP said these users are administrators, so even if this "works," they can simply turn it off. – Bill_Stewart Sep 27 '16 at 12:53
  • @Bill_Stewart Totally agree, but that's the best option under the OP's conditions, but I think the OP gets it. (Though, I do believe that I can properly restrict admin if I try hard enough.) – EliadTech Sep 27 '16 at 13:00
  • Not really. Administrators can simply undo what you did. – Bill_Stewart Sep 27 '16 at 13:55
3

This is where I would build a custom DB Backup/Restore tool that uses paths from a configuration file so the right paths are selected by default. You can only screw that up intentionally.

joshudson
  • 421
  • 4
  • 11
  • Good point, but we have one but hardly anyone uses it... – QWE Sep 23 '16 at 10:09
  • 1
    Depending on your environment you might be able to block the standard tool from working. – joshudson Sep 23 '16 at 15:11
  • Since the users in question are already administrators, they can simply undo it. – Bill_Stewart Sep 27 '16 at 14:47
  • @Bill_Stewart: I'm sure they can but most people who make basic mistakes struggle to undo clever blocks. – joshudson Sep 27 '16 at 15:14
  • You don't have to wonder. Members of `Administrators` can definitely undo it (they are administrators, after all). You are correct that they might not know how, but that's beside the point since the real problem is adding people to `Administrators` that shouldn't be in there. Everything else is a "band-aid" that doesn't address the real problem. – Bill_Stewart Sep 27 '16 at 15:29
1

In this specific situation I would change the default backup location using SQL management studio to put the backup files in the correct place.

It should be in the context menu for the sql instance: Properties > Database Settings

I don't think the dev admin users are deliberately filling the C drive right?

Also another common mistake when creating a SQL backup is to forget to add the ".bak" extension as it is not added automatically.

Last it can sometimes be the SQL service that creates the backups and that would be difficult to limit without breaking the service

OrangeKing89
  • 143
  • 5
1

Since these users are members of Administrators, this means (wait for it) that they are administrators and can fill the disk if they want to. It sounds to me like the real problem is that you have people that are members of Administrators that shouldn't be. You say they are administrators for "a variety of reasons". If these reasons are political rather than technical, then there's not going to be a viable technical solution, because you are asking, "How do I restrict administrators?" (The answer is that administrators can bypass all restrictions.)

Bill_Stewart
  • 258
  • 1
  • 7
  • Reasons are political indeed. I know that they can bypass anything that I set up. All I want is to create an obstruction so that they are reminded that C: is not a place for backups. They are not saving files there maliciously. It's mainly power of habit. – QWE Sep 28 '16 at 08:19
  • Since the reasons are political, you don't have a technical means to enforce anything. – Bill_Stewart Sep 28 '16 at 11:09