SQL Server 2016 Reporting Services Cookbook

Chapter 1. Getting It Ready – Configuring Reporting Services

In this chapter, we will cover the following recipes related to installation and configuration of Microsoft SQL Server Reporting Services:

Selecting the edition
Installing Reporting Services with default configurations
Installing Reporting Services-related tools
Accessing Reporting Services Configuration Manager
Configuring the service account of Reporting Services
Configuring Reporting Services URLs
Configuring the Reporting Services database
Executing post-installation tasks

Introduction

Stepping back to the year 2004, the release of Reporting Services as a post-release add-on to Microsoft SQL Server 2000 was a big in with the market as there were not many user-friendly reporting tools out there. Microsoft makes it easy and user-friendly; this worldwide-accepted catchphrase was expected by developers for the same and Microsoft kept the word again. Although it initially focused more on IT-driven reports, it became the most wanted and in-demand reporting tool for report designers and consumers.

Rosetta, the code name used for Reporting Services, was a perfect match. The Rosetta Stone found in 1799 explained the mystery of Egyptian hieroglyphics because it provided information needed to understand their history. Reporting Services does the same; it allows you to have the mystery or reasons for the success of your business revealed.

The first release of Reporting Services did not provide a wide range of data regions for addressing all Enterprise requirements, but it offered enough. However, the key was its architecture. It was designed to run under Internet Information Services as a middle-tier service, making it a server-based and web-based reporting platform. Reports designed with Reporting Services were able to be sourced by any type that has .NET managed provider, OLE DB provider, or ODBC provider. Since most Reporting Services processors and services were distributed, obtaining and processing data was efficient and extensible. The rich architecture used was supported with modern rendering extensions and delivered reports in HTML, PDF, and Excel. In addition to that, it allowed us to have our own extensions, making sure that reports were delivered as per the requirements and printing devices used.

Along with other Reporting Services releases, it was significantly improved and enhanced, making it the best reporting platform in the world. Before Microsoft SQL Server 2016, there were four more releases, with SQL Server 2005, 2008 R2, 2012, and 2014. Among all of these fantastic releases, 2005, 2008 R2, and 2016 are considered major releases of Reporting Services.

The latest release, Reporting Services 2016, is becoming the foremost reporting platform because of the features added, enhancements done on existing features, and the completeness in terms of modern reporting and BI platform. Since there were no major enhancements after 2008 R2, we thought that Reporting Services was about to retire, but the 2016 release showed us that it is still a part of the Microsoft BI ecosystem.

This chapter focuses on the installation and configuration of Reporting Services 2016. The recipes in this chapter explain typical installation and configurations as well as further and infrequent configurations that go beyond the traditional installation and configurations. You will be guided through the installation using recipes, and we will explain which sections are important, why they are important, and how they should be configured.

Selecting the edition

The edition of SQL Server 2016 should be selected based on your needs. SQL Server 2016 offers five downloadable editions, targeting specific business use cases for different prices and levels of capabilities. These five editions are Enterprise, Standard, Web, Developer, and Express. Generally, for an organization, you select one of the premium editions (which is Enterprise) or one of the core editions (which is Standard). There is another premium edition called Analytics Platform System (APS), formerly Parallel Data Warehouse (PDW); that will not be discussed in this book as it is beyond the scope. For an individual, it is always the Developer edition that has all functionality of the Enterprise edition. It can be downloaded and installed without paying Microsoft. Yes, it is free for developers and is licensed for use as a development or test system, not as a production server.

SQL Server 2012 and 2014 had an edition called Business Intelligence, which is no longer available with SQL Server 2016. You can try out Reporting Services with Enterprise Edition without purchasing, as it is available for a 180-day trial period. If you need to use the cheapest edition, which is Express, you need to have Express with Advanced Services.

Getting ready

Features supported by editions are different. For understanding, without discussing all features, we will see the features that are supported only by Enterprise edition.

The following features are supported only by Enterprise edition:

Mobile reports and KPIs
Data-driven report subscription
Scale out deployment
Alerting
Power view

Reporting Services maintain two databases: ReportServer database and ReportServerTempDB. It does not necessarily require the same edition for databases as you have used for Reporting Services. The following list shows you which editions of the Database engine can be used for specific editions of Reporting Services:

Enterprise edition of SSRS: Enterprise or Standard edition database engine
Standard edition of SSRS: Enterprise or Standard edition database engine
Web edition of SSRS: Web edition database engine (local only)
Express edition of SSRS: Express edition database engine (local only)
Developer edition of SSRS: Enterprise, Standard, or Developer edition database engine

How to do it...

Select the edition based on the capabilities required for your application. If you need the aforementioned items that are supported only by the Enterprise edition, then you have no option but select the Enterprise edition. If they are not required, then the Standard edition is enough for your implementation. Cost and limited budget are some other factors for selecting the Standard edition.
Select the Developer edition for development or testing, not as a production server.
Try using Web or Express, which have very limited features. These two editions are good for small-scale implementations.

How it works...

You will be able to create, deploy, and consume reports with functionalities as per the features offered by the selected edition. Not only that, but the usage of processors of the system and memory consumption are based on the edition you have selected. Processors are more relevant to database engine. The Enterprise edition will use operating system maximum, but Standard and Web can utilize only four sockets or 24 cores. If you have selected, Express edition, it is limited to one socket or four cores.

Reporting Services consume memory for various operations. If the edition is Enterprise, it can consume operating system maximum. However, if the edition is Standard or Web, it consumes only 64 GB and, Express edition consumes only 1 GB.

Installing Reporting Services with default configurations

Installing Reporting Services is not a complex process. It is a part of SQL Server installation; just like selecting any other component, Reporting Services can be selected as a required component. Reporting Services 2016 offers two modes: Native and SharePoint integrated. SharePoint integrated mode will be discussed in detail in Chapter 9 , SharePoint Integration. Hence, this recipe focuses on Native mode.

Native mode is the default Reporting Services mode. You can install Reporting Services by either launching the wizard or using the command line.

Getting ready

It is always better to check and see whether your system is ready for Reporting Services before installing it. This check-up can be done using a tool given with the installation, which is called System Configuration Checker.

You need to make sure that you have admin rights on the system that you intend to run the installation on. For that, you need to be a member of a local administrator group, or you need to have credentials of an account that has admin rights.

During the installation, you can decide whether the installation wizard should install files and configure Reporting Services with default configurations, or only install files. If you select Install only, then you need to do the configuration later using Reporting Services Configuration Manager. Selecting the Install and configure option allows you to use Reporting Services immediately after setup is finished. However, this option will not be available if Database Engine Services is not selected as part of the instance features or the database engine is not installed on the same instance.

How to do it...

Let's go through the specific steps and options you select during SQL Server installation when you do the installation for Reporting Services. Note that we will not go through every page in the installation wizard, but only the pages related to this recipe. The steps are as follows:

Once the installation wizard is launched by executing setup.exe, it opens SQL Server Installation Center, which has multiple tabs. The default tab is opened, which is Planning, and the detail page has the link for System Configuration Checker. The following screenshot shows this:

Figure 1.01
When you click on System Configuration Checker, the tool goes through a set of predefined rules and tells you whether the system is ready for installation of SQL Server or not. The following screenshot shows the result of it:

Figure 1.02
If everything is okay, continue with the installation. When you see the Feature Selection page, make sure Database Engine Services and Reporting Services - Native are selected:

Figure 1.03
When you see Reporting Services Configuration, select the Install and configure option to install Reporting Services with the default configurations:

Figure 1.04

Now you can continue with the other pages and complete the installation.

How it works...

Since the installation is done with the Install and configure option, it installs the following items/features for us:

Report Server service:
- Report Server Web Service
- Report Server Web Portal
- Other background processing applications
Reporting Services Configuration Manager
Reporting Services command-line utilities

In addition to that, it configures following items too:

