Home Data SQL Server 2016 Reporting Services Cookbook

SQL Server 2016 Reporting Services Cookbook

By Dinesh Priyankara , Robert Cain
books-svg-icon Book
eBook $47.99 $32.99
Print $60.99
Subscription $15.99 $10 p/m for three months
$10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
BUY NOW $10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
eBook $47.99 $32.99
Print $60.99
Subscription $15.99 $10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
  1. Free Chapter
    Getting It Ready – Configuring Reporting Services
About this book
Microsoft SQL Server 2016 Reporting Services comes with many new features. It offers different types of reporting such as Production, Ad-hoc, Dashboard, Mash-up, and Analytical. SQL Server 2016 also has a surfeit of new features including Mobile Reporting, and Power BI integration. This book contains recipes that explore the new and advanced features added to SQL Server 2016. The first few chapters cover recipes on configuring components and how to explore these new features. You’ll learn to build your own reporting solution with data tools and report builder, along with learning techniques to create visually appealing reports. This book also has recipes for enhanced mobile reporting solutions, accessing these solutions effectively, and delivering interactive business intelligence solutions. Towards the end of the book, you’ll get to grips with running reporting services in SharePoint integrated mode and be able to administer, monitor, and secure your reporting solution. This book covers about the new offerings of Microsoft SQL Server 2016 Reporting Services in comprehensive detail and uses examples of real-world problem-solving business scenarios.
Publication date:
November 2016
Publisher
Packt
Pages
596
ISBN
9781786461810

 

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...

  1. 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.

  2. Select the Developer edition for development or testing, not as a production server.

  3. 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.

See also

For more information about feature comparison between editions, visit the following URL:  https://msdn.microsoft.com/en-us/library/cc645993.aspx

 

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:

  1. 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

  2. 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

  3. 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

  4. 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.

See also

The next recipe, Installing Reporting Services-related tools, discusses the other required installations for completing a Reporting Services installation.

 

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:

  1. 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

  2. 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.

  3. 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.

See also

A newly added tool called SQL Server Mobile Report Publisher is available with Reporting Services 2016. See Chapter 6, Authoring Mobile Reports with the Mobile Report Publisher, for more on it.

 

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:

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

    Figure 1.07

  2. 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

  3. 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.

See also

In addition to the rsreportserver.config file, there are two more configuration files: rsreportserverservices.exe.config and rsreportdesigner.config. You can read more about them at https://msdn.microsoft.com/en-us/library/ms157273.aspx and https://msdn.microsoft.com/en-us/library/bb630448.aspx .

 

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:

  1. Start the Reporting Services Configuration Manager as shown in the preceding image.

  2. 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.

  3. 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

  4. 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:

Figure 1.12

 

Configuring Reporting Services URLs


As you saw in the Installing Reporting Services with default configurations recipe in this chapter, once the installation is done, one way of verifying that the installation is successfully completed is by accessing one of the URLs that are configured automatically during the setup. If the installation is done with install only mode, then the URLs have to be configured using Reporting Services Configuration Manager, because only the install and configure option creates URLs for Reporting Services during the installation.

Installation with the default configuration reserves two URLs for Reporting Services, one for the Web Service application, and another for the Web Portal application. These URLs are defined as URL reservations in HTTP.SYS, which is an operating system component that listens to network requests and handles them. Even though it is possible to create and configure these URLs using different tools, it is recommended to use only Reporting Services Configuration Manager for creating and configuration. URL reservations are automatically created when you configure URLs using Reporting Services Configuration Manager.

URL reservations in HTTP.SYS define which URLs are configured for accessing Reporting Services applications. You can configure one or more URLs for accessing the Report Server Web Service and one or more URLs for accessing the report Web Portal. Although multiple URLs can be configured for web service and web portal, all URLs have to be connected to the web service virtual directory and the web portal virtual directory respectively. It is not possible to have multiple virtual directories.

Note

Note that Internet Information Services (IIS) is no longer used by Reporting Services to manage applications, and it is not required. All requests are completely handled by HTTP.SYS.

Getting ready

If you need to create or configure URLs using Reporting Services Configuration Manager, you need to understand how default URLs are created during the installation. This table explains how URLs are set by default:

Application

Instance type

Default URL

Reservation in HTTP.SYS

Web service

Default instance

http://<servername} reportserver<="" p="">

http://<servername}:80 reportserver<="" p="">

Web service

Named instance

http://<servername} reportserver_<instancename}<="" p="">

http://<servername}:80 reportserver_<instancename}<="" p="">

Web Portal

Default instance

http://<servername} reports<="" p="">

http://<servername}:80 reports<="" p="">

Web Portal

Named instance

http://<servername} reports_<instancename}<="" p="">

http://<servername}:80 reports_<instancename}<="" p="">

You can see this in the rsreportserver.config file located in the <installationpath}\Microsoft SQL Server\<instancename}\Reporting Services\ReportServer folder:

Figure 1.13

As mentioned before, you should not use any other tool to create or configure URLs, including the rsreportserver.config file.

In order to create or configure them, you need to make sure that you are a member of local administrator group of the server. In addition to that, if IIS is running in the same server, make sure that there are no virtual directories already configured with the names you have planned for Reporting Services virtual directories for applications.

How to do it...

Reporting Services Configuration Manager has two pages for managing URLs, one for web service applications, and another for web portal applications. It is shown in the following screenshot:

Figure 1.14

If you need to configure the Web Service URL, select the page and make the necessary changes:

  1. If you need to configure the Web Portal URL, you can select it to view and configure it. The preceding screenshot shows the settings related to the Web Service URL. As you see, the URL is formed by combining the machine name and virtual directory name.

  2. If you need to change the name of the virtual directory, you can simply change the text in the Virtual Directory input box. Remember that the name of the virtual directory is case sensitive, and only one virtual directory is allowed for the web service application.

  3. If you want to configure multiple URLs for the web service application, you can click on Advanced... and add more URLs.

  4. When you click on Advanced..., it opens a window that can be used to add both HTTP and HTTPS URLs. For testing purposes, let's allow users to connect with the web service using another port. Follow these steps to configure a URL with another port:

    1. Click on the Advanced... button to open the Advanced Multiple Web Site Configuration window.

    2. Click on Add in the HTTP section to add a new report server HTTP URL.

    3. Set the TCP Port as you want and click on OK to save it. Click on OK again to save the settings:

    Figure 1.15

  5. Once the settings are saved, you should see two URLs appearing: one with port 80, which is the default, and another with port 50000. When you set a port, make sure that it is not reserved by another application:

    Figure 1.16

  6. You can click on either URL to open the web service application. The configuration of Web Portal URL is the same as the Web Service URL. The Web portal URL page also has the Advanced... button for configuring additional URLs. Let's see how we can change the name of the virtual directory for web portal.

  7. For the Web Portal URL, the default virtual directory name is formed as Reports_<instancename>. As you can see in the following image, in our testing machine, it has been set as Reports_SQL2016_M and the URL has been formed as http://DINESH-LAP01:80/Reports_SQL2016_M:

    Figure 1.17

  8. Let's change the name of the virtual directory to ProductionReports, as shown next. Once changed, the Apply button must be clicked on to save the changes. Note that you need to make sure that there is no virtual directory in IIS with the same name, if IIS is installed in the same server:

    Figure 1.18

    You can click on the new URL and see whether it opens web portal or not.

How it works...

As we discussed before, Reporting Services Configuration Manager creates URL reservations in HTTP.SYS when changes are applied. If you open the rsreportserver.config file, you will see how changes are applied to the file.

As we set an additional port to the web service application, we can set additional IP addresses or hostnames for forming new URLs. One advantage with additional URLs is that you can provide different URLs for different audiences, as shown here:

  • For internal (or Intranet) users: http://server/reports

  • For external (or Internet) users: http://www.company.com/reports

Figure 1.19

Note

If you have multiple instances installed on the same machine, you need to have a proper naming convention to avoid conflicts with URL reservations.

There's more...

When accessing either web service or web portal in the local server, you can use localhost instead of an IP address or server name. You can simply call applications using http://localhost/reportserver and http://localhost/reports . The http://localhost URL is interpreted as http://127.0.0.1 . However, if you have configured URLs to the computer name or a single IP address, this will not work unless you have added an additional reservation for 127.0.0.1.

 

Configuring the Reporting Services database


Reporting Services stores and maintains different types of information such as settings, metadata, and objects related to its applications. Some of this information is stored in configuration files and some of it, specifically metadata and objects, is stored in a SQL Server database. Reporting Services native mode uses two databases named ReportServer and ReportServerTempDB by default. The ReportServer database is the primary storage for holding all metadata and objects. ReportServerTempDB is for maintaining temporary information.

Note

Reporting Services uses three databases if the mode is SharePoint integrated mode. In addition to the ReportServer and ReportServerTempDB databases, it uses a third one to hold altering metadata.

If the Reporting Services instance is the default instance, the names of these databases are set as Report Server and ReportServerTempDB. If the instance is a named instance, then the default names become ReportServer$<instancename> and ReportServerTempDB$<instancename>.

The Reporting Services database is used to maintain report definitions, report models, shared data sources, schedules, security information, and snapshots. You should treat this database as a normal database; hence, it should be regularly backed up and should be a part of High Availability and Disaster Recovery strategies.

ReportServerTempdDB stores temporary data, such as session and cache data. Since it maintains temporary data, you do not need to have a backup strategy for this database. However, if this database gets corrupted or accidently deleted, you can create it using the CatalogTempDB.sql query located in the <installationpath>\Microsoft SQL Server\<instancename>\Reporting Services\ReportServer folder.

Getting ready

Similar to Reporting Services URLs discussed in the previous recipe, if you selected Install and Configure during the installation, these two databases are created automatically as a part of the installation. If you selected Install only, then you need to use Reporting Services Configuration Manager to create databases. You can host these databases either in a local database engine instance or in a remote database engine instance. Reporting Services supports the following database engine versions for hosting its databases:

  • SQL Server 2016

  • SQL Server 2014

  • SQL Server 2012

  • SQL Server 2008 R2

  • SQL Server 2008

However, you need to make sure that the Reporting Services database is hosted in the correct edition:

  • Enterprise edition: The database can be hosted in Enterprise or Standard edition

  • Standard edition: It can be hosted in Enterprise or Standard edition

  • Web edition: The database can be hosted in Web edition (local only)

  • Express edition: The database can be hosted in Express edition (local only)

  • Developer edition: It can be hosted in Enterprise, Standard, or developer edition

How to do it...

You can use Reporting Services Configuration Manager to create and configure the report server database. Or you can use it to view or modify settings related to the database that is already configured. Use the following steps to create or modify the report server database:

  1. Open Reporting Services Configuration Manager and go to the Database page.

  2. You should see two sections: Current Report Server Database and Current Report Server Database Credential. Click on the Change Database button:

    Figure 1.20

  3. Change Database starts a wizard. You need to select a task from first page; select Create a new report server database to create a new one or select Choose an existing report server database to add an existing one. Select the first option for standalone report server.

  4. The next page allows you to select the hosting server and credentials to connect with it. Note that the credential required in this page is for creating the database only. It will not be used by Reporting Services later to connect with the database:

    Figure 1.21

  5. Select the hosting server and click on Next after setting the credentials. The next page allows you to set Database Name and Language.

  6. There is no restriction on the database name. You can enter the name of the database as you want. However, it is always better to have a proper naming convention and name it accordingly. As you can see, the name of the temp database is automatically set using the database name. Click on Next to continue:

    Figure 1.22

  7. The next page is for setting credentials to connect to the database by Reporting Services. You have three options: Windows credentials, SQL Server credentials, or Service credentials. If the database hosting server is a remote server, you should select either Windows or SQL Server credentials; otherwise, you can select one of these as per your requirements:

    Figure 1.23

  8. Everything required is set. Click on Next to continue. You should see a summary page with all your input. Continue with the wizard to complete the database configuration.

  9. If you go back to the second section of the Database page in Reporting Services Configuration Manager, you will see that there is a button named Change Credentials. This is for changing the credentials configured for Reporting Services to connect with the report server database. Although this gets configured either during the installation or while configuring the report database, you may use this option for changing credentials in the following situation:

    • The password set to the local or domain account is changed

    • The authentication type needs to be changed

How it works...

The wizard creates both ReportServer and ReportServerTempDB in the selected SQL Server instance. It uses codes stored in the <installationpath>\Microsoft SQL Server\<instance name>\Reporting Services\ReportServer folder to create these databases. Once the report server databases are created, report server uses the credentials stored for accessing report databases. During the configuration, it creates the encryption keys required and adds the necessary permissions for accessing the database.

Note

Users who access reports do not require permissions for report database or login for report database server. Report server uses credentials stored for accessing the database when required.

Reporting Services stores the connection for report database in the rsreportserver.config file in an encrypted format. Depending on the authentication type selected, some of the nodes related to the connection may be empty. As an example, if the authentication type selected is Service Credentials, then LogonDomain, and LogonCred are empty:

Figure 1.24

In order to verify the report database configuration, log in to the SQL Server instance that hosts the databases. You should see two databases configured with the names you used:

Figure 1.25

There's more...

Remember, Microsoft does not recommend using these databases directly to query or modify data. It is an internal database for report server and should not be accessed and modified.

See also

As mentioned before, Reporting Services uses three databases when the mode is SharePoint integrated mode. Read more about SharePoint integrated mode in Chapter 9, SharePoint Integration.

 

Executing post-installation tasks


Although the installation and configurations are done and everything works fine as you expected, it does not mean that the process of installation and configuration is completed. You have to do a few more tasks to complete it. These tasks make sure that your Reporting Services is protected and maintained properly.

Getting ready

There are a few key tasks you should perform after installation and configuration:

  • Make sure that report database is a part of your backup strategy

  • Back up the rsreportserver.config file

  • Make sure you have backed up Encryption Keys

  • The startup type of the services related to Reporting Services and database engine are set as Automatic

How to do it...

The required tasks can be performed using standard techniques and available tools:

  1. Taking a backup of the ReportServer database is easy with Management Studio. Or it can be a part of your scheduled backup job. The main thing to consider is that it should be treated as a normal database and backed up regularly. You may implement a strategy that has one backup type, such as Full Database backup, or based on the complexity in terms of number of reports it holds, frequency of changes, or heavy user interaction. There can also be a strategy that has a combination of various backup types, such as Full, Differential, and Log.

  2. To back up the rsreportserver.config file, you can take a copy of it and keep it in a safe place. Since most settings related to report server are held with this, you need to make sure that you have a backup copy of it.

  3. You can use Reporting Services Configuration Manager to back up Encryption Keys. This should be done because this requires many instances when recovering after failure. We will discuss Encryption keys in more depth in Chapter 10, Administering and Managing Reporting Services.

  4. In order to make the startup type of related services automatic, use Services Desktop Application. Note that you should not use this application to change any other settings.

How it works...

The key thing to understand is how quickly you respond to an issue, troubleshoot an issue, or how you can recover your reporting environment in a disaster. There are a few standard recovery strategies for reporting environments, but these post installation tasks will definitely help you to recover in most situations. As an example, if you make a mistake while changing the rsreportserver.config file manually, you can simply restore it to the previous stage if you have a backup copy of it. If, for some reason, your ReportServer database becomes corrupted, you can get it recovered using the database backup. Therefore, post installation tasks should not be ignored and must be a part of the installation and configuration process.

There's more...

You can secure your reporting server more by configuring a High Availability and Disaster Recovery strategy. Considering the demand, requirements, and the budget, a proper HA and DR should be implemented.

About the Authors
  • Dinesh Priyankara

    Dinesh Priyankara is an experienced professional and database enthusiast with skills in database management systems and business intelligence, especially on the Microsoft SQL Server product suite. Possessing over 16 years of experience on data related technologies, he does training, consulting, and is a top contributor to the local SQL Server community. In recognition of his contributions and expertise he has demonstrated, he was awarded the Microsoft Most Valuable Professional (MVP) award for SQL Server for 11 consecutive years since 2006. Dinesh is the Founder/Principal Architect of dinesQL (Pvt) Ltd (http://dinesql.com), a company that provides services on database, business intelligence, and training. In his spare time, he runs a blog at http://dinesql.blogspot.com with a follower base of over 100,000 from around the world and is a co-organizer of SQL Server Sri Lanka User Group (http://www.meetup.com/ssslug/). Even though he has written many articles on the database area, this is his first official book as a co-author.

    Browse publications by this author
  • Robert Cain

    Robert C. Cain (http://arcanecode.com) is a Microsoft MVP, MCTS Certified in BI, and is the owner of Arcane Training and Consulting, LLC. He is also a course author for Pluralsight, with over 11 courses to his credit. Robert is a team mate at Linchpin People, and co-author of four books. A popular speaker, Robert has presented at events such as the SQL PASS Summit, IT/Dev Connections, TechEd, CodeStock, and numerous SQL Saturdays. Robert has over 25 years’ experience in the IT industry, working in a variety of fields including manufacturing, insurance, telecommunications and nuclear power.

    Browse publications by this author
Latest Reviews (3 reviews total)
Nachschlagen während des Entwickeln
Encontré el tema que estaba buscando, tal vez falta calificar el libro en nivel (Básico, intermedio etc.)
good coverage of the product. may be more examples would be beneficial
SQL Server 2016 Reporting Services Cookbook
Unlock this book and the full library FREE for 7 days
Start now