Monitor and Secure SQL Server with MOM 2005
When we started to deploy MOM 2005 we ran into a security issue. The MOM action account is a local admin on all the MOM servers. The SQL group does not allow BuiltIn\Administrators SA access to SQL. Therefore, once they removed that group from SQL MOM started to have issues with scripts and maintenance jobs. I looked at the whitepaper for MOM 2005 Security and the SQL MP Guide and tried to create an outline for my DBA (and for our own documentation process). Here is what I ended up with:
This solution is only available if your running SQL on Windows 2003.
These instructions are assuming that you are using W2K3 w/ SQL 2000 SP3a. In this scenario the MOM action account is a local admin on the box, but built-in administrators have been removed from SQL server access (these are basically the steps you have to take if you want to monitor SQL with a low level privileged account):
1) Needs to belong to the Users group and Performance Monitor Users group (since it is a local admin you really don’t need to worry about group membership).
2) Should also belong to Performance Log Users, Power Users, and Remote Desktop Users. **This is my requirement, not MSFT**
3) Needs permission to “Manage auditing and security log”.
4) Needs permission to “Allow log on locally”. Since it is a member of Users and Power Users it already has this permission in my lab. In production this may be different.
5) Setup gives the action account additional permissions, these permissions must remain. Act as part of the operating system, log on as a batch job, log on as a service.
6) Added to the registry properties of HKLM\Software\Microsoft\Microsoft SQL Server and provide read access.
7) Added to the sub-keys: Setup, MSSQLServer, MSSQLServer\Parameters, Replication and provide read access.
8) Needs default access to all databases on the SQL server. Should still be listed as DB_Owner for One Point. If this is SQL Reporting Server, the DB_Owner for the ReportServer databases and SystemCenterReporting database.
9) Given Select permission to the sysjobs table of the msdb database.
10) Given Execute permission to the xp_sqlmaint extended stored procedure in the master database.
11) Given Execute permission to the xp_sqlagent_enum_jobs extended stored procedure in the master database.
12) Given Execute permission to the xp_startmail and xp_stopmail extended stored procedures in the master database.
13) Network Service must have read access to SQLEVN70.rll and SQLAGENT.dll (should have it by default).
After all of this is done and all components of MOM have been installed, then the SQL DBA can remove BUILTIN\Administrators from all of the MOM SQL servers.
If you follow these steps you should be golden. Read the SQL MP guide as it will enumerate what you must do for other SQL scripts to work properly.