Access to Windows OS in the Enterprise for a DBA

Question

Post reply

Access to Windows OS in the Enterprise for a DBA

ckissee

SSC Enthusiast

Points: 102
More actions
March 16, 2005 at 10:16 am

#102439

I’m a DBA in a large enterprise supporting DB2, Oracle, Sybase, and now SQL Server. The administration of the windows box for SQL Server is under different management than what I am under. As such, I am only allowed access via Enterprise Manager. I can’t view windows logs, file systems, OS settings, etc.. Based on my experience with other DBMS’s, I find this highly unusual. I’m looking for some feedback from other members about their experience on this matter. Specifically, what are the advantages of a DBA being an admin or having other access on the Windows platform ? What DBMS tasks are there that I’m not aware of that could possibly need access to the OS ? Would appreciate feedback on how other DBA’s are operating, and if this is a common scenario or not, in a large enterprise setting.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Our DBA group members are members of Local Administrators on the servers that have SQL Server on them. We find it necessary for installing SQL Server and it's components, viewing Windows logs via Event Viewer, etc. We are not Domain Administrators as we have a Network Services group to administer servers and networks.

Greg

Gé Brander SSCarpal Tunnel Points: 4353 More actions · Answer 2

I think it is needed to have access to terminal server mode on every SQL Server. Not with administrator rights, that is only needed for installing and configuring de SQL Server instance. But with restricted rights it is possible to read the log files, copy dump files and read the eventvwr. Just ask what functionality you need, and mention that you do not need admin rights. That way they can work out how they can do that.

gbn SSCertifiable Points: 6379 More actions · Answer 3

I think it is needed to have access to terminal server mode

What?

What about restarting the SQL services? Rebooting the server?

Installing SQL?

Changing some server properties writes to the registry.

Is the SQL Service account local admin? If so, then just use xp_cmdshell to do stuff.

The point of being a DBA is to manage SQL Server. If I (as a DBA) have to submit a form in triplicate to get some server activity performed than I am hampered. I don't want to be a domain admin, I don't want to be local admin on non-SQL Servers.

From my personal view:

Do not ask me to manage the server if you don't give me access.

It says you don't trust me. It says you have an empire.

If your server team will not let the DBA have access then it becomes their responsibility if you must ask them to do any server level activity.

See:

http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=61&messageid=2621

http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=98537

http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=13983

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 4

On the same token, though, the argument from the server admin group is this:

"Don't hold me accountable for uptime and availability of a server when others outside of the server admin group have administrative access to the server and can do any number of things to bring it down. That says you've put the responsibility on me but you won't give me the authority to enforce what's necessary to maintain that responsibility."

Having walked in both shoes I see both sides. I've been on the DBA side when I really needed something done and didn't have rights to do anything about it. That caused me to sit and wait on my hands while a sysadmin was paged. I've also been on the server admin side when a DBA who didn't know what he was doing took down a production cluster for our core application. Took it down hard. It ended up being quicker to eject the node he was playing with, drop ship a server from Compaq, bring it into the node, and reinstall SQL Server rather than try to piece through everything that he did and correct it. Meanwhile, we stayed "up" with an unstable cluster for several work days. That weekend the server admin guys were in fixing the problem. The DBA in question was at home enjoying time with his family.

So either direction you go, there's the potential for something bad happening. The only solution is to cooperate between teams and have strong and workable procedures in place that ease some of these pains. Issues will still come up, but they will be reduced.

K. Brian Kelley
@kbriankelley

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 5

Typically, unless you either have a Terminal Services Licensing Server or really tweak Remote Desktop, though, you have to have administrative access to use Terminal Services. From what you indicated, the following can be done:

Share access to where the dump and log files are.

Local Security Policy change on Manage Auditing and Security Log.

In your case you are absolutely right, administrative access isn't needed.

K. Brian Kelley
@kbriankelley

Darrell Zink SSC Enthusiast Points: 187 More actions · Answer 6

I work in a Fortune 10 company with an installed base of nearly 1000 SQL Servers, and DBAs all have local administrator rights. I'm in the server support group, and we have no issue with DBAs having such rights not have we experienced any particular problems. DBAs are, however, precluded by policy from 'tuning' the OS or making any other underlying system changes.

Gé Brander SSCarpal Tunnel Points: 4353 More actions · Answer 7

Our servers hava a group 'Remote Desktop Users', with no administrator rights... So it is possible to use remote desktop without administrators rights.

I have a very good relationship with the server administrators group. This helps a lot. I am also the only one in our company with restrictive access to servers. Soon that is over, many others have done some destructive thins on other servers where they had administrator rights. So in some time they will have restricted access too. I think this is better. Even when you have good knowledge of administering Windows OS (I am MCSE 2000 and MCDBA 2000) it is still better not to share responsibilities. No miscommunications on policies etc. That just works better. When in times of troubles you need to fill in a form in triplet to get something done... The procedures are not right in your firm... Different administrators need to work together very closely in times of trouble and closely in normal times is my point of view here.

The SQL Server machines are OS managed by the server administrators group and the SQL Server is managed by my group. I do have an account with administrator rights on those servers, but I will only use that account for installing and configuring the SQL Server. That is not very often of course! No other services run on our SQL Server machines, and that way I have a very stable environment. No other users/administrators, then the SQL Server admins or Server admins, will ever get access to our servers. No need for that, and I can guarantee uptimes...

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 8

K. Brian Kelley

SSC Guru

Points: 114532

March 17, 2005 at 10:01 am

#547601

Right. Tweaked. Not a big deal, but it has to be done on each computer typically and it gets to be a pain if that's a lot.

K. Brian Kelley
@kbriankelley

ckissee SSC Enthusiast Points: 102 More actions · Answer 9

My My !!! Seem's I've evoked some strong opinions here ! To reiterate, I don't even have remote desktop access. Strictly enterprise manager. Also, I don't have console access. I have physical access to the machine room (for other platforms), but to get to the console requires VKM console access, which I am not given either. Again, I'm told "You don't need it to do DBA work".

In spite of differences of opinion, it seems most folks are at least allowed some level of non-admin access rights, as well as some remote desktop access facility.

Okay, so what about back-up authorities. The DBA group also does not perform the backups, it is done by CommVault and a CommVault administrator. I also find this unusual, DBA's in my experience have always backed up the data. Although I have no CommVault experience. Also, the CommVault administrator recovers the data when requested. But the DBA's are held accountable for data integrity !

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 10

I would say your situation is an extreme one... one which many of us wouldn't enjoy working in. I'm primarily on the server side and I wouldn't think of allowing anyone to mess with db backups other than DBAs. But yes, you're absolutely right in that some access outside of SQL Server is almost certainly essential for proper management of this database platform. Certainly access to the event logs is one. Keep in mind... if you have EM access you have QA access. That gives you some of the functionality back, but you may be violating policy (fireably offense in most cases) by doing so. Document when you run into a roadblock and let your management handle it from there. That's about all you can do unless the paradigm changes.

K. Brian Kelley
@kbriankelley

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 11

Sounds quite 'draconian' to me. I've always had 'Local Admin' on my SQL Servers. Prior to RDP, console access as well. The list of companies I've traversed reads from fortune 100 to mom'n'pop 200. It's definitely a management/paradigm thing (a nice way to say that those in charge know almost nothing !). Even at one largest banks in the world (#2) we had 'Local Admin' and RDP access. You are not in an enviable position at all. I'd seriously consider executing "sp_update_resume" since you're being asked to provide critical database support with such severe restrictions.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."