MS SQL Server 2008 Reporting Services

Topics

Prerequisites

Table 1. Microsoft (MS) SQL 2008 Report Server Software Requirements
MS .NET Framework 3.5 SP1
If installing the MS SQL 2008 Report Server on Windows 2003 Server, first install the Windows 2003 Hotfix.

Windows 2003 Server Service Pack

  1. Go to the Software Update Installation Wizard. Click Next.
  2. Read and accept the License Agreement. Click Next.
  3. Click Continue.
  4. Click Finish.
  5. Click OK and then restart your computer and run the SQL Server 2008 setup.

Install the MS SQL 2008 Report Server

For any steps that have no description, accept the default settings.

  1. Click New SQL Server stand-alone installation or add features to an existing installation.
  2. When the Setup Support Rules finish running, click OK.
  3. If you have a license key, select Enter the product key then enter the key.
  4. Click Next.
  5. Read and accept the License Terms. Click Next.
  6. Click Install.
  7. When the Setup Support Rules finish running, click Next.
  8. Select the features to install. Click Next.
  9. Enter the SQL Server name and instance ID. Click Next.
  10. Review the Disk Space Summary. Click Next.
  11. Specify the configuration. Click Next.
  12. Select the Windows authentication mode.
  13. Click Add Current User.
  14. Click Next.
  15. Specify Analysis Services administrators and data directories.
  16. Click Add Current User.
  17. Click Next.
  18. Select the first option.
  19. Click Next.
  20. Clear all options on the Error and Usage Reporting screen. Click Next.
  21. When the Installation Rules complete running, click Next.
  22. Click Install.
  23. When the process is complete, click Next.
  24. Click Close.

Configure Microsoft SQL 2008 Reporting Server URLs

These directions include how to update the ICP port and how to obtain the Report Manager and web service URLs.

  1. Go to Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > Reporting Services Configuration Manager.
  2. Configure the URL used to access the Report Server.
  3. After you obtain the Report Manager and web service URLs, go to each URL in a browser window. If the URLs do not open successfully, update the server name to IP in the URL and try again. Examples:
    • Report Manager URL: http://10.50.70.75/Reports_MSSQLSERVER2008/Pages/Folder.aspx
    • Web Service URL: http://10.50.70.75/ReportServer_MSSQLSERVER2008

Set the Authentication Mode on the Server

After you install Microsoft SQL 2008 Report Server, use the Reporting Service Configuration to initialize the Microsoft SQL 2008 Report Server. By default, the Microsoft SQL 2008 Report Server requests Specify Negotiate or NTLM authentication. This is an example of the default authentication information found in the rsreportserver.config:

<Authentication> 
     <AuthenticationTypes> 
          <RSWindowsNegotiate/> 
          <RSWindowsNTLM/> 
     </AuthenticationTypes> 
     <EnableAuthPersistence>true</EnableAuthPersistence> 
</Authentication>

Topics

NTLM Protocol for IIS 6.0

If your agency uses IIS Manager 6.0, disable the Negotiate process to force IIS to use the NTLM protocol for authentication. Run this command to verify the NTLM protocol is in use:

cscript C:\inetpub\adminscripts\adsutil.vbs get W3SVC/1/Root/MyApp /NTAuthenticationProviders

In the syntax sample, replace MyApp in the command with your actual web application name. Example: ReportServiceFacade or Myreports.

  • If the result is NTAuthenticationProviders: (STRING) 'NTLM', then the NTLM protocol is in use.

  • If the result is The parameter 'NTAuthenticationProviders' is not set at this node, the NTLM protocol is not in use. Run the following command to disable the Negotiate process.

    cscript C:\inetpub\adminscripts\adsutil.vbs //nologo SET W3SVC/1/Root/MyApp/NTAuthenticationProviders NTLM

For more information about how to configure IIS to support both the Kerberos protocol and the NTLM protocol for authentication, go to Microsoft's support article on the subject.

Basic Authentication

  1. Change the AuthenticationTypes in the rsreportserver.config to RSWindowsBasic on report server.
    <Authentication> 
     <AuthenticationTypes> 
          <RSWindowsBasic/> 
     </AuthenticationTypes> 
     <EnableAuthPersistence>true</EnableAuthPersistence> 
</Authentication>
  2. Enter the user name and password on the Report Service Form in Civic Platform.

Anonymous Access

  1. Change the AuthenticationTypes in rsreportserver.config to Custom:
    <Authentication> 
     <AuthenticationTypes> 
          <Custom/> 
     </AuthenticationTypes> 
     <EnableAuthPersistence>true</EnableAuthPersistence> 
</Authentication> 
File Path: D:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER2008\Reporting Services\ReportServerrsreportserver.config
  2. Change authentication mode to Windows, and set identity impersonate to true in the web.config file:
    <authentication mode=“None”/> 
<identity impersonate=“false”/> 
(original: <authentication mode=“Windows”/> <identity impersonate=“true”/>
  3. Copy Microsoft.Samples.ReportingServices.AnonymousSecurity.dll into reportserver directory. Example: D:\Program Files\Microsoft SQL Server\MSRS 10.MSSQLSERVER2008\Reporting Services\ReportServer\bin

    The file path is C:\Accela\av.deploy\dist\reportadaptor. The C:\Accela portion of the navigation path varies depending on the installation location. The av.deploy\dist\reportadapter portion must always exist in the installation folder.

  4. Add extension to rsreportserver.config file:
    <Security> 
 <Extension Name=“None” Type=“Microsoft.Samples.ReportingServices.AnonymouSecurity.Authorization, Microsoft.Sample.ReportingServices.AnonymousSecurity”/> 
</Security> 
<Authentication> 
 <Extension Name=“None” Type=“Microsoft.Samples.ReportingServices.AnonymousSecurity.AuthenticationExtension, Microsoft.Samples.ReportingServices.AnonymousSecurity”/> 
</Authentication>
  5. Configure code access security. Add this information into rssrvpolicy.config. In this example, the project has no strong name, so we are using URL membership:
    <CodeGroup 
          class=“UnionCodeGroup” 
          version=“1” 
          PermissionSetName=“FullTrust” 
          Name=“Private_assembly” 
          Description=“This code group grants custom code full trust.”> 
          <IMembership Condition 
               class=“UrlMembsershipCondition” 
               version=“1” 
               Url=“D:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER2008\Reporting Services\ReportServer\bin\MicrosoftSamples.ReportingServices.Anonymoussecurity.dll”/> 
</CodeGroup>
  6. Restart the report server to use anonymous authentication.
  7. Go to Microsoft SQL Server 2008 > Configuration Tools > Report Services Configuration Manager.
  8. Enter this URL using a browser on a separate machine (using your IP instead of the sample IP): http://10.50.70.75/Reports_MSSQLSERVER2008
  9. Copy the ReportFacade.aspx and ReportFacade.aspx.cs files to the reportManager folder. Example: D:\Program Files\Microsoft SQL Server\MSRS10MSSQLSERVER2008\Reporting Services\ReportManager. Add the following information to the web.config file:
    <appSettings> 
          <add key=“URL” 
          value=“http://10.50.70.75/ReportServer_MSSQLSERVER2008/ReportService2005.asmx”/> 
          <add key=“reportFolders” value=”/”/> 
</appSettings>
    Note: The http://10.50.70.75/ReportServer_MSSQLSERVER2008 portion of the URL represents the report service. The default file path is C:\Accela\av.deploy\dist\reportadaptor
  10. Enter these URLs in the corresponding Civic Platform Report Service Form fields (using your IP instead of the sample IPs):
    • Report Name List URL: http://10.50.70.75/Reports_MSSQLSERVER2008/ReportFacade.aspx
    • Report Service URL: http://10.50.70.75/ReportServer_MSSQLSERVER2008
    • Report Manager: http://10.50.70.75/Reports_MSSQLSERVER2008