July 16, 2008 at 1:10 pm
Sorry to open this up again but I'm facing this again and it didn't appear that there was much research done in the previous threads.
Someone keeps bringing these things back to the top of the pile for network administrators so I believe it needs to be addressed by Microsoft at some point but for the life of me I haven't found a good security document on MSDB or Technet or Premier Support sites.
So far we've found issues in SQL and (Oracle) that if not requiring local administrative access certainly make it more expeditious. Of course "more expeditious" is precisely what we complain about from software vendors who insist they need the sa account to run their application. So I'm possibly out to lunch on this one or deemed a hypocrite at worst.
Again my question would be which requires more protection? The operating system of the server that houses SQL or the corporate data that is housed on it?
In fact most of the support documentation I've found at Microsoft contains just the opposite security concerns.
How to impede Windows NT administrators from administering a clustered instance of SQL Server
http://support.microsoft.com/kb/263712/en-us
How to make unwanted access to SQL Server 2005 by an operating system administrator more difficult
http://support.microsoft.com/kb/932881/en-us
Books on Line mentions Local Administrative access for Analysis Services.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uas9/html/0637c625-7b5d-4604-be21-f7fbd8782bf7.htm
Our local implementations primarily run with a domain level service account that has limited privileges anywhere else in the domain.
As far as the DBA's we have Local Administrative Access. (For now)
Here are some things that we've found difficulty doing without local administrative privileges.
Administrative privileges seem to be required for:
Installation of SQL service packs (Services must be shut down restarted, temporary directories are created, traces begun, registry updates are performed.)
Additional SQL related product software installs (Services must be shut down restarted, temporary directories are created, traces begun, registry updates are performed.)
Troubleshooting SQL server performance issues (Access to logs both SQL and operating system, access to registry settings, access to event manager and the ability to stop and start services)
Bulk Copying data (File level access is required on the server where the originating import file is located, log file output on the destination server)
Backing up and restoring databases (File level access is required)
Running DBCC on the databases (Searching for Disk Fragmentation, Freeing up extended stored procedure DLL's in memory, CheckALLOC)
Required for use of the performance monitor (Without local Administrator access we were unable to bring up Perfmon. This may have been access to system files.)
Creation of Traces (This is file level access problem but we can have traces dumped to a database. The downside of this is the space requirements and threads.)
Control of the SQL services when performing job and configuration modifications (This is primarily referring to stopping and starting the sql agent.)
Creating new databases (with no access to drives or the space available on them we were forced to use XP_Cmdshell to thwart the very restriction placed on us so we knew where we had space to store the new database.)
Running xp_CMDSHELL (90% of current jobs require this because of external applications and access permissions)
In SQL the base files for the database are managed by the operating system and full control access to these files is essential for attaching and detaching databases
Physical files detached have modified permissions and must have a local administrator to take ownership of the file or the file is lost and cannot be recovered.
Surface area configuration changes and modifications require local administrator access to registry, memory and currently running services.
Local drive monitoring and file fragmentation. (This is at the operating system level but without access to it you have to depend on someone else to verify file consistency or disk fragmentation.)
On high availability nodes need the ability to manage cluster resources.
Setting up log shipping, (File level and registry access are required)
Directed restores require local administrative privileges through Networker. (Legato backup software)
Local administrator access is required for data recovery on suspect databases. (Often requires file manipulation to recover the database. Full control at the file level for all disks associated with the database are required.)
Stopping runaway threads (this is Oracle too, in Microsoft SQL this can be related to extended stored procedures. )
Access to the Event logs to troubleshoot SQL application issues or system events that have or are affecting database performance.
If anyone has any rebuttal advice or a link to a Microsoft authority please respond.
Thanks
July 16, 2008 at 1:52 pm
I'm addressing administrative rights from the OS level. You can't take it away at the SQL Server level and do the job. Just won't work.
Troubleshooting SQL server performance issues (Access to logs both SQL and operating system, access to registry settings, access to event manager and the ability to stop and start services)
SQL Server logs you can get access to without having admin rights. Technically you can get access for the OS, but it's a pain.
Any sort of file access
Appropriate rights will get you most of the way. Some things are problematic, like the database .mdf and log files. You need admin rights in those cases.
Perfmon
There are two local groups in Windows Server 2003 which handle this if you're not an administrator.
Controlling services
Power Users local group gets you this. I believe it also gets you shares.
K. Brian Kelley
@kbriankelley
July 16, 2008 at 3:45 pm
at the end of the day SQL Server is just another application, the OS is the fundamental part of the equation. Every DBA whether Oracle\SQL\etc should understand and be able to administer the underlying OS.
Understanding file\folder permissions, managing users\groups, administering services, etc.
clustered servers take DBA's to a whole new level and its more important, after all the clustering is done at the OS level the application (SQL Server) just sits on top and makes use of the service.
Just my 2 cents worth
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2008 at 5:58 am
Again my question would be which requires more protection? The operating system of the server that houses SQL or the corporate data that is housed on it?
From a compliance standpoint, I think the answer is both.
Most of the issues you have brought up become an issue with separation of duties - if you are not the server administrator, it becomes difficult to do some functions that you consider DBA tasks.
So, you have to ask the question - is the task you are trying to do a DBA task, a server admin task, or both? In the cases where it is arguably both, HIPAA and SOX are both relatively clear that it should take two people to accomplish it. So, I think for the cases in which security is vital, the additional complication of the DBA not being able to accomplish some of these tasks on their own is really the right thing to do.
Where this breaks down is at companies that try to be secure and compliant but are unwilling to invest the resources in having two people around when a drive needs to be defragmented on a SQL Server. However, you can hardly blame the software vendor for that problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply