4

BACKGROUND: Infrastructure team has revoked local admin where SQL Servers resides to everyone including DBAs. Finally, they agreed to let us RDP to the SQL Servers (which they had taken out), but any of the tasks we need elevated permissions are being done via a webex where the individual with rights logs into the server and gives me control to do what I have to do. (I still have to wait for that person to be available to do this for me).

I am Database Administrator III, MCSE, MCSA, and I can't even support my servers. My coworker and I provide support to over 140+ SQL servers from 2008-2014. 95% are virtual servers (Hypervisor). I am trying to get opinions from the SQL Server experts in the industry so I can document the answers for management, and would really appreciate your comments.

QUESTION: * What is an appropriate workaround? *

These new security policies implemented (and without letting us know when they were going into effect) against all the staff in IT, and have prevented my fellow DBA and I from doing the very basic DBA tasks (which I have already documented for my manager and explained locations affected, registry keys, etc..):

  • Install SQL server
  • Install Upgrades, Hotfixes, or apply patches to SQL server
  • Upgrade or install features/components to SQL server
  • Create databases
  • Create data or log files and or move them to different devices
  • Manage files or file groups for space utilization, cleanup, and TROUBLESHOOT
  • Backup and Restores
  • Install Microsoft Assessment and planning tool. Had to install under someone else’s login now I can’t run it or access the database in (localdb)\maptoolkit
  • Reboot the server after installs or to clear up an issue.- I’m ok with infrastructure doing this
  • Start and stop services for SQL components.- I’m ok with infrastructure doing this
  • Implement:
    • replication
    • high availability
    • SQL Server cluster
    • another business continuity strategy
  • Implement auditing
  • Import/export data
  • Start the instance of SQL Server in single-user mode
  • Connect to SQL Server When System Administrators Are Locked Out
  • Server Configuration Manager
  • Create jobs in the OS scheduler if needed - I’m ok with infrastructure doing this if needed, and we can provide scripts

What is an appropriate workaround?

closed as primarily opinion-based by Shawn Melton, mustaccio, Marco, Colin 't Hart, RLF Jun 8 '17 at 13:34

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    There's no appropriate workaround to company policy. Is this change in response to an incident? If you don't feel like you can do your job effectively and can't get the policy changed, find a new job. – Erik Darling Jun 7 '17 at 17:39
  • Not sure what prompted this extreme level of security where servers are left unmanageable. Last Wednesday everyone was surprised (and pissed off) about finding permissions have been revoked department wide.. Mgt had been talking about doing it for about a week, but no testing was done, or communication sent out. – GVila Jun 7 '17 at 17:51
  • Are you asking "how do I circumvent the protections" or "how do I convince them to change their mind"? – Riley Major Jun 7 '17 at 17:55
  • Riley, LOL ..which ever does the trick! I have spent a week trying to convince them to change their mind. Today I am emailing experts so I can document their responses and posting the question here. – GVila Jun 7 '17 at 17:59
  • 1
    One solution to be considered is the use of privileged accounts that are "checked out" for elevated access, and the accounts are managed by a system that changes the password after the checkout period expires. Used in conjunction with a virtual PC that has no Internet access, this breaks the ability of any malware that might get onto your regular workstation from privileged access to servers or from reading poorly encrypted credential caches that contain credentials with access to servers. – Tony Hinkle Jun 7 '17 at 18:55
4

There are a number of US government regulations that, depending on how your company interprets them, require this separation of duties. Sarbanes–Oxley (SOX) is the big one for publicly traded companies. HIPAA for healthcare organizations, etc.

The first question I have is, of those tasks you outlined, how many are you doing directly from the server? Arguably many of those tasks don't require local server access, but I understand this would be frustrating if you are used to performing this work locally on the server.

Regarding workarounds, my suggestion is to be an adopter of this policy. Do this by making sure that your security team is properly locking down user accounts, but creates clearly defined policies for service accounts and that those accounts are setup appropriately. I would even encourage you to look at Group Managed Service Accounts as this makes everyone's life easier if you can make them work in your environment. Follow this up with moving whatever processes you can off of the servers. There are real security concerns with users actively logging into servers regularly, and I would say you should avoid doing this unless absolutely necessary. Argenis Fernandez, during one of the most entertaining PASS sessions I've attended, even showed that anyone with access to a local admin account on a server can get elevated access to the SQL Server regardless how "locked down" it is. Honestly, you could even go so far as to request all of the servers get configured to run as Windows Server Core, which is about as secure a Windows server as you can have. Finally, document those processes that cannot be moved off or need to have exceptions created for them. This will likely be your biggest headache in the short term, but proper documentation should allow your organization to bend the rules to fit the business needs.

Look at it this way. If anything goes bad, such as a breach of information, and it's traced back to unauthorized access to a server, this policy may save your behind without you knowing. I've worked for years in the healthcare and insurance industries and as much as a headache as this sort of policy may seem to be, it's really a good policy to adopt.

This process will be frustrating... at least it has been the number of times I walked a similar path, but don't make it a we vs. them issue, instead approach road blocks with the following statement "this needs to happen so the business can run, how do we accomplish that within these restrictions?" With this approach, it's not about you or your job, it's about your company staying in business which makes it a we vs. the policy issue.

1

[UPDATE: Attitudinally, John Eisbrener's post is spot on. My post is mostly talking about doing the documenting he mentions]

First, of course, make sure you haven't overstated your case. I understand most of the items on there, but I'm not 100% sure that a couple are impossible without local admin (specifically, backup/restore (should be doable via SSMS, but may not be via third-party tools), Import/export data, and maybe create databases).

And, make sure that existing accounts that are allowed to be elevated are. For example, if it's appropriate for the service accounts that run the various SQL Server services to be local admins, make sure they are set up as such.

Second is the same question sp_BlitzErik asked - why did they do this?

If this is to cover something like SarbOx "separation of responsibilities", it's possible to document why this doesn't apply (needing to have an OS admin watching what you do 20 hours a week would be pretty valid).

If it's in response to a recent incident in your company or elsewhere (e.g., "WannaCry"), then this may be an over-reaction, and in time things may cool down.

From there, the key is documentation. Do not try to truly circumvent the new policies; but do document the impact it has to work within them. I gather you've started this already. This is important, to help clarify what the change is costing the company, as well as the potential impact on your job performance!

  • If any emergencies have occurred (or occur while this is in place), have the policies delayed your ability to respond and fix them? Do you have examples (for instance, response time in a similar situation before the policy went into place)? Based on this, are SLAs going to have to be adjusted?

  • How much time are you spending waiting for an OS admin before you can your work? How much will this delay projects you are working on?

  • How much extra time will it take to provide someone else the instructions to do certain tasks, rather than you doing them yourself? Ideally, this would be for tasks that would be repeated frequently; anything done rarely, or one-offs, almost certainly would require someone letting you in with the necessary rights.

  • To expand on the previous item: do you still have full rights to development/test servers? If you do, you may be able to provide detailed instructions for deployment to production based on what you do on the dev/test server; if not, ensuring that what is done on dev can be repeated on prod becomes much more difficult, as no one person may know what was done on the dev/test server. At least estimate the potential impact this will have on any upcoming go-lives.

  • How much time are you spending looking for alternative ways to do what you did before (as noted, within the bounds of the new policies)?

  • On the flip side - have you been able to find solutions to some of your problems, and does this provide reasonable hope that more issues will ultimately be solvable?

In addition, document all significant risks of the above. NOTE: handing a task, even a fairly automated one, off to another computer admin type will always have some risk associated with it, and I wouldn't focus too deeply on that. However, it is fair to reference situations (especially if there are several good examples) where the team you'd be relying on had problems dealing with issues you've asked them to take care of before.

[A few personal examples: In a previous position, we had available a team of "DBAs" from an outsourced partner. I would note times where we had scheduled an activity they were supposed to help with (they were supposed to handle a DB backup and restore while I was doing other things), and they were not available to help (in part because the person to help was the "on-call" person, who may have run into an emergency - but we couldn't contact him to be sure). Or, the time that (due to SarbOx requirements) I had to have them remove some accounts from our servers, and they couldn't because there were associated schemas. I wound up writing a script for them, that would remove unused schemas for them (something they should have been able to figure out on their own, in my opinion).]

Beyond that, do the best you can under the circumstances. As other have suggested, if the impact this change has on your job performance results in poor reviews etc., or if the problem becomes too frustrating to continue to deal with, be prepared to move on.

  • At this time I have no information why this implemented the way it was. Waiting to hear from my manager, but I am pretty sure I will get the 'we just want a secure environment' – GVila Jun 7 '17 at 18:49
  • I have been documenting all the impediments I am coming across...trust me! LOL Also only my other DBA and I are the only ones that will be doing anything in those SQL servers. – GVila Jun 7 '17 at 18:50
  • 2
    To answer your question on permissions on dev/test servers: ALL Servers were affected ....even our own DBA servers – GVila Jun 7 '17 at 18:51
  • 2
    the ultimate secure environment - take your server, and place in a large cube of cement. Let harden. (i.e., you want a secure environment too, but all security has to be balanced against usability). – RDFozz Jun 7 '17 at 19:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.