I recently did some consulting for a customer who had inherited the deployment and administration of an entire SSRS environment. Whilst working through the planning component of the project, there was confusion over exactly how many Active Directory (service, user or group) accounts and database logins would be required for the entire end to end deployment and where each would need to be applied.
OK, here goes – there are essentially 5 levels of security in a SSRS environment (excluding Sharepoint). Note, this is a basic interpretation of SSRS security (i.e. suit SSRS beginners or outsiders such as project managers).
1. Service accounts
2. SSRS Instance security
3. SSRS Content security
4. Data source security
5. Row level security
A service account is recommended for running the Report Server Service Account. Optionally you can use the built-in Network Service account however ideally go for the domain account if you can.
Optionally you can also configure the unattended service account so two service accounts all up or just use the same service account for both. See here http://msdn.microsoft.com/en-us/library/ms156302.aspx.
SSRS Instance Security
This requires A.D. users or groups to be added to the SSRS instance as SSRS users (there are only two choices here, users and administrators).
SSRS Content Security
We then step down to SSRS role security (content security) which involves mapping the AD group or user account to one or more predefined SSRS content roles (e.g. Content Manager at the highest level down to Browser at the most restrictive level). Custom roles can be created if you have extra special requirements not covered off exactly by the built in content roles. SSRS content security uses inheritance which means that by default subfolders and items within a folder inherit permissions from the parent folder. If for example the security for a subfolder differs from the parent folder you essentially need to break the inheritance at which point security changes made to the parent folder will no longer cascade down to the sub folder.
Data Source Security
Data sources have security too! When you create a data source you need to specify connection details (for example SQL Server and Oracle databases) with one of the key differences being SQL Server connections allow you to specify an A.D. account (not just a database login).
The embedding of credentials inside the data source is a good option where you don’t have a requirement for per user authentication (row level security) and therefore a service account can be used to connect to the data source as opposed to the user’s own credential. Embedded credentials are however a MUST when you want to create subscriptions for reports. Stored Credentials reference here http://msdn.microsoft.com/en-us/library/ms160330.aspx.
You need to have your encryption key created on your SSRS instance for embedded credentials to be stored as the data source credentials are encrypted and stored inside the Report Server database via the encryption key.
Row Level Security
At this point you can take the elevator one additional level down from data source security. This is used where there is a requirement for multiple users to look at the same data (for example, table) but have restricted results returned depending on who they are. Kerberos (Windows) authentication is the preferred method as it covers off access to the database outside of reporting services e.g. Excel, Management Studio. Once the Kerberos setup is configured, this is where you would need to switch the authentication setting for any required data sources to Windows authentication (this differs from embedding a Windows credential inside the report server database).
Kerberos is not the project manager’s uncle from El Salvador, but rather, a multi tiered configuration exercise that is (for a large organisation) usually going to involve your systems administrators plus the Sharepoint team (if you have an integrated mode installation). Once configured however, will allow row level security to be implemented using Windows authentication. For Analysis Services data sources you just need to configure the relevant user or group against an Analysis Service role that has Deny restrictions configured. For SQL Server databases you will need to embed logic in your views and/or stored procedures to ensure the Windows credential can be evaluated. You will then need to ensure permissions are granted only to those views and stored procedures, not the underling tables.
If Kerberos is not going to fly for whatever reason, you can implement DIY security utilising the SSRS User collection (for SQL Server Data Sources) or the Custom Data Field (for Analysis Services Data Sources). This will require mapping tables to be created, populated and then managed on an ongoing basis (hence likely to require Active Directory synchronisation). This is not as comprehensive as the Kerberos method however as there are gaps.
There are a few things not covered here (such as Custom Forms authentication) however you can read all about it here http://msdn.microsoft.com/en-us/library/bb283249.aspx.
Happy SSRS Planning !