In this chapter, we will focus on the configuration settings in SQL Server Reporting Services (SSRS) that will make this functionality work optimally. (See details on installation of SQL Reporting Services Integrated Mode in Chapter 35.)
This chapter covers:
Current version configuration
Provisioning subscriptions and alerts
Email configuration
Key management
SQL 2012 SP1 enhancements
Leveraging EffectiveUserName
One helpful feature that was released with SQL Server 2012 is the ability to set up subscriptions and alerts in SQL Server Reporting Services Integrated Mode. This new feature allows users to set notifications and regularly deliver reports via email, network share, or a SharePoint library.
From a report, click on the Actions menu and you will see New Data Alert and Subscribe options (see Figure 40-1).
In order to support this new functionality, you must first configure the Service Application settings in Central Administration by going to Application Management and selecting the Reporting Services Service Application.
The first setting that needs to be configured is the SQL Server Agent access (Figure 40-2). This is done using the Provision Subscriptions and Alerts screen.
In this screen, you will validate that the SQL Server Agent is running. From here, you have two options to configure Reporting Services to access the SQL Server Agent:
Central Administration
SQL script
Both options are viable and achieve the same end result, however the guaranteed method of granting permission to a SQL Server is to run a T-SQL script directly on the server.
Once this has been completed, you will be able to set alerts and subscriptions on SSRS Integrated Mode reports—but you will not be able to utilize the mail capabilities yet.
To configure the email capability of SSRS, you will need to configure an SMTP outbound server. Our best recommendation is to leverage your existing email infrastructure. As part of your initial SharePoint configuration, you most likely configured outbound email in Central Administration under the System Settings and then configured outgoing email settings (Figure 40-3). These settings should be mirrored in Email Settings for your Reporting Services Service Application.
The SQL Server Reporting Services Encryption Key is a vital part of the SSRS infrastructure. At a minimum, it is critical to create a backup copy of the symmetric key used for encryption.
Some of the common uses for the backup SSRS key are:
Changing the report server Windows service account name or password
Renaming the computer or instance that hosts the report server
Migrating a report server installation or configuring a report server to use a different backing database
Recovering a report server installation due to a failure
To back up the encryption key, go to the Reporting Services Service Application and select Key Management and then choose the Backup Encryption Key option. You will be prompted to enter a password to protect the encryption key. This should be a strong password.
One of the new great features of SQL Reporting Services (SSRS) for SharePoint 2013 that enables Power View is the EffectiveUserName. EffectiveUserName allows a user to pass their credentials from Power View to the PowerPivot BISM model on the web server that leverages the Claims to Windows token service to pass the credential to the SSRS Integrated Mode instance on the App Server. The SSRS Integrated Mode service application then passes the EffectiveUserName parameter as a part of the request string to the SQL Server Analysis Services (SSAS) Tabular Mode instance. This allows the initial user credential to be passed from the Power View request to the back-end SSAS Tabular mode instance. Figure 40-5 is a visual representation of this implementation.
By passing the credential this way it is enabling a double hop scenario without requiring Kerberos to be configured on the SharePoint and SQL servers.
In order to accomplish getting around the double hop issue, the service account running SSRS Integrated Mode must have administrator rights on all of the SSAS instances that will host Tabular BISM models. When a SharePoint user clicks on a BISM model in a document library, it opens Power View and a connection to the SSAS Tabular Mode instance.
The connection between Power View and a tabular model database performs a double hop authentication sequence where the credential is sent from Power View to SharePoint, and then from SharePoint to a SSAS Tabular Mode instance. The ADOMD.NET client library that handles the connection request always tries Kerberos first. If Kerberos is configured, the credential is impersonated using EffectiveUserName on the connection to the tabular model database, and the connection will then succeed.
To set the administrator rights to the SSAS instances you must do the following:
In SQL Server Management Studio, connect to the Analysis Services instance.
Right-click the server name and select Properties.
Click Security, and then click Add.
Enter the service account that is used to run the service application.