How to configure SQL Server Reporting Service (SSRS) for Safewhere Identify reports

Introduction

In this article, we will show you how to install and configure the SQL Server Reporting Service (SSRS) in SQL Server 2016 to send out Safewhere Identify reports to target audience eventually using the Email subscription feature.

Install and Configure SSRS

Prerequisites

You have the same SQL Server version installation media that was used when the SQL Server is being installed.

  • Create a new service account for Reporting Service. (Microsoft recommends to specify and use a separate account for each SQL Server service)
  • Specify the authentication type and the account that have permission to connect to the database server. (Will be used during configuring the Report Server DB)
  • Specify the authentication type and the account that the report server will use to connect to the report server database. (Will be used during configuring the Report Server DB)

SSRS Installation

Run the SQL installation file and select “Installation
image3
Click “New SQL Server stand-alone installation or add features to an existing installation”.
image4
In "Global Rules", wait for checking the problem that might occur when installing SQL Server setup files. Failure must be corrected to continue.
image5
In "Microsoft Update", it's recommended to check the Microsoft update for security and important updates but at the end, it's up to you to check it or not.
image6
In "Install Setup files", if an update for SQL Server setup is found and specified to be included the updated will be installed.
image7
In "Install Rules", wait for checking the potential problems that might occur while running setup. Failure must be corrected to continue.
image8
In "Installation type", specify add features to an existing instance of SQL Server 2016.
image9
In "Feature Selection", check Reporting Service - Native.
image10
In "Server Configuration", specify a separate Service account for Reporting Service mentioned in Prerequisites.
image11
In "Reporting Service Configuration", specify the "Install only" configuration mode.
image12
Finally, click “Next” to run the setup then wait till the installation is completed successfully.
image13

SSRS Configuration

General Settings

Once the SSRS feature is installed successfully, open Reporting Service Configuration Manager
image14
Connect to the Report Server Instance.
image15
Review Report Server Status and make sure it's started.
image16
In "Service Account", you will find the report server service account that runs the Report Server Service.
image17
Go first to the "Database" section to configure the database that holds the all report server content and application data. Click “Change Database” to create the report server database for the first time.
Check “Create a new report server database” then click “Next
image18
Make sure the database server is correct.
Set the authentication type as (Integrated User or SQL Server account). Note: this account must have permission to connect to the database server.
Provide the correct account credentials.
image19
Test and make sure that the connection is established successfully.
image20
Specify the “Database Name” as you prefer.
image21
Specify the credentials of an existing account that the report server will use to connect to the report server database.
image22
Review the summary information, and make sure that everything is set properly before proceeding.
image23
Wait while the report server database wizard configures the database then click “Finish”.
image24
Go back to configure the "Web Service URL".

  • Set the “Report Server Web Service Virtual Directory”.
  • Set the “Report Server Web Service Site identification” based on your requirements then click "Apply".

image25
Test the Report Server Web Service URL that should be now browsed properly.
image26
Go now to the "Report Manager URL", specify the virtual directory as you prefer then click Apply to configure it.
image27
Once the Report Manager Completed Successfully, Click the Report Manager URL to test and ensure it should be now browsed properly.
image28
Go to the "Encryption keys" to take a backup of the symmetric key that used to encrypt sensitive data in the report server database like connection strings, credentials ...etc. it will be helpful in case you migrate or move the report server installation to another server, you can restore this key to regain access to the encrypted content.
image29

E-mail Settings for subscription

Go to the “E-mail Settings” section to configure the SMTP settings to be used to send reports out. Following settings need to be filled in:

  • Sender Address: fill in the sender’s email address
  • SMTP Server: fill in the name or IP Address of the SMTP server used to send reports
  • Email credential: fill in the Username, Password and Confirm Password.
  • Use secure connection: check this if the SMTP server requires SSL encryption

Note: it is recommended that outbound rules should be added in Windows Firewalls to allow using SMTP port (default 25 for non secure and 587 for secure connections - ask system administrator if you do not know what ports are using)
image30

Install Report Builder

The Report Builder is used to create and collect Safewhere Identify Reports.
Download link: https://www.microsoft.com/en-us/download/details.aspx?id=53613
Run "ReportBuilder3" installation file and follow the installation steps till complete. Once installation is successful, you can launch Report Build from the Report Manager web site (Web Portal).

