July 8, 2003 at 8:21 pm
I know that in normal database admin, the administrator does not NEED admin access to the acual box that the server sits on, however, in order for certain functinality to work on SQL (i.e. DTS and auto restart of SQLAgent) Microsoft says that there should be a dedicated account used with Admin rights on the host 2k box or else this will not work properly, especially a DTS packaged kicked off by a non admin (sql) user. However we are having interoffice diatribes where the novell/netware security and 2k admin does not want any dba to have admin access (even the sql service) to the box. I have come up with some basic MSDN articles and reference material, but the 2k admin insists that admin access is not necessary. Can anyone point me in the correct direction to get "official" proof that the 2k admin group is incorrect?
I will mention just for reference that the two systems I administer at this time is the first 2 SQL servers running on a 2k platform, before, the company only ran sysbase/oracle on Unix platforms where it is easier to separate the database application from the OS layer of the system.
Thanks for any feedback.
July 8, 2003 at 10:07 pm
Hi there
You have two levels of lockdown:
1) SQL Server Service account
The default install is over the administrator login. Services run with the local system account. What tends to happen for prod based installs, is a new domain account is created that will run the sql services, this account as restricted privs to both selected registery keys and the file-system. Setting this up can be tough going and a pain to debug, but does work nicely. Leave the builtin./administrator group unless you want to prevent "administrator" users accessing your sql server with sysadmin privs.
This locks down the install and is probably what your after.
2) SQL Server "sa" account
The DBA must have sysadmin access to the box on an adhoc basis, to setup the server initially and perform other higher level ops to actually do their job. How and when the sa account is used is a different story and really needs to be discussed and outlines in the roles/responsibilities doc. There should be zero accounts with sysadmin access apart from sa/builtinadmin and thats it, you should be able to change sa's password at any time. Remember there are a range of other instance and dblevel privs that may offer you other avenues of privilged access to the instance and the databases. May also assist in setting up a plan for DBMS access.
Dont forget, the DBA is the owner and person responsible for the database. Dont screw your DR plans and issues with change management because of convoluted security mechanisms that in most cases, are actually uncalled for. Sit back and have a very good think about what is being achived here and can efforts with very low level security be better handled at higher levels in the network/app infrastructure.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 9, 2003 at 10:49 am
The MSSQLServer service needs to run in a domain account to use Log Shipping, DTS and any operations that involve moving files between servers (pretty common operation for e.g. fetching flat files to import etc.). Via xp_cmdshell any SQL sysadmin user has access to the server with whatever privs MSSQLServer has.
Chris is right, it is pointless tying your hands needlessly. There is however another rather more unpalatable political truth for your 2K admin collegues who, like you say, are probably still clinging on to the idea that being Unix root means being at the top of the computer operator heirarchy. The truth is that in a corporate environment databases are there to make money and when they are down the company is losing money. The Platforms team (OS sysadmin) is the first port of call when a production system goes down to see if there's a hardware fault or if a reboot will cure the problem (often does with Windows). However if that doesn't work the call has to escalate to the DBAs. In the context of a database server in a commercial environment the DBAs outrank the Platform admins because it is the former's job to get the system working again which is something the platform admins cannot do. This is business, not Berkley, and the old "geek" hierarchies are transmuted accordingly.
July 9, 2003 at 11:50 am
Thanks for the responses. But shouldn't a Certified Win2k Administrator KNOW what security is needed on the host box? That runs SQL2k? I know that I had to know to pass my DBA tests for certification. They brought in an "expert" in Win2k just for this purpose and that is the person who is starting the backlash.
Tight Security is a great idea, but in effect it is like Database Normalization taken to the extreme in this case. I know, not a great analogie, but all I could come up with.
At this point, there is not a way of convincing either management or the Unix/Sys Admins that a separation of the OS layer and SQL is not impossible, but very difficult task to achieve in a windows environment.
July 10, 2003 at 5:09 am
I agree, In the microsoft world there is a fin line if any between DBA's and Windows administrators. Most situations that I have worked, We do not segegate the to as far as security is concerned.
Why would you??? What is to be gained gy it?
July 16, 2003 at 3:04 am
Great thread guys.
I totally agree with the underlying point. I have had a similar experience, even within my small organisation.
Very interesting reading!
July 16, 2003 at 3:48 am
Maybe you should congratulate the guy on his thorough approach to security. Just draw up an agreement that whenever you do need admin access to do your job, this guy will come round, log you in, sit around and monitor your work, and log you off when finished. He can search your desk for stolen paperclips etc. while he's there. If this slows down essential maintenance etc. then you know where to direct the complaints.
OK this is churlish and hopefully you can make your point without coming to this, but I once had to resort to this when I was contracted to do some development work. Every time I needed some rights (think it was as simple as writing to certain registry keys), I emailed the admin and copied his boss, explaining that I couldn't continue and would be in the cafeteria (still billing of course) until I got the rights. After a few visits, I got the password of course. Funny thing was, as far as I know, they never changed that password after I finished the work .... ho hum.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply