Authentication, Role-based Security, and SQL Reporting Services Web Services

In Introduction To Role-Based Security In SQL Server Reporting Services we introduced role-based security in SQL Server Reporting Services. In this article, we will discuss what you need to know about security to invoke the web service API.

There are two issues to address: authentication and authorization. First, you need to pass the proper credentials to the report server to avoid authentication errors (HTTP 401 access denied messages). Secondly, you need to have role-based security properly configured to avoid authorization exceptions from the API like the following:

The permissions granted to user 'REPORTING\scott' are insufficient for performing this operation.

Credentials

ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
Extension[] extensions = rs.ListExtensions(ExtensionTypeEnum.Delivery);

The DefaultCredentials property represents the credentials for the current security context of the process. For a client-side application, like a Windows Form application or a console mode program, the credentials will be the credentials of the user executing the program. If you are receiving the “permissions are insufficient” exception with this code in a client application, the user is authenticated but not in a reporting services role with authorization to complete the task. See the later section on role-based security.

If you are seeing an access denied error message from a client application then the report server cannot authenticate the client. Perhaps the report server is not in the same domain as the user’s machine. In this scenario there are at least two options available.

First, you could create a ‘shadow account’ on the reporting server by duplicating the user’s domain login and password on the report server. Creating a shadow account can be hard to maintain, particularly if a password change policy is in effect for the domain, because the passwords must remain synchronized. Alternatively, you can pass credentials for an account that exists on the report server using the NetworkCredential class:

ReportingService rs = new ReportingService();
rs.Credentials = new NetworkCredential(username, password, domain);         
Extension[] extensions = rs.ListExtensions(ExtensionTypeEnum.Delivery);

Obviously, you need to take extreme care in where and how you store the username and password on a client machine. Hard coding the values into the program as we do above is inflexible and vulnerable to a disassembler. Storing the values in a configuration file in plaintext is even more vulnerable. My suggestion is to use Microsoft's data protection API (DPAPI) to keep the values encrypted.

Credentials and ASP.NET

Local Server

If the web application is on the same server as the Reporting Services web service, the call will authenticate using DefaultCredentials, but you are probably seeing the “permissions are insufficient” exception. One solution to this problem is adding the ASPNET or NETWORK SERVICE account into a role in Reporting Services, but take care before making this decision. If you were to place the ASPNET account into the System Administrators role, for example, anyone with access to your web application is now a Reporting Services administrator.

Alternatively, you can use impersonation in ASP.NET. You can enable impersonation for the application, for a subdirectory of pages, or for individual pages, using the identity element in web.config (see more resources for additional information). When using impersonation, it is important to deny access to anonymous users, as shown below:

	
<system.web>
    <authentication mode="Windows"/> 
    <identity impersonate="true"/>
    <authorization>
        <deny users="?"/>
        <allow users="*"/> 
    </authorization>
 </system.web>

WIth impersonation the ASP.NET page will execute with the security context of the client, and web service calls to the same machinewill also be made with the security context of the client (using DefaultCredentials). You will still need to configure role-based security for each user to give each user authorization to perform actions.

Remote Server

When a web application makes web service calls to a remote report server, there are additional complications. If you are using impersonation, there is a one-hop limit with NTLM authentication. The client’s credentials make one hop from the client machine to the web server, and ASP.NET can use these credentials to impersonate the client on the same machine only. For ASP.NET to use the credentials on another remote machine would require the credentials to make a second hop, which does not happen - the call will go to the remote machine with the credentials of the ASP.NET process instead. Since the ASP.NET process runs under a local machine account by default, the remote server will not authenticate the credentials and the call will fail with an access denied message.

There are a number of solutions in this scenario.

First, you can look at enabling Kerberos delegation, which is beyond the scope of this article, but you can read about delegation in the article How To Configure an ASP.NET Application for a Delegation Scenario. Using delegation would allow you to have the client’s credentials make the additional hop to reach the report server, which in turn gives you more granular control over authorizations by placing users and groups into roles instead of process accounts.

Another option is to run the ASP.NET process under an account with permissions to the report server. For example, the ASP.NET process could run under a domain account. For details, see the article: ASP.NET Process Identity. A similar strategy would be to synchronize the ASPNET / NETWORK SERVICE accounts on both the application server and the reporting server by matching thier passwords and configuring ASP.NET to use the password. Using these options you’ll need to add the ASP.NET process account into a role on the report server. Unfortunately, anyone with access to your web application will now be in this role, so your application will become responsible for more granular authorization checks.

Finally, you can pass credentials from ASP.NET to the reporting server using the NetworkCredential class shown earlier in the article. All the same caveats apply in regards to storing a username and password in code or in an XML config file.

Once you’ve avoided all of the access denied messages, it’s time to configure role-base security.

Role-based Security Settings for Web Services

In order to determine what roles to assign you’ll need to know what tasks the user will perform with the web service API. Unfortunately, the API documentation does not list what permissions are required for each method to complete successfully. By experimenting with the report manager UI, the web service API, and using educated guesswork, you can find the minimum amount of permissions needed.

As an example, the ListExtensions method shown earlier in code only requires the caller to be in the System User role – the least privileged role available. A system user has permissions to view the report server properties and view shared schedules, but not view any reports or folders.

Callers who are using the ListChildren method will need to be in at least a Browser role for the item they are trying to view. Callers using the CreateRole method need to be in the System Administrators role. All the above assumes the default setup of Reporting Services, as an admin can modify the tasks permitted for each role. For an introduction on adding users and groups into roles, see our previous article.

Conclusion

In this article, we outlined some common scenarios for your web service API calls to be authenticated and authorized in Reporting Services. The number of possible environments and configurations is quite large, but hopefully you can extrapolate information from this article to match your specific setting.

By K. Scott Allen

Additional Resources

More SQL Server Reporting Services articles