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”

Click “New SQL Server stand-alone installation or add features to an existing installation”.

In "Global Rules", wait for checking the problem that might occur when installing SQL Server setup files. Failure must be corrected to continue.

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.

In "Install Setup files", if an update for SQL Server setup is found and specified to be included the updated will be installed.

In "Install Rules", wait for checking the potential problems that might occur while running setup. Failure must be corrected to continue.

In "Installation type", specify add features to an existing instance of SQL Server 2016.

In "Feature Selection", check Reporting Service - Native.

In "Server Configuration", specify a separate Service account for Reporting Service mentioned in Prerequisites.

In "Reporting Service Configuration", specify the "Install only" configuration mode.

Finally, click “Next” to run the setup then wait till the installation is completed successfully.

SSRS Configuration

General Settings

Once the SSRS feature is installed successfully, open Reporting Service Configuration Manager

Connect to the Report Server Instance.

Review Report Server Status and make sure it's started.

In "Service Account", you will find the report server service account that runs the Report Server Service.

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”

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.

Test and make sure that the connection is established successfully.

Specify the “Database Name” as you prefer.

Specify the credentials of an existing account that the report server will use to connect to the report server database.

Review the summary information, and make sure that everything is set properly before proceeding.

Wait while the report server database wizard configures the database then click “Finish”.

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

Test the Report Server Web Service URL that should be now browsed properly.

Go now to the "Report Manager URL", specify the virtual directory as you prefer then click Apply to configure it.

Once the Report Manager Completed Successfully, Click the Report Manager URL to test and ensure it should be now browsed properly.

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.

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)

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

In “Log User Requests” section, check “Log user requests to the "Log target" as specified in the setting below”

In “Log target” section, select the “SerilogSinks” option

Then click button ”Save” to save the settings.

Add reports for Safewhere Identify

Add reports from scratch

Allow the report builder to open

Select “Table or Matrix Wizard”

Select “Create new dataset”

In “Data Source Connection”, select “New” to add connection string

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

• 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

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”

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

Click "Next" and preview the settings

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

You can also click the Run button under File menu to see how report is generated

Close the Report Builders application and back to Report Manager page, the new added report is displayed

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

• User logon history - all.rdl: User’s logon history report
• User consent history - all.rdl: User’s consent history report

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.

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.

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)

After completing modify the *.rdl file in Report Builder, you can click the Run button to generate report, following is sample report generated

Upload to SQL Server Reporting Service

Click on the "Upload" button, point to *.rdl report file then complete the upload process.

The new uploaded report will be displayed

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.

Open the Report Manager web site
Click on the “...” link and click MANAGE to edit the Subscription email setting

Click on “ Subscriptions” menu on the left hand side

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)

Save and back to “Subscriptions” page. This time the icon will be changed to “+”

Click “New subscription” to add email subscription

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

Modify your settings then click “Apply” to complete edit schedule

In “Destination” section, select “E-mail” option

In the “Destination” section, fill in the email addresses and select “Render Format” - “PDF” option, you can also modify the subject

Click “Create Subscription” button to complete.