SQL Reporting Services in clouds. Part 3: Multi-Tenant

by Pavlo Revenkov

The previous post was devoted to configuring SQL Reporting in Windows Azure. We've looked at two alternate configurations for report publishing services: as a service (SQL Reporting), and using an SQL Server virtual machine (SSRS). Now, let's have a look at the SQL Reporting services and the SQL Server configuration method which supports multi-tenant scenarios, when a single reporting service can be used for different data sources.

Publishing the project

1. Open the reporting project in SQL Server Business Intelligent Development Studio.
2. Choose Properties in the project context menu.
3. We will differentiate customer reports by using separate root folders. In every field containing the "Target?Folder" prefix, define the root folder with the customer's name.

4. Publish the reporting project for different customers in the SQL Reporting service and/or on a SQL Server virtual machine.

Multi-tenant: SQL Reporting

1. Go to -> Windows Azure Management Portal -> SQL Reporting -> Users.
2. Create a new account for every customer.


3. Open the Items tab.

4. Select the customer reports folder you want to configure and click on Manage Permissions. 5. Click on Assign item-specific permissions and remove all users that should not have access to the selected reports folder from the list.

Multi-tenant: SQL Server Reporting Services

As I mentioned before, both methods use role-based reports folder access control based on Windows Authentication. Thus, every owner of a Windows Account with access to the SQL Server virtual machine can use SQL Server Reporting Services.

Creating a Windows Account

1. Connect to the SQL Server virtual machine via Remote Desktop.
2. Go to the Windows Control Panel and select Add or remove user accounts.
3. Click on Create new account.
4. Define the account name and type for the new user.

5. Click on Create account.
6. Select the new user and click on Create a password.
7. Enter the password, confirm it and click on Create password.

Report Manager Configuration

1. Go to the URL matching the following pattern: "http://YOUR_MACHINE_NAME.cloudapp.net/Reports". Use the virtual machine administrator credentials for access.

2. Select the customer's reports folder and click on Folder Settings.
3. Open the Security tab and click on Edit Item Security.
4. Select New Role Assignment.

5. Enter the name of the user and select the roles you want to assign to this user.

Conclusion

As you can see, both methods provide identical capabilities for report generating services. A regular SQL Server can also be used to connect to the database using Widows Authentication mechanisms. SQL Azure and SQL Reporting Services do not support this capability, and this is a well-known constraint of the Windows Azure platform.

Price-wise, the SQL Reporting Service is preferable for solutions where reports are not generated very often and presented as static content. If your application or its users generate a large amount of reports over time, then SQL Server Reporting Services hosted on a separate virtual machine are the preferable solution. All the regular SQL Server capabilities will come as a nice bonus.

Keep in mind that before deciding which report service implementation method to use, you must thoroughly analyse the expenses and resources involved.