Understanding SQL Server Security

Even if an end user is allowed to run a report, the SQL Server instance my not return the requested data. The settings in the data source determine the credentials sent to SQL Server.

In the case of SQL Server, two types of accounts will be used to connect: Windows logins and logins defined within the SQL Server instance. Both types of logins are mapped to database users which have specific permissions within the database. Another feature of SQL Server called Contained Databases allows you to create users directly within the database. Figure 9-1 illustrates these concepts.

Figure 9-1. Authentication within SQL Server

Configuring Site Security

SSRS security is role based. The roles have certain predefined permissions. Adding an account or group to a role allows the account or group to have the permissions defined by the role. Some aspects of security can be configured at the site, while others are based on location or object. In this section, you will learn about site security.

I have created an information technology (IT) group and added the following local Windows accounts to the group on my server:

• CIO

• Director

• Manager

• TeamLeader

• TeamMember

At the Site level of the web portal, there are two possible roles: System Administrator and System User. Any accounts or group members in the System Administrator role have full control of the site, including controlling security. The System User property is important for anyone who will be using Report Builder, which you will learn about in Chapter 10. To add an account to a site-level role, follow these steps:

1. Launch the web portal.

2. Click the gear icon and select Site Settings as shown in Figure 9-10

   

   Figure 9-10. Select Site settings

3. This opens the site properties. Click Security.

4. Click Add group or user as shown in Figure 9-11.

   

   Figure 9-11. Click Add group or user

5. For Group or user, type in CIO.

6. Select System Administrator as shown in Figure 9-12.

   

   Figure 9-12. Adding an account

7. Click OK to create the role membership.

8. Repeat the process to add the Manager account to the System User role.

You can also remove accounts from the site-level roles on this page.

Configuring Folder and Report Security

Before you publish the first report in your company, you should plan the folder structure within the web portal to simplify security management. End users can view those folders and reports that they have rights to see. As a best practice, configure security at a folder level only. Security can be configured on individual reports, but that makes long-term management very difficult.

To reach a particular folder, an end user should have rights to the folders above it. By default, folders inherit permissions from the parent folder and reports inherit permissions from the folder in which they are located. Permissions should be set to be more restrictive as you travel down the path. For example, you may want to grant rights to everyone in the domain at the Home folder. Then create folders for each department under Home. Within each department folder, create folders for managers.

There are several roles defined at the folder and object level. Table 9-1 lists the roles and definitions as displayed on the Security page.

Follow these steps to create and configure the folders for the IT department:

1. In the web portal, navigate to the Home folder. If you are in Site Settings, click SQL Server Reporting Services at the top of the page.

2. Click Manage Folder as shown in Figure 9-13.

   

   Figure 9-13. The Manage Folder icon

3. This opens the Security page of the Home folder. Click New Role Assignment.

4. Type in Users. For a domain, this will be the Everyone group.

5. Check Browser as shown in Figure 9-14. This will give all users rights to run any report in the site unless the security in a folder has been overridden.

   

   Figure 9-14. Add Users to the Browser role

6. Click OK to save the change.

7. Click the Home link to navigate back to the folders.

8. Click New Folder.

9. Fill in IT for the name and click Create.

10. Click the ellipses next to the new IT folder and select Manage.

11. This opens the folder properties. Click Security.

12. Click Customize security as shown in Figure 9-15.

    

    Figure 9-15. The security page of the folder

13. This brings up a warning about breaking inheritance from the parent as shown in Figure 9-16. Click OK.

    

    Figure 9-16. Confirm that security will be different

14. Click Add group or user.

15. Add the IT group to the folder in the Browser role and click OK.

16. Check the box next to BuiltinUsers. If you are working within a domain, remove the Everyone group.

17. Click Delete and OK to confirm.

18. Navigate back to the Home folder.

You now have a folder that can only be viewed by members of the IT group. Continue adding folders and modifying permissions using the skills learned in the previous steps according to Table 9-2 to set up security for the site.

Obviously, this is a simplified example, but it should illustrate how security should be configured. Figure 9-17 demonstrates how the number of accounts diminishes the further down you go in the path.

Figure 9-17. The folder permissions

To test, run the browser as any of the accounts to make sure that the account can only see the expected folders. Figure 9-18 shows the difference between what the Director and TeamLeader see when each is looking at the IT folder.

Figure 9-18. The Manager’s and TeamLeaders’s views of the folder

At this point, the Manager account has control of the content of most of the folders and permission to use Report Builder. You will see Report Builder in action in Chapter 10.

Sending Reports Automatically with Subscriptions

One of the nicest features of SSRS is the ability to schedule reports to run and be delivered automatically. The reports may be sent via e-mail or delivered to a network share. When delivering an e-mail report, either the report may be embedded in the e-mail message or a link can be sent.

Because subscription delivery does not involve human intervention, there are two requirements that must be met for a subscription to be created for a particular report. All data sources used by the report must contain hard-coded credentials . Default parameters must be determined and configured in the subscription.

In previous versions of SSRS, an SMTP (Simple Mail Transfer Protocol) server within your domain was required to send reports through e-mail. Starting with SSRS 2016, it is now possible to use an e-mail server outside your domain such as gmail to send reports. To send reports to a network share, the share must be created. A Windows account must have permission to create the report in the share, and the appropriate end users must have permission to open the files. SQL Server Agent must also be running on the server that hosts the ReportServer database for both types of subscriptions.