Service account for the Report Server service
Report Server Web Service URL
The Web Portal URL
Report Server databases
Service accounts for accessing Report Server databases

To verify the installation, you can access the Web Portal and see whether it works or not. The URL for the Web Portal is different based on the installation:

With a default instance: http://MachineName:80/Reports
With a named instance: http://MachineName:80/Reports_InstanceName

This image shows the Web Portal configured with a named instance:

Figure 1.05

There's more...

If you open Reporting Services Configuration Manager (this will be discussed in detail in the Accessing Reporting Services Configuration Manager recipe), you will see that some of the services related to Reporting Services are not configured. Unattended Execution Account, Email Setting, Encryption Keys, Subscription Settings, Scale-out Deployment, and Power BI integration are not configured as part of the installation, and they need to be configured manually using Reporting Services Configuration Manager.

Installing Reporting Services-related tools

Installing only Reporting Services does not make your environment ready for working with Reporting Services. In order to author reports, you need Report Designer, which provides an interface for designing reports with various data regions. Report Designer ships with SQL Server Data Tools (SSDT), which was previously called Business Intelligence Development Studio (BIDS) with SQL Server 2005, SQL Server Data Tools (SSDT) with SQL Server 2012, and Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio (SSDTBI) with SQL Server 2014. SSDT is based on Visual Studio and it provides not only Report Designer for building reporting solutions, but also many other interfaces for working with relational databases, Azure SQL databases, SSIS, and SSAS solutions.

Until SQL Server 2014, this tool was automatically installed with SQL Server, but now you need to download it separately and install it.

The other most important tool that is required is SQL Server Management Studio (SSMS). This is not just for Reporting Services; it's an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. This was part of the default installation with previous versions of SQL Server, but now it needs to be downloaded separately and installed as it is a standalone tool.

Getting ready

You can find links to download SSDT and SSMS using any search engine. But the Microsoft SQL Server team has given us an easier way of finding the relevant download page. These links are given with the SQL Server installation wizard, and this makes sure that you download the latest versions of SSDT and SSMS.

How to do it...

Note that the SQL Server installation page has a number of installations related to SQL Server, but we focus only on the tools related to this book. Here is the way of installing the related tools:

Execute the SQL Server setup.exe to open SQL Server Installation Center. Click on the Installation tab; this shows the various types of installation that can be done with it:

Figure 1.06
Click on Install SQL Server Management Tools to open the web page that provides links for downloading Management Studio. This page shows you the link for downloading the latest SSMS.
Note
Note that at the time of writing this book, the version of SSMS is 13.0.15000.23, and it is June 2016 generally available release. This version of SSMS is free and does not require any license to install and use.
You need to click on Install SQL Server Data Tools to install SSDT.
Note
Note that at the time of writing this book, the version of SSDT is 14.0.60525.0. Microsoft recommends that you install Visual Studio 2015 prior to this, and this update will replace SSDT RTM in Visual Studio 2015. If Visual Studio 2015 is not installed, SSDT will install Visual Studio Integrated Shell.

How it works...

Once both tools are installed, SSMS can be used to connect and manage Reporting Services instances and SSDT can be used to design and deploy reporting solutions. You can launch SSMS and connect with Reporting Services. We will talk more about this tool in Chapter 10, Administering and Managing Reporting Services.

You can create Reporting Services projects using SSDT. It allows you to create a new project, and select the Reporting Services template and Report Server Project type. You will see more on this in Chapter 2, Authoring Reports with SQL Server Data Tools.

There's more...

There is another useful tool called Report Builder, which is specifically available for power users. This does not mean that it cannot be used by developers, but as it is more of a Microsoft Office-like tool, it is more relevant to power users and business users. This will be discussed in detail in Chapter 4, Authoring Reports with Report Builder.

Accessing Reporting Services Configuration Manager

Reporting Services Configuration Manager is a tool that you can use to configure Reporting Services settings and related various components. It allows you to access the settings configured during the installation and modify them if required. In addition to that, you can use it to configure settings that are not available during installation.

Note

Note that you cannot use Reporting Services Configuration Manager to manage SharePoint integrated report servers. Prior to SQL Server 2012, SharePoint integrated reporting services instances were able to manage with it, but starting with SQL Server 2012, SharePoint integrated mode is managed and configured by using SharePoint Central Administration.

Unlike SQL Server Management Studio, Reporting Services Configuration Manager is version-specific. You cannot use a Configuration Manager installed for a specific version in your machine to manage another instance of Reporting Services that is not the same version, even if it is a lower version. As an example, if you have installed Reporting Services 2016 Configuration Manager, you cannot use it to manage Reporting Services 2014.

Most of the settings configured through Configuration Manager are stored in the rsreportserver.config file. Even though it is possible to make changes to the file directly without using Configuration Manager, you should not do it unless it is really required. If you have to, make sure you have copy of the file before making any changes.

Getting ready

Reporting Services Configuration Manager is installed with the default installation. It allows you to manage the following items:

Report Server service account: You can update the account that is already added or you can add a new account for the service
Web Service URL: You can modify or create new URLs for accessing Report Server Web Service
Database: You can create or configure the report server database for the Report Server. It also allows you to connect with a database that is already created and set with the required content
Web Portal URL: You can modify or create new URLs for accessing the Reporting Services Web Portal
Email settings: You can configure an SMTP server or gateway that Report Server can use to deliver reports to e-mail addresses
Execution account: You can configure an unattended account that report server can use for accessing various sources on scheduled operations, or when user credentials are not given for operations
Encryption keys: You can back up, restore, or change the symmetric key used to encrypt stored connections and credentials
Subscription settings: You can configure an account for subscriptions that need to access file shares
Scale-out Deployment: You can add multiple report servers to scale out your reporting solution
Power BI integration: You can use this section to register a Power BI account if you plan to integrate your reports with Power BI

In order to access either a local report server or remote report server using Configuration Manager, you need local system administrator permission of the server.

How to do it...

Let's see how we can open Configuration Manager and see the configuration settings. The steps are as follows:

When you open the Configuration Manager, it prompts you with a dialog box for Server Name and Report Server Instance:

Figure 1.07
Enter the server name and select the Report Server Instance for connecting with the required report server. If you have provided valid values and you have local administrator permission, it will open Configuration Manager for you:

Figure 1.08
You can navigate through the tabs given in the left pane. Note that the Apply button is grayed out until you make a change. Once the changes are made, you need to click on the Apply button to save and apply your changes.

How it works...

The rsreportserver.config XML file holds most of the settings related to Configuration Manager. When you change any settings with Configuration Manager, the changes are applied to this file. You can see this file in the <installationpath>eport\Microsoft SQL Server\<instance name>\Reporting Services\ReportServer folder. You can open it and edit using any text editor or XML editor, but it is not recommended because your changes may prevent Report Server from functioning properly.

There's more...

There are some settings that are not available through Configuration Manager. For example, if you need to add another Authentication Type, it has to be manually added to the rsreportserver.config file, as Configuration Manager does not provide an interface for adding Authentication Types.

Some of the settings should only change with Configuration Manager even though it is possible to change them through rsreportserver.config. For example, encrypted values that are related to some nodes should not be changed through the rsreportserver.config file manually.

Configuring the service account of Reporting Services

The service account for Reporting Services is assigned during the installation, it is configured to run the Reporting Services service within the context of a specific Windows account. Installation allows you to select a local account, domain user account, or built-in account for the service. Once set, the installation makes sure that the account has the required permissions to access resources and run processes related to Reporting Services by adding the account to relevant security groups.

If you need to view or reconfigure the assigned account, Microsoft recommends that you use Reporting Services Configuration Manager without using Services Desktop Application. You might change the assigned account to a new account, or you might change the password set for the account if it has been changed at the Windows level. If you use Services Desktop Application to manage the service account, the additional settings that are linked with the assigned account will not be automatically changed. Specifically, it will not be added to relevant security groups. But Reporting Services Configuration Manager makes sure that all required settings are updated accordingly when a change is applied to the assigned account.

