Microsoft Operations Manager 2005 Reporting: Installing with a remote instance if IIS and no IIS installed on the SQL Reporting Server
After MMS 2004 Microsoft released MOM 2005 (previously known as MOM 2004). A short time prior to MMS 2004 I had a beta copy of MOM and had installed it to see what was new. At that time I did not install reporting, because I wasn’t very concerned about that portion of MOM. Several months after MMS, I really had to dive into MOM 2005 because we were making plans to move to it from MOM 2000 SP1. In my virtual labs I could set up every aspect of MOM 2005. In our test labs, I always had issues with SQL reporting services. Why? Because I didn’t understand it. I was a bit upset that Microsoft did not include some basic documentation on setting up SQL Reporting Services with all their MOM documentation. Microsoft simply asks you to refer to the SQL Reporting Services documentation, and now I finally know why…It’s not very hard! It’s sad that this was a major roadblock for me, but sometimes the easiest solution is the hardest one to find.
Often I see people asking about MOM Reporting on the forums and newsgroups. I asked the same questions, and got assistance, but never the right answer (maybe it was the right answer, but I just didn’t get it!). So now I would like to share with you my guide to setting up SQL Reporting Services and MOM 2005 Reporting. Keep in mind that I follow some security guidelines, but this is by far not the most secure deployment. These steps are tailored for my environment, so they may not work in yours. I recommend setting this up in a virtual lab first just to show that MOM Reporting is not difficult to install. After you see that it works, then you can work on securing it. I highly suggest you read the MOM 2005 Security Guide and the SQL 2000 Management Pack Guide for MOM 2005. For more information on how to set up SQL monitoring with a low privileged account, see my blog titled “Monitor and Secure SQL Server with MOM 2005”.
All of my MOM servers use one service account and that account is a local admin on all of them (Management Servers and Database Servers). This one service account has these roles:
MOM Server Action Account
MOM Reporting Server Service Account
MOM Reporting Server SQL Access Account
MOM SQL Server Action Account (Our DBA’s strip BUILTIN\ADMINISTRATORS from SQL Security after we install all of the MOM Servers and apply the correct security settings. If you don’t do this, then this install should really be simple, but you have a potential security threat.)
MOM SQL Reporting Server does not have any IIS components installed.
MOM Management server has IIS components and ASP.Net installed, or there is a dedicated IIS server.
All MOM server roles (besides the MOM Management server with IIS) are separated: Dedicated Management Server, Dedicated SQL Server(s).
When I install MOM 2005 I logon with this account and perform the install. So all default ACL setting is assigned to this one account (saves me from having to maybe change a few and less troubleshooting if there are any issues). I do NOT use this account to push and install agents. I have a separate account for that. I also run all my agents as local system, unless they require a low privileged action account (requires Windows 2003 to do this). So now that you know all of this, let’s proceed to the task of installing SQL Reporting Services.
If your MOM SQL Reporting Server is running Enterprise SQL, then use the Enterprise edition of SQL Reporting Services (Same for Standard editions, etc.). You want to keep these at the same edition level.
I don’t have IIS installed on my SQL Reporting Server for security reasons. So we put IIS on one of the Management Servers in order to host our reporting interface. This is where you will begin:
- Logon to the Management Server that has IIS and ASP.Net installed (Doesn’t have to be your management server. It can be a stand alone IIS server).
- Begin the setup for SQL Reporting Services.
- Verify prereqs are good (minus Visual Studio .Net).
- Follow all the screens until you are asked about the SQL Reporting Services account. Change that to the domain account you use for your MOM DAS account and ensure the box is checked for ‘Auto-start the service’ (if you use network services, then I can’t help you. I was unable to get this to work, but it should be possible).
- I keep the virtual directories with their default names and unselect ‘Use SSL..’
- When you see the option to select the Reporting Server SQL Database Instance enter the name of the SQL Server and Instance. On this same screen select Domain User Account for ‘Credentials Type’ and then put in your DAS account information.
- Complete the rest of the install.
You should see the page for successful install. If you see a warning about failed to initialize, then you will have to trouble shoot that. I was getting that a lot, and it was primarily related to policies on the server that didn’t let IIS or ASP.Net work correctly.
Now that SQL Reporting Services is installed you have to go to your SQL Server and install MOM Reporting. If your environment does not allow IIS to be installed, like mine, then you will have to do a silent install of MOM Reporting. Here are the steps to do that:
- Log on to the SQL box with that same MOM service account (keep in mind this account has all the roles listed in the beginning of this article).
- Copy the MOMReporting.msi file from the MOM 2005 install CD/Share to this SQL Server.
- Open a command window and change directories to the location of MOMReporting.msi.
- Execute the silent install which would look something like this:
- msiexec /i momreporting.msi DB_SIZE="<Size you want>" SQLSVR_INSTANCE="<Reporting SQL Server>" TASK_USER_ACCOUNT="<DAS Account if you follow my model>" TASK_USER_PASSWORD="<DAS Account PW>" TASK_USER_DOMAIN="<Domain Account is in>" REPORTING_USER_ACCOUNT="<DAS Account if you follow my model>" REPORTING_USER_PASSWORD="<DAS Account PW>" REPORTING_USER_DOMAIN="="<DAS Account if you follow my model>" MOM_DB_SERVER="<MOM SQL Server hosting OnePoint>" ROSETTA_SERVER="<Server you have SQL Reporting Svcs on>" PREREQ_COMPLETED=1 /q /l*v <Path and name of msiexec log file>
- Keep in mind, this is what I did for my deployment and it may not follow all best practices for security. Nonetheless, if you want to ensure that MOM reporting works, then following this guide and you should have no problems installing MOM Reporting.
- Also the above silent install is one line, so type it all out in notepad in a single line, then copy and paste it into the command window to execute. Double check what you typed. The silent install options for MOM components can be found HERE.
- When you specify the size of your Reporting Server database you should make it the same size or larger then your current OnePoint database.
- Wait patiently and monitor the event log. Eventually you should see an informational event in the application log with the source of MsiInstaller indicating it was successful (event id is 11707).
- The only bad part about this install seems to be that you cannot tell msiexec where to put the database files and log files. There maybe an option for this, but I don’t know of it. It may just come from your SQL settings. I don’t know. You may be able to move the log file to a different drive after you create the database (or create a new one for the database to use.
- Now you need to add users or global groups to the local group on the SQL Reporting Server called SC DW Reader. This must be done so the users can access the reports. Microsoft recommends creating global groups with similar names of all the local groups MOM creates for easier management. You can read more about that in the MOM whitepapers.
- After you have done all of this, verify that the scheduled task for DTS has been created and is set with the DAS account credentials (Run as on the task should be the DAS account – If you follow my model).
- Execute the DTS job, and verify it works by looking at the application log.
- Go to the server with SQL Reporting Services and bring up the Report Manager web site. You may be prompted for credentials, so enter the DAS account information: DOMAIN\DASACCOUNT and password.
- On the first page, click on the Properties tab.
- Click on New Role Assignment.
- Enter the user name (Domain\Account) or global group in the box "Group or user name" and select the role "Browser" then click apply.
- You are done!
Should you have any issues, be patient and you will be able to find the solution. If you follow this article and something does not work for you, please feel free to contact me or post a comment. If this article becomes your solution, then I am happy for you and good luck!