Before you can send reports through e-mail, the SMTP account settings must be configured. Select the E-mail settings page to fill in the SMTP properties. See the Reporting Services Team blog found at https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/07/15/deliver-reports-via-emailusing-an-email-server-outside-your-network/ for information about how to configure the settings for several types of services.

It is much simpler to configure the settings for delivery of a report to a network share than via e-mail. The example in this section will cover delivering the report to the share. When you configure a subscription to deliver a report to a share, you will need to specify a File Share Account which has permission to create the file. You can set up one account for the entire SSRS instance, or you can specify an account for individual subscriptions. Figure 9-19 shows the properties for the File Share Account that can be used for subscriptions delivered to network shares.

Figure 9-19. File Share Account properties

You will also need to create the share where the reports will be delivered. To configure a network share , follow these steps:

1. Navigate to the C: drive or another drive on your computer.

2. Create a new folder named Reports.

3. Right-click the folder and select Properties.

4. Select the Sharing tab.

5. Click Share which brings up the File Sharing dialog.

6. Add any accounts and permissions required for the share as shown in Figure 9-20.

   

   Figure 9-20. The File Sharing dialog box

7. Click Share, Done, and Close to create the share and close the dialogs.

Once the network share is created, launch SSMS and make sure that SQL Server Agent is running. SQL Server Agent is the service which runs scheduled jobs for SQL Server. SSRS subscriptions are just a type of job as far as SQL Server is concerned.

Now that you have the infrastructure in place, you can create a subscription. Follow these steps to configure the subscription:

1. In the web portal, navigate to the Dynamic Reports folder.

2. The Sales by Territory Matrix report should be connected to the AdventureWorks2016SQL data source with stored credentials. If not reconfigure the data source.

3. Test the Sales by Territory Matrix report.

4. Navigate back to the Dynamic Reports folder.

5. Click the ellipsis next to Sales by Territory Matrix and select Manage.

6. Click Subscriptions.

7. Click New Subscription.

8. On the Subscription property page, type Sales by Territory in the Description property.

9. The Owner property should automatically default to your account.

10. For this example, accept the default schedule. For a production subscription, be sure to create a schedule. Figure 9-21 shows the properties so far.

    

    Figure 9-21. The subscription properties

11. Scroll down to the Destination property and select Windows File Share.

12. Type in the path to the network share.

13. Select PDF for the Render Format.

14. If you wish to override the File Share Account , select Use the following Windows user credentials. Fill in the User Name and Password for the specific account.

15. Modify the Overwrite Options according to your requirements. This section of properties should look similar to Figure 9-22.

    

    Figure 9-22. The delivery options of the subscription

16. Scroll down to the Report Parameters section.

17. Select a value for the Year parameter.

18. Once the Year value is set, the values for Territory will populate. Select a territory. The parameter properties should resemble Figure 9-23.

    

    Figure 9-23. The Report Parameters of the subscription

19. Click Create Subscription.

20. The subscription should now be visible in the list as shown in Figure 9-24.

    

    Figure 9-24. The new subscription

A SQL Server Agent job is created for each subscription. Unfortunately, the name of the job is a globally unique identifier (GUID), and you can’t tell by looking which job goes with each subscription. To figure out which subscription maps to each job, you will need to run a query like the following in your ReportServer database :

SELECT Name AS ReportName, ScheduleID AS JobName, s.[Description]
FROM [Catalog] c
JOIN Subscriptions s ON c.ItemID = s.Report_OID
JOIN ReportSchedule rs ON c.ItemID = rs.ReportID
    AND rs.SubscriptionID = s.SubscriptionID;

Figure 9-25 shows the results of the query.

Figure 9-25. The report name mapped to the job name

To test the subscription, run the job. The job will report success once it kicks off the subscription. It will report success even if the report is not delivered. You can see the results by checking the share for the report or refreshing the Subscriptions page to see the status.

In previous versions of SSRS, you had to navigate to every folder and report to find and manage subscriptions. The web portal has a new feature called My Subscriptions . By clicking the gear in the menu, you will see a link to My Subscriptions. Here you can see the list of subscriptions owned by the account you are using and their locations. You can also enable, disable, or delete your subscriptions. Figure 9-26 shows the link and the list.

Figure 9-26. The new My Subscriptions feature

Securing Delivery

By default, report data travels through the network unencrypted. This may be a security risk in your environment. You can configure SSRS to use the secure socket layer (SSL) protocol. This is the network protocol used to connect securely that you see when buying something or visiting your banking web site. To properly configure SSL, you need to purchase a certificate from a trusted certificate authority.

Once you have obtained a certificate and installed it on the server, it can be configured by launching the Reporting Service Configuration Manager. On the Web Portal URL page, click Advanced. The SSL certificate can be bound to SSRS by clicking Add at the bottom of the Advanced Multiple Web Site Configuration dialog box. Figure 9-27 shows the dialogs you will use to configure SSL.

Figure 9-27. The dialogs for configuring SSL

Summary

Securing your reports is very important, but it can become very complex and difficult to manage if you don’t have a strategy before you start publishing reports. Make sure you figure out the layout of the folders and never configure security on a report, only on a folder.

Security is configured in two layers: at the data source and in SSRS. Predefined server and folder roles make security simpler to manage. You can also configure reports to be delivered automatically through subscriptions.

Chapter 10 covers creating reports directly from the web portal with Report Builder. Two types of reports new with 2016, KPIs and Mobile Reports, will be covered as well.