Getting ready

You can see the current assigned account using Reporting Services Configuration Manager. Once connected to the instance, you can click on the Service Account page to see the current account set or to set a new account for the service:

Figure 1.09

As you can see, there are two options for setting it. You can either select a built-in account or a user-defined (local or domain) account. If you have accepted the default during account selection, you should see the selected account as Virtual Service Account.

When selecting an account, either during installation or later using Configuration Manager, it is important to remember that the account you select has no excessive privileges. Not only that, but also you must make sure that it is a dedicated account for Reporting Services.

There are different types of account that you can select for Reporting Services:

Local system account: This account is a highly privileged account that should not be selected (or required) for Reporting Services. Generally, we should avoid highly privileged accounts for SQL Server services.
Network service account: This account has fewer privileges compared to the Local System account but has network log-on permissions. Therefore, it is not a bad practice to select this account as the Reporting Services service account. However, since this account is used by many applications, it might introduce a security threat, so it is better to avoid this account type as well.
Virtual service account: Virtual Service account was introduced with Windows Server 2008 R2, and SQL Server started supporting it from SQL Server 2012. It is simply a virtualized account based on a Network Service account but with its unique identifier. It is a local account, and you do not need to create or configure it. For SQL Server services, virtual accounts are automatically created during installation. Since it is a managed account, you do not need to worry about policies or password expiry issues, and this account type is recommended if you do not have a domain user account configured.
Local service account: This is a local account that is similar to an authenticated Windows user account in a local users group. This account accesses resources as a null session or without credentials. Therefore, this will not work as expected when report server needs to access external resources such as a remote database.
Local user account: There is a local, least-privileged Windows account configured for Reporting Services. This is recommended when Reporting Services is installed in a non-domain environment.
Domain user account: A least-privileged Windows domain account configured for Reporting Services. This is recommended when Reporting Services is installed in a domain environment.
Managed service account: This account is similar to a Virtual Service account, but this is a type of domain account. It is more secure than a domain user account and, unlike the Virtual service account, it needs to be created and configured by a domain administrator before it can be used.

How to do it...

In order to manage the assigned account, follow these steps:

Start the Reporting Services Configuration Manager as shown in the preceding image.
Make the necessary changes to the Report Server service account and click on Apply. You may change the account from Virtual service to Network service account for testing.
The Apply button opens the SQL Server Connection Dialog window. It requires an account that has administrative privileges for applying grant rights script to a newly selected service account. If you have administrative permissions, select Current User - Integrated Security, or supply SQL Server credentials by selecting SQL Server Account:

Figure 1.10
Click on OK to save the changes. Once completed, verify the messages in the results shown in the next image and see whether everything is successfully completed:

Figure 1.11

How it works...

Reporting Services Configuration Manager performs a few operations to apply your changes successfully:

A newly configured account is added to the report server group created on the local server.
The account is added to the necessary security roles in the SQL Server database engine instance that hosts the report server database, only if the connection configured to the report server database is set to service account. This account is not just a part of report server database; it gets added to the Master, Msdb, ReportServer, and ReportServerTempDB databases under different roles:
- Master database: This account is added to RSExecRole
- Msdb database: This account is added to RSExecRole, SQLAgentOperatorRole, SQLAgentReaderRole, and SQLAgentUserRole
- ReportServer database: This account is added to db_owner and RSExecRole
- ReportServerTempDB database: This account is added to db_owner and RSExecRole
The encryption key is updated, including the profile information of the account.

There's more...

Note that if you add a new service account to Reporting Services, the old account will not be removed from the database instance. See the next screenshot. It shows both accounts under the Users node; NT AUTHORITY\NETWORK SERVICE is the new account and NT SERVICE\ReportServer$SQL2016_M is the old account. Therefore, it should be manually removed if it is not required by the database engine: