After the native installation of the SQL Server Reporting Service, we may need to customize some settings of Report Server. SQL Server Reporting Service provides a tool that is named as such; Report Server Configuration Manager. This tool helps us to customize SQL Server Reporting Service parameters and configurations. With the Report Server Configuration Manager, we can change and customize various parameters of the SQL Server Reporting Service. We can find the below settings in Report Server Configuration Manager:
- Service Account
- Web Service URL
- Database
- Web Portal URL
- E-Mail Settings
- Execution Account
- Encryption Keys
- Subscription Settings
- Scale-out Deployment
- Power BI Service (cloud)
We can find and run the Report Server Configuration Manager on the Windows start screen.
- Type Reporting in Windows start screen
-
Click Report Server Configuration Manager.
In the main tab of configuration manager, we can start and stop Report Server service and at the same time we can find out Report Server edition, product version and other main information about SQL Server Reporting Service.
Service Account: In this tab, we can configure Report Server Windows service account. In the built-in account selection box; we can find Network Service and Virtual Service Accounts. These accounts have minimum privileges which they require to run Report Server and have network login permissions. Microsoft recommends that if we don’t have any domain account we can use a Virtual Service Account. Because this account allows us to avoid password expiration or Report Server breakdowns, but if we have a domain account we can create a dedicated account for Report Server and can customize required security policies to this account and can use this account to run Report Server.
Web Service URL: In this setting tab; we can customize network policies which have access to Report Server web URL. So we can control incoming request to Report Server. In this configuration tab we can configure;
- Can filter the web service request
- Can change web service request ports for HTTP and https in Advanced tab
- Can change virtual directory name for the ReportServer. Default virtual directory name is ReportServer.
- Can bind and configure SSL (Secure Sockets Layer) certification.
Database: The Report Server database stores all report contents, subscriptions and other several data (Dataset, Data source, Roles etc.) which required by Report Server. In this tab, we can configure the Report Server database connection login and password setting. In the default Report Server installation, two databases are created which are named as ReportServer and ReportServerTempDB. At the same time, this tab provides us to change Report Server database.
In Report Server database, we can find a handy view which is named as ExecutionLog2.
This view helps us to find out the usage frequency of reports, execution times and what parameters are used in the reports. By default, this log is kept for 60 days. We can change this setting to connect the SSRS instance with the management studio.
- Open Microsoft SQL Server Management Studio and run as “Administrator”
-
Select Server type as Reporting Services and click OK
-
Right click on Report Server instance and select Properties.
-
Click Advanced tab and change ExecutionLogDaysKept
The other method is;
-
Click Logging tab and change default number. In this tab execution logging option can disable.
Web Portal URL: In this configuration tab, we can configure web portal virtual directory name and we can change request ports and filter IP address like Web Service URL.
E-Mail Settings: In this configuration tab, we can configure e-mail account for mail subscriptions. Report Server allows us 3 type of authentication methods:
- No authentication
- Username and password for SMTP
- Report server service account NTLM
Now, we will look at how to create an e-mail subscription on SQL Server Reporting Service
- Open report server web portal
-
Click (…) icon which we want to create mail subscription of report
- Chose to Deliver the report to as E-Mail
-
Fill Delivery options (E-Mail)
- Click Edit schedule and create required subscription schedule
-
Click Apply
- Click Create Subscription
After completing all these steps about email subscriptions, Report Server will create a job on SQL Agent. If our SQL Agent does not run, we get an error about this.
When we look at the SQL Agent jobs we can find out a job which represents Report Server email subscription.
At the same time, we find some information about this job in Report Server database, the below query returns the following information about subscription.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Schedule.EventType, Subscriptions.MatchData, Schedule.ScheduleID, Subscriptions.[Description], Schedule.StartDate, Schedule.LastRunTime FROM ReportServer.dbo.ReportSchedule ReportSchedule INNER JOIN ReportServer.dbo.Schedule Schedule ON Schedule.ScheduleID = ReportSchedule.ScheduleID INNER JOIN ReportServer.dbo.Subscriptions Subscriptions ON Subscriptions.SubscriptionID = ReportSchedule.SubscriptionID |
At last, you can find logs of subscriptions in My Subscription page.
Encryption Keys: In this configuration page, we can find out encryptions keys configurations. Encryption keys are most important for Report Server stored connection strings and other sensitive data in Report Server database and this data is encrypted by these symmetric keys.
|
1 2 3 4 5 |
SELECT [ConnectionString] ,[UserName] ,[Password] FROM [dbo].[DataSource] |
For this reason, our first task after the installation of the Report Server should be the backup of this encryption key. Because when we need to move or change the location of the Report Server location. This encryption key provides us to recreate connection strings username and password successfully. If we delete the encryption key Report Server it will clear all connection string usernames and passwords and other sensitive data. We will need to redefine this data.
Backup encryption key
- Click Backup
- Define file location for encryption key
- Set password for encryption key
- Click OK
Subscription Settings: We mentioned about file subscriptions under the email set heading. If we don’t set this setting, the Report Server tries to access file paths with their service account privileges. But in some special security policies service accounts cannot access defined file subscription keys. To handle this issue, we can use file share account. This setting allows us to use different accounts to access file paths.
Scale-out Deployment: In this model, we can run multiple Report Server and can connect one Report Server Database. The benefit of Scale-out Deployment;
- Increase high availability of Report Server
- Handle more concurrent users at the same time without performance issue
- Network load balancing can configure
Conclusions
SQL Server Reporting Services provides us with several configuration settings. With these settings, we can customize the Report Server and adapt it to our business requirements. If you ask my opinion SQL Server Reporting Service is an advanced solution for reporting and business intelligence.
References
- Configure a Native Mode Report Server Scale – Out Deployment
- Subscription Settings and a File Share Account (Configuration Manager)
- Encryption Keys (SSRS Native Mode)
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023