October 14, 2008 at 9:34 am
I'm working with a client that has extremely draconian security policies for administrator access to SQL Server as a DBA. I'm trying to drum up justification for having local administrator AND remote desktop access to all the SQL Servers in the inventory.
I've started a list of things that would be "easier" for a DBA to do his or her job with this type of access. I'm interested to see what the community comes up with. Any thoughts?
October 14, 2008 at 10:05 am
- It would be easier to reboot the server arbitrarily.
- It would be easier to delete important system files
- It would be easier to introduce a virus by surfing for porn.
Oh - probably not what you wanted...
I prefer to have limited access. Being able to RDP into a SQL server is not terribly useful as long as I am able to get to the event logs remotely. You may want to start with your list of routine maintenance tasks and determine what access you need to accomplish these.
Remember, having limited access protects you as much as it can annoy you.
October 14, 2008 at 10:55 am
I've been through this before with one company I worked at. In the end, the DBA and Server teams settled on an account with these privileges that we could "check out" through the change process on an as needed basis. I actually didn't mind it because it protected us from those little "oopses" and we were allowed to check out the ID for a day or more when needed. We checked it back in when we were done.
Your post didn't specify if you could have this access temporarily as needed or not so I'll assume you aren't getting it at all.
You will eventually need this access to build servers, add instances, manage server settings, restart the services, setup log shipping, replication, apply service packs/patches, etc.
If you can't access the console of a production server it is more difficult to find the resource usage. (CPU, memory, disk) If you can't do that, you can't troubleshoot performance issues.
One way to justify your access is to create a list the tasks you perform, and try to perform them without local admin. Document your results and associate the tasks with scenarios. Then, present the list to the decision makers for discussion.
They may decide that the server team will "supply" you with the information and thats their right, but you may want to make sure that they understand that resolving an outage could take a lot longer.
Chris.
Chris.
October 14, 2008 at 11:30 am
I didn't mention temporary or permanant access in the post simply because I didn't want that to sway the responses toward one method or the other.
What I'm looking for is more of the "ammunition" here to quickly justify why its needed. If the client looks at the information and still says "no you can't have it", then I will have sufficient timeline risk documentation.
The problem seems to be that an awful lot of DBA "stuff" to be done. There are several challenges to gettting to this goal.
1. Lack of a qualified DBA up to this point
2. Lack of system access for a qualified DBA
3. Lack of effective procedural controls to create and maintain a managable environment
4. Draconian security requirements that effectively remove a qualified DBA's ability to create and maintain such an environment
October 14, 2008 at 11:33 am
Easier, yes. Needed, no.
You might not get all the performance counters you want, and that can delay troubleshooting. You might have issues in an emergency in getting the server going.
Other than that, what "DBA" stuff do you need to do that you can't with with sysadmin within the server? Not a lot in my mind. While admin is good, it isn't necessary, especially if you have separation of duties.
Document what you can, revisit it (and revise) when you have issues. Let someone else decide if they can deal with the delays. Who knows, the 12th time you call the sysadmins they might start suddenly supporting your position.
October 14, 2008 at 11:38 am
Interestingly enough, I've been around long enough on this to not have a "position" either way. I completely understand the seperation of duties issues and compliancy concerns. However executives also need to have things quantified in language they can understand of why certain things may take longer with these kinds of seperations in place. What I'm looking for is just a brainstorm of "What are the things that may take longer" so I can present that to them.
October 14, 2008 at 12:10 pm
It all depends on the situation. I work as a DBA in a company where I have sysadmin rights on all SQL Servers, and I could not do that job without it. BUT, we are a small company where time is often a major factor and we not have a large team of sysadmins or network admins (1 each). So there are times when I often do things that other companies would not consider DBA work. For instance, I install the new instances of SQL Server and related tools, trim unnecessary files if disk space is running low, and on a few rare occasions I have assigned the needed local server permissions to the SQL Server Service and SQL Server Agent Accounts we use, as well as installing some relevant OS patches if our Sysadmin is busy and agrees to me doing it.
In some larger companies those types of tasks are designated to groups outside of the DBA team. In those cases where there is a large and robust IT team and time is not of the essence then the DBA probably does not need Sysadmin at all. And in other cases (as another poster mentioned) a hybrid approach where the DBA can get special access to an admin account is the right way to go.
As to tasks that the DBA may need (depending on the company) that require Sysadmin access:
**Installing SQL Server and new updates for it
**Installing related tools (Red Gate SQL Backup for instance, the Reporting Services if those aren't installed with the database engine, etc)
**Installing operating system patches directly related to SQL's operations
**Troubleshooting, especially complaints of a "slow server"
**Trimming space usage if space is low
**Setting up replication (may require assigning permissions, creating folders, etc)
Of course, some or all of those may fall into a sysadmin or network admins domain depending on the environment.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
October 14, 2008 at 12:14 pm
Well the words "completely understand" in the previous post are a bit presumptuous. More like "acknowledge". I don't think anyone "completely understands" why a lot of these policies are put in place.
I saw a post in a Microsoft forum about a similar subject. It basically stated that locks "keep the honest people honest".
Coming at the problem from another direction you can also use the "gun control" argument... If you outlaw guns the only people who have guns will be the outlaws...
These debates will continue until the end of time I'm sure.
I think I've got a pretty good idea of a "reference framework" of administrative tasks. Microsoft actually has great documentation on it in the WSSRA for SQL Server. The final gap analysis I will provide for them includes this reference, as well as risks to implementation of the framework. If it takes longer to implement it that's fine, as long as they understand the reasons why ahead of time.
I'm a "techie". I fix broken servers and make things run fast. But just like a lot of techies now, we're being required to do this in increasingly restrictive environments while being pushed for answers on why things take so long to complete. Switch the hat, become a consultant, and speak in "business words"... Tell them in language they can understand why the restrictions force extended timelines.
October 14, 2008 at 12:18 pm
It's hard to say what will take longer. I've had things that took longer at a large company that didn't at a small one. Familiarity with the system, DBA, etc. come into play.
I think this is a living document, one you need to revise as things come up and then re-present to execs.
October 14, 2008 at 12:26 pm
I would expect the the things that will take longer are the things you do not need to do very often.
- You would need assistance to apply a service pack.
- You would need assistance to add new shared folders to the file system (not that this should be your responsibility).
- Troubleshooting a performance problem could be a bit trickier - or at least you would need to have the appropriate tools to do it remotely
If you find yourself having to terminal into a production SQL server on a regular basis with a login that has admin permissions, you probably have a process problem or software issue of some sort. MS SQL requires regular monitoring and tuning, but should rarely require hands-on visits to the console. If you are logging in to gain access to event logs or remaining disk space, you don't need to and using the appropriate remote tools will probably save you time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply