Installing MOM 2005 and SMS 2003 on SQL 2005 backend [updated!]
INTRODUCTION
I know there have been a lot of questions regarding MOM and SMS running on SQL 2005. This is now a supported configuration for MOM DB and Reporting Services (with 3 hotfixes) and SMS Site database (with SP2).
The steps to perform the installation are not so straightforward. I have carried out both procedures in a VM lab and it works. I assumed Windows Server 2003 SP1 was the OS basis. I want to share with you the steps, so you can go ahead with the install w/o having to figure out why it does not workJ
MOM 2005
1. Install SQL 2005 with those two components (at least:
a. database
b. reporting services
2. Configure reporting services during the installation or later via the configuration wizard).
3. Change permissions on %windir%\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files to grant the Network Service write access (I don’t know why it is not done during install)
4. Install MOM 2005 SP1 server
5. Install hotfixes 913812, 913801
6. Install MOM reporting server. Manually enter both reporting virtual directories.
7. Install hotfix 915785
8. Open IIS manager, open Reports properties page and change ASP.NET version from 1.1. to 2.0 (again, I do not know why the install does not do it automatically)
9. Additionally you may need to follow https://support.microsoft.com/?kbid=909846
10. If you are NOT using SSL on your Reports pages make sure:
a. \Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config has the following section <Add Key="SecureConnectionLevel" Value="0"/> (3 would indicate SSL is required, 0 - not)
11. If you are using SSL on your Reports pages then you should follow additionally these steps if you cannot reach the SQL RS website via https:
a. \Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config has the following section <Add Key="SecureConnectionLevel" Value="3"/> (3 would indicate SSL is required, 0 - not)
b. Verify the server FQDN and not NETBIOS name is used by the certificate:
i. Open Internet Information Services Manager (IIS)
ii. Open the properties of the Default Web Site. In the Directory Security tab, under "Secure communication" click the "View certificate" button.
iii. Make sure there are no errors/warnings on the General tab or the Certification Path tab.
iv. Get the name from the "Issued to" field on the General tab, or the "Subject" property on the Details tab. This will be the fully-qualified domain name (FQDN).
v. Exit out of IIS Manager
c. Add the server’s FQDN to the SQL Reporting Services Web configuration file if needed:
i. Open the RSWebApplication.config file using Notepad.exe on the Microsoft SQL Server 2000 Reporting Services server. Note The RSWebApplication.config file is typically located in the following path on the SQL Server 2000 Reporting Services server: Drive :\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\
ii. Locate the following line in the RSWebApplication.config file: "<ReportServerURL>https://NetBIOS_Name/ReportServer</ReportServerURL>" (note to the tech writer: this config file is XML, so the <> symbols should appear around ReportServerURL)
iii. Replace this line with the following line: "<ReportServerURL>https://Server FQDN/ReportServer</ReportServerURL>"
d. Save the RSWebApplication.config file.
e. Perform a reset of the IIS service.
f. Verify that you can open the SQL Reporting Services site by connecting to the following URL in Internet Explorer: https://FQDN/Reports.
g. If you successfully connect to the SRS site at https://FQDN/Reports, try again to install MOM 2005 Reporting.
Here are the direct links to the downloads for the fixes:
Couple of gotcha's:
1. Remember to specify the reportserver and report manager virtual directory NAMES not URLs into the prerequisite checker:
2. Make sure your reporting services webpage is running smoothly, i.e. you can open the browser to the reporting manager website (use the Reporting Services configuration wizard). For the application pool I advise to create a new one and use either ASPNET local account or Network service built-in service account to run it. I have had problems running it in LocalSystem account (reporting services claimed that it cannot resolve 'LocalSystem' to a SID).
3. After that the installation will continue and succeed. There is a known issue with importing XML reports to SQL 2005 Reporting Services - it is being worked on by the Product Group in Redmond. Clive posted info on this: https://blogs.technet.com/cliveeastwood/archive/2006/05/12/428162.aspx.
4. A KB article about supportability of MOM 2005 SP1 and SQL 2005 is already out:https://support.microsoft.com/?kbid=917615.
5. SQL 2005 Sp1 is already out, I advise to install it, though it is not a strict requirement, I know there was so minor issue that it fixed.
Note: I installed everything on 1 machine, and in a VM environment. You might want to use different accounts due to security constraints in production environment.
SMS 2003
1. Install SQL 2000 + SP3a or SP4
2. Install SMS 2003 SP1
3. Upgrade SMS 2003 site server to SP2
4. Upgrade SQL 2000 to SQL 2005
Until SMS 2003 comes with a slipstreamed SP2 we will have to follow the above general procedure.
Hope this helpsJ