Configure Safewhere Identify reports

Safewhere Identify*Admin settings

It is required that Safewhere Identify logs must be written to SQL Server in order to generate reports. The following steps will show you how to configure it.
Login to Safewhere Identify*Admin, go to System Setup > Logging
image31
In “Log User Requests” section, check “Log user requests to the "Log target" as specified in the setting below
image32
In “Log target” section, select the “SerilogSinks” option
image33
Then click button ”Save” to save the settings.

Add reports for Safewhere Identify

Add reports from scratch

image34
Allow the report builder to open
image35
Select “Table or Matrix Wizard
image36
Select “Create new dataset
image37
In “Data Source Connection”, select “New” to add connection string
image38
Fill in the “Name” for the Report and click button “Build” to build the new connection string or edit the existing one.
Select the connection type depends on your system environment (Identify only supports Microsoft SQL Server and Microsoft Azure SQL Database). In this article we use “Microsoft SQL Server”.
image39

  • Data source: select “Microsoft SQL Server (SqlClient)
  • Server name: fill in your SQL Server name
  • Log on to the server: chose the logon method and fill in the credential.
  • Connection to a database: select IdentifyAudit

image40
lick “Test Connection" button to ensure that Connection string is valid.
Click "OK" then complete the setting for Data Source Connection.
Click “Next” on the next screen
In the Design a query window add settings then click “Next”:

  • Database view” window, select the Identify instance (dev for this article) then expand to check the “Logs” table
  • Click the “Filter” button to add filter
    • Field name is “Type
    • Value is “ANALYSIS

image41
In the “Arrange fields” drag & drop what fields (Available fields) you wants to display in the “user logon history” report to the “Values” then click Next
image42
Click "Next" and preview the settings
image43
Click "Next" then click "Finish" to complete the report builder
In Report Builders windows, click to add the report title and click “File” > “Save As” or “Save” to save the report
image44
You can also click the Run button under File menu to see how report is generated
image45
Close the Report Builders application and back to Report Manager page, the new added report is displayed
image46

Add reports using existing *.rdl files

We created some sample reports in *.rdl files which can be modified using Report Builder then uploaded to the SQL Server Reporting Service to generate reports

Edit *.rdl report files

Using Report Builders to open the *.rdl file, you need to modify the Data Sources and Datasets by double clicking on sub items.
image47
Data Source: It is recommend that you need to update the Data Source value (replace “dc2k16” by the SQL Server instance name) and the Credentials then clicking on "Test Connection" button to test new settings.
Dataset: in dataset properties you can modify the Query, add/remove fields or set new Filter as you wish.
image48
Data Region: where you can modify the Report tile, report data (the first row is report header, second row is report data which value is the field name in Dataset)
image49
After completing modify the *.rdl file in Report Builder, you can click the Run button to generate report, following is sample report generated
image50

Upload to SQL Server Reporting Service

Click on the "Upload" button, point to *.rdl report file then complete the upload process.
image51
The new uploaded report will be displayed
image52
You can click on the report to generate the report data or configure to export it to other file format (e.g. PDF) and send out via email as following instruction.

Email subscription settings for each report

Before doing that, ensure that the “SQL Server Agent (MSSQLSERVER)” service is started and running.
image53
Open the Report Manager web site
Click on the “...” link and click MANAGE to edit the Subscription email setting
image54
Click on “ Subscriptions” menu on the left hand side
image55
In case you see the exclamation icon next to “New subscription”, it is usually because the credential to access data source is invalid, do the following steps to correct it.
Click the “Data sources” menu, ensure the “A custom datasource” choice is selected and go “Credential” section
Select “Using the following credential” and fill in your credential then click the “Test Connection” button to ensure that your credential is valid. (It will also store the credential securely into report server)
image56
Save and back to “Subscriptions” page. This time the icon will be changed to “+
image57
Click “New subscription” to add email subscription

  • Fill in ”Description
  • In the "Schedule" section select “Report-specific schedule” and click “Edit schedule” link.

image58
Modify your settings then click “Apply” to complete edit schedule
image59
In “Destination” section, select “E-mail” option
image60
In the “Destination” section, fill in the email addresses and select “Render Format” - “PDF” option, you can also modify the subject
image61
Click “Create Subscription” button to complete.