SQL Reporting Services in the Cloud. Part 3: Multi-tenant

So, the previous part was devoted to setting up the SQL Reporting service in Windows Azure. We examined two options for configuring report publishing services: as a service (SQL Reporting) and using the SQL Server virtual machine (SSRS). Now let's look at a way to configure SQL Reporting and SQL Server services to support multi-tenant scenarios when a single reporting service can be used for different data sources.

Project Publishing

1. Open the report project in “SQL Server Business Intelligent Development Studio”;
2. In the context menu of the project, select “Properties”;
3. We will separate reports from one customer from another using different root directories. To do this, specify the root directory with the name of the customer in the values ​​of the fields with the prefix “Target ... Folder”;
   
   
4. Publish the reporting project for different customers in the SQL Reporting service and / or SQL Server virtual machine.

Multi-tenant: SQL Reporting

1. Go to the Windows Azure Management Portal, section “SQL Reporting”, tab “Users”;
2. Create for each customer an account:
   
   
   
3. Go to the “Items” tab;
   
   
4. Select the catalog with customer reports that you want to configure and click the “Manage Permissions” button;
5. Select “Assign item-specific permissions” and leave in the list of users only those who will have access to this directory of reports.
   
   

Multi-tenant: SQL Server Reporting Services

As mentioned earlier, both solutions use a mechanism to differentiate access rights to report directories based on roles. This authentication method is based on Windows authentication. This way, every user who has a Windows account to access the SQL Server virtual machine can use SQL Server Reporting Services.

Create a Windows account

1. Connect to the SQL Server virtual machine via RDP;
2. Go to the Windows Control Panel and select “Add or remove user accounts”;
3. Select the link “Create new account”;
4. Specify the name of the new user and its type;
   
   
5. Click the “Create account” button;
6. Select a new user and follow the link “Create a password”;
7. Enter user password;
   
   

Setting up Report Manager

1. Go to the following URL: “http://YOUR_MACHINE_NAME.cloudapp.net/Reports” . To access, use the login and password of the administrator of the virtual machine;
   
   
2. Select a customer report catalog. Then click the “Folder Settings” button;
3. Go to the “Security” tab and click on the “Edit Item Security” button;
4. Then select “New Role Assignment”;
   
   
5. Specify the username and roles that you want to assign to him.
   
   
6. Setup completed.

Conclusion

As you can see, both solutions provide the same capabilities for the reporting service. Regular SQL Server can also be used to connect to the database using Windows authentication mechanisms. SQL Azure and SQL Reporting services do not support this feature, but this is a known limitation of the Windows Azure cloud platform.

According to price comparisons, SQL Reporting is preferable to use in a solution where reports are generated infrequently and mostly available as static content. If your application or users can generate many reports over time, then SQL Server Reporting Services hosted on a separate virtual machine is the preferred option. As a bonus, you also get all the features of SQL Server.

However, the specific decision which of the options for constructing the report service to use should be primarily analyzed in terms of costs and resources in each case.