May 25, 2010 at 8:59 am
With respect to DBAs and System Administrators, we're trying to be compliant to any applicable governing regulations/standards and be sure we won't have any Audit findings. We're a medium size municipality with 7 System Administrators and 3 DBAs.
Obviously this is going to be much different for a Fortune 500 company vs the local dotcom shop, so it's been difficult to find this info in my googling. Any sources I can cite would be very much appreciated, so I can show to management.
First for us, should some line be drawn between the 2 groups with regards to SQL Server? For example: should SysAdmins have
Local Admin on the SQL boxes (they also have domain admin), but should they have any rights within SQL Server itself?
Likewise should DBAs have "SA" rights, but not have Local Admin? Currently our DBAs only have access through Management Studio and some shares (more on this below), while sysadmins have domain admin, local admin, and SA rights.
I need to note that Brad McGehee's "SQL Server DBA Best Practices" ebook has been very helpful for many of my questions.
* DBA access outside of Management Studio
Brad's book says DBAs should be checking OS logs, so then I take it that it's acceptable for a DBA to have remote desktop access from an audit perspective? Is it okay for a DBA to be a local admin, or is just a regular user good enough with adding specific rights for other required things.
* DBA access rights to move around .mdf, .ldf, and backup files
I would think it's okay for a DBA to be able to move these things around and delete them if necessary. Brad in his books says "Avoid creating network shares on any SQL Server". So, what's the best way for DBAs (or even SysAdmins) to be able to do this? Should they be doing these moves through an rdp session or other? Can you explain why shares are bad? I'm sure it has something to do with security like maybe viruses. Also, my guess is that DBAs shouldn't have access to modify certain logs.
* Managing the SQL clusters
Our DBAs have absolutely no access to this setup outside of Management Studio, honestly this is just one less thing for us DBAs to worry about so we don't care if the SysAdmins manage it so long as it's okay with the Audit police. It sure would be nice to view some cluster info though.
* SQL Server Patching
Yeah, I need to check if SQL Server patching requires both local admin and SA. Not a big deal though...
But on another point, I get very nervous when I find out about SysAdmins doing things in Management Studio with SA rights
when I'm not comfortable with their SQL skills although they are great sysadmins.
It doesn't matter what I prefer, most important is avoiding any audit findings and doing the right thing.
May 25, 2010 at 9:11 am
I forgot to mention that I found this in my googling:
It even states that it is not an absolute and may vary by organization, and it may be a good fit for a large enterprise but not the local mom and pop shop.
Any help is appreciated!
May 25, 2010 at 9:13 am
First, I'd disable the sa account, or set it to a one-time, long password that no one uses (and audit for its use). That should cover anonymous use of activity.
Beyond that, having auditing is important, but if you don't have, or don't want to set up large auditing of systems, that's OK. I worked in an ISO9001 company, and we had Windows admins (domain admins), that had rights to work in SQL Server through BuiltIn/Administrators. That passed, as we had requirements for documenting how things were done and by whom. That allowed us to PASS. Document the responsibilities, and then ensure people stick with those duties and you should be fine.
In terms of separating duties, either you have rights, or you don't. I didn't have issues with Windows admins working in SQL Server as long as it was documented. Typically they didn't, but could act as remote hands for us DBAs, which was invaluable.
Ultimately you want to document the separation. As a DBA, I've preferred being a Windows admin since it makes my job easier for figuring out issues, tracking performance, etc. However I have worked without it and it was fine.
In terms of your questions, I've never seen an issue with DBAs having RDP access to servers. With regards to backup files, I haven't created those shares, I've had processes copy things off using UNC paths.
May 25, 2010 at 9:28 am
Thanks a ton Steve! You saved me many more hours of googling, and it's a great sanity check.
I'm still a little confused about the "no shares" in the best practices book, but that's no big deal. We've been using them, but will change our ways if someone can point us in the right direction.
Thanks again Steve, that helped so much.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply