AWS Database Blog

Configuring SQL Server Reporting Service 2016 on SQL Server 2012 and 2016

Attempting to connect to SQL Server Reporting Services (SSRS) may sometimes fail with the error “The Reporting Services instance could not be found.” This error can happen when using Amazon Machine Images (AMIs) installed with SSRS, SQL Server Integration Services (SSIS), and SQL Server Analysis Services (SSAS).

This post explains the step-by-step procedures on how to configure SSRS 2016 on SQL Server 2016 to avoid these errors.

Confirming the error

First, confirm that the SSRS started.

  1. Launch the Amazon EC2 instance using the respective AMI.
  2. Navigate to the services.msc console. You can do this one of two ways:
    1. Enter services.msc from the Run.
    2. Use the SQL Server Configuration Manager.
  3. Select SQL Server Reporting Services (MSSQLSERVER).
  4. Open SSMS. From Server type, choose Reporting Services.
  5. Leave Server Name and Authentication with their default settings.
  6. Choose Connect. The error message “The Reporting Services instance could not be found. (Microsoft.SqlServer.Management.UI.RSClient)” appears. (The time between connecting and receiving the error may take up to a few minutes.)

When further examining the connection error, you see the following:

Cannot connect to <Server Name>.
The Reporting Services instance could not be found. (Microsoft.SqlServer.Management.UI.RSClient)
Program Location:
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.Microsoft.SqlServer.Management.UI.ConnectionDlg.IServerType.ProceedWithConnection(UIConnectionInfo ci)
   at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.ProceedWithConnection(UIConnectionInfo ci)
   at Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.GetRSConnectionObject(Boolean ensureOpenConnection)
   at Microsoft.SqlServer.Management.UI.RSClient.RSClientConnection.CreateConnection()
   at Microsoft.SqlServer.Management.UI.RSClient.RSClientConnection.ParseServerName(String serverName)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Checking the configurations of the SSRS Configuration Manager

When you observe this error, you must connect to the SSRS Configuration Manager and check the configurations of the web service URL, the database, and the web portal URL.

Before you can use the web portal or the Reporting Server web service, you must configure at least one URL for each application. Configuring the URLs is mandatory if you installed Reporting Services in files-only mode. That is, if you installed Reporting Services by selecting Install but do not configure the server option on the Reporting Server Installation Options page in the Installation Wizard. If you installed Reporting Services with the default configuration, you already configured URLs for each application.

Use the Reporting Services Configuration tool to configure the URLs. This tool defines all parts of the URL. Unlike earlier releases, Internet Information Services (IIS) websites no longer provide access to Reporting Services applications in SQL Server 2008 and later versions.

For information about the prerequisites for modifying a URL, see Configure a URL (SSRS Configuration Manager) on the Microsoft website. This post uses the default URLs.

Configuring the web service URL

Complete the following steps to configure the web service URL:

  1. Connect to the SSRS Configuration Manager.
  2. From the Service Account section, choose Built In Account, which is the Virtual Service Account.
  3. From the menu, choose Web Service URL. The values for Virtual Directory, IP Address, TCP Port, HTTPS Certificate, and Report Service Web URLs are already prefilled. For the Report Server web service, the default virtual directory name is
  4. Choose Apply to configure the web service URL.

For more details about configuring the web service URL, see the section “To configure a URL for the Report Server Web Service” from the previously linked Microsoft tutorial.

Configuring the database

SSRS native mode uses two SQL Server relational databases to store report server metadata and objects. The first database is your primary storage, and the second database stores temporary data. These databases are created together and bound by name. With a default SQL Server instance, the databases are named reportserver and reportservertempdb. Collectively, the two databases are called the report server database or report server catalog.

There are two ways, automatic and manual, to create the native mode report server database. This post uses the automatic option.

Use the SQL Server Installation Wizard if you choose the default configuration option for installation.

  1. On the Report Server Installation Options page, choose Install and configure. If you choose the Install only option, you must use the SSRS Configuration Manager to create the database. The Report Server Database window appears.
  2. Choose Change Database to change the database. Or, choose Apply to go with automatic database configuration.

Configuring the web portal URL

The web portal helps you view reports, mobile reports, KPIs, and navigate through the elements in your report server instance. You can also use the web portal to administer a single report server instance. You must complete this step after you configure the web service URL.

  1. From Web Portal URL, review the following information. Most values are prefilled; you can use them as-is. For the Report Server Web Portal, the default virtual directory name is ReportServer. You can change the values based on your use case.
  2. Choose Apply. You can see the list of tasks completing.

After configuring the web service URL, database, and web portal URL successfully, choose Exit and close the Reporting Services console.

Confirming the connection is successful

Test that the connection is successful by completing the following steps:

  1. Open SQL Server Management Studio.
  2. From the menu, choose Reporting Service.
  3. Choose Connect. In a few seconds, you see Reporting services configured on the left side of the console.

Conclusion

You can now configure SSRS with SQL Server 2016 quickly by following these simple and easy steps.

 

About the Author

 

Paras Manocha is a Cloud Support Engineer with Amazon Web Services helping customers design solutions, workloads, and helping them utilize AWS Services to best suit their environment.