How to install and configure Microsoft SQL Server Reporting Services (SSRS) 2016 on Windows Server 2016

Views: 2.510
Reading Time: 7 minutes

Hello people,
Good day.

In this post, I will demonstrate how to configure Microsoft SQL Server Reporting Services (SSRS) 2016 on Windows Server 2016. As you know, Reporting Services is installed together with SQL Server, and at the time of installing SQL Server, if you install Reporting Services as well, you can set whether to configure it at this time or to configure it in the future.

If you don't know how to install SQL Server 2016 on Windows Server 2016, check out my post How to install and configure Microsoft SQL Server 2016 on Windows Server 2016.

If you have chosen to configure SSRS in the future, you should open Reporting Services Configuration Manager (usually located at "C: \ Program Files (x86) \ Microsoft SQL Server \ 130 \ Tools \ Binn \ RSConfigTool.exe").

Defining the installation that will be configured

In this step, you must enter the name of the server where SSRS is installed and the name of the instance where you created the tool database (ReportServer) that will be configured.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-1

Service management and installation information

In this screen you can view some SSRS installation information, such as the instance name, version, edition, check if the service is running, and also control the status of the service by starting or stopping it.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-2

Operating System Account (Service Account)

Here you can configure which operating system account Reporting Services will use to run. You can use one of the standard system accounts that the tool makes available, or use a user from your Active Directory (AD).

In the configuration screens, you must click the “Apply” button on each screen where you have changed any settings before clicking configuration page.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-3

Configuring the Service URL (Web Service URL)

From this screen, you can set the Reporting Services Webservice access URL where developers can use to create integrations and applications that will integrate with the tool or for use in tools such as SSRS Report Builder or Mobile Report Publisher.

As I changed the default name of the URL, I clicked the "Apply" button.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-4_2

Configuring the database

In this session, you can define the database that will be used by Reporting Services to store the objects, users and permissions (everything is stored in the database). You can use the default database or set a new name for the database.

You can also define how the database connection will be made by the tool. The default setting is to use a service account whose access was previously granted by SQL Server installation on this database, but you can use either an AD user or a SQL Server user of your choice.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-6

In this installation, I will change the Reporting Services database and create a new database to demonstrate how to do this. If you want to use the default SSRS database, you don't have to follow the steps below and you can skip to the next topic.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-7

In this screen, you must configure a user with CREATE DATABASE privileges on the instance where you will create the new database.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-8

Here you will define the name of the new database that will be created.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-9

It is on this screen that you can choose which user will log in to the database to perform Reporting Services queries while using the tool.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-10

It's time to review your chosen settings.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-11

Successfully created database.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-12

Configuring the access URL (Web Portal URL)

In this screen, you can set the Reporting Services Webservice access URL, where users will enter the address in their browser to view reports.

Here, I will change the default URL name to "reports" and click the "Apply" button.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-13

Configuring the Email Account

In this session we can define which SMTP account the tool will use to send the emails. As I am on my test VM, I have not set up an SMTP server (nor is it intended for this post), but you can use an SMTP address from a public mail server (Gmail, Yahoo, etc.) or the SMTP server of your company.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-14

Configuring Account for External Access (Execution Account)

This account should be used in cases where you have reports that do not use authentication to connect to the database. In this case, the connection is made using the credentials configured on this screen or to access external data over the network.

This user must have read permissions, but nothing more, and must be an AD user.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-15

Configuring Encryption Keys

In this screen you can backup encryption keys, restore, change and delete encrypted data. The first thing to do is to back up the encryption keys so that you can restore it in case of problems or in case of server migration.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-16

At the backup screen, simply set where the key will be recorded and the password, which will be used as salt.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-17

Configuring File Sharing (Subscriptions Settings)

On this screen we can specify an account to access file shares. The file share account allows you to use a single set of credentials in multiple signatures that send reports to a file share. When it's time to change credentials, you set up the file sharing account change and you don't have to update each individual subscription.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-18

If this option is checked, you can provide an account to use to access file shares through the report server. If you set up the file share account, all users can select the account for any subscriptions configured to send reports to a file share. If this option is not selected, the file sharing account will not be available in subscriptions.
Note that you need to verify that the account configured as the file share account has read and write permissions for file share users to use for file share upload.

sql-server-reporting-services-2016-file-share-account

Configuring Server Scaling (Scale-out Deployment)

Scale-out settings are used to increase report server scalability to handle more concurrent users and higher report execution loads. They can also be used to dedicate specific servers to process interactive or scheduled reports. Expanding deployments consist of:
- Two or more report server instances that share a single report server database.
- Optionally, a Network Load Balancing (NLB) cluster to broadcast interactive user load across report server instances.

Basically, this setting allows you to create Reporting Services server clusters by accessing a single database so that information is shared between servers, but each server performs information processing in parallel, increasing service availability and ensuring it has more resources. CPU and Disk to perform this processing as they are independent servers.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-19

When deploying Reporting Services on an NLB cluster, you need to ensure that the NLB virtual server name is used to configure report server URLs and that the servers are configured to share the same view state.

Reporting Services does not participate in Microsoft Cluster Services clusters. However, you can create a report server database on an instance of the Database Engine that is part of a failover cluster.

Configuring Power BI Integration (Power BI) Integration

In this last session of the installation, you can configure the integration between Reporting Services 2016 and PowerBI so that you can show the reports available in your PowerBI account within Reporting Services in a single view and centralizing the reports.

microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-20

This is already a breakthrough, but Microsoft has innovated even more. During one October Technical Preview Available from Microsoft, we can see that in future releases of PowerBI, you will have native support for exporting reports directly to Reporting Services, further enhancing the integration between these two tools.

Accessing Reporting Services

Now that we have completed the installation, it's time to see if everything is working normally:

Web portal access (where reports are published)
microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-21

Webservice Access (To create integrations)
microsoft-sql-server-reporting-services-ssrs-2016-on-windows-server-2016-22

As we can see, the installation was completed successfully.

I hope you enjoyed this post and see you soon.