Alright, blog post #1 on security…. wahooooo! – okay that is probably not your first thought. Security sure isn’t sexy, it doesn’t intrigue a whole lot of people, it probably won’t get people knocking on my virtual door for work – but it is incredibly important. I just finished a free SSRS report offering and remember the pains it took to learn the security model behind SSRS. I scoured the web and could not find one that laid out the structure where it would be useful to clients. Sure, many define the components – but with so much lingo to learn and so many paths to take, it could be done in a more user friendly way. I set out to help you understand the components AND the context of how to best use them. I would not be comfortable releasing my report offerings without a guide that simplifies this enough to make it functional. This is that guide which I hope will help you out.
There is always a tradeoff between security and convenience, that always holds to be true, but how much of a tradeoff that is varies widely depending on what tools you are using. Many consultants cannot stand SSRS because they think the security model it is a pain, or it is too granular, or too complex. I understand that impression because i have had similar pains trying to digest and understand it fully with the available content. However, now that I completely understand it, I couldn’t disagree more – SSRS is incredibly simple, powerful, and can be as secure as you need. Security can be setup very robust or very flexible; it can be managed by your finance manager or your IT personnel. The flexibility of the tool is also what makes it difficult to learn. There are multiple levels, multiple options, and a ton of unique vocabulary to learn.
In this post I will deconstruct all of those layers, provide you some guidelines on security paths to take (with pro’s and con’s) of each, and distill all that confusing lingo around SSRS into a form you can easily understand. I want this to be one of the most heavily referenced pages on my site. This will mean people are adopting the tool more heavily and getting the amazing benefits it has to offer. I plan to create a lot of content on how to use SSRS, how to optimize it, and also some product offerings using it – the more benefit and simplicity you see in it the better.
Jump around as you need – since this is a pretty large post, and you probably need one thing here are some links to different areas.
- Visual Breakdown of SSRS Security Layers
- Common Security Paths
- Details at each security level
Layers on Layers of Security – The Visual
Your Security Approach & Pathway
This is the main thing I want to convey in this post to help people get started with their security in SQL Reporting Services. Here I will highlight a couple approaches to securing everything and where you focus needs to be for each approach. You are not limited to one or the other, and there are variations of each. If you can understand the basics in these approaches, you will be ahead of most. You will have enough knowledge to fully secure your SSRS environment and feel safe knowing who has access to what data.
In subsequent sections, I get into much more detail on each layer of security and the details in each. I hope reading those after already having seen the big picture here will help nail it down for you.
Option #1 – Report & Folder Access Security Path
In this security path, the access to all of the folders and reports are tightly controlled where the actual connection to the data is the same for the actual reports. By locking down who can get what report (Layer B), the security for how the data is retrieved (Layer C) can be the same for every report. This makes maintenance and visibility into who can access the report much easier.
This setup will allow anyone setup as an admin in Site Settings (Layer A) to add or remove users or groups to reports without having to make further adjustments in SQL.
Security Layer Breakdown:
- Layer A – Site Access: Always the same – either users have access or they don’t
- Layer B – Folder & Report Access: Tightly Controlled access to every folder and report. Start with minimal access in the home folder, by default everything beneath it (folders & reports) will inherent from the top level.
- Layer C – Data Source Access: Use a powerful SQL Account to connect using the “Credentials Stored Securely in the Report Server.” This account will be able to access all of the required data, but who can access those reports is limited by the Report & Folder Access (Layer B)
Option #1 Visual Breakdown
This is the simplest option to maintain while still giving you the option to secure every single report down to the individual user level. Making changes is very simple and just requires a change to the report or folder security option, then adding or removing users/groups. This is a completely non-technical way of being able to fully manage the security. Any user with Security Admin access in Site Settings (Layer A) can fully manage this security.
Limitations & Considerations:
This is an option to be used for all or nothing access to run reports. All the users provided access to the report will be able to see the same things in the report. If multiple users need to see different levels of data in a report or need to get a different experience (visibility into sensitive data, graphs, charts, subreports, etc…) from the reports then this security option will not be the best option. If these differences only apply to a small number of reports in your system, see Option #3 for a good solution.
- Simple Non-Technical Maintenance by Report User
- Easy Visibility into User Access
- No Security Error Messages when Running Reports
- Same Data Access for All Users
- High importance on Folder & Report Security (Layer B)
Option #2 – Tightly Controlled Data Source Access
In this path, the actual access to the data the report uses is the most important level of security, allowing for a more flexible option on who can get to what report. If you already know a user can only see certain data no matter what, the chance of a security blip (i.e. a report added in the wrong folder or a mistake with inherited folder security) is much less.
This pathway for security is a harder to maintain and parts of it must be done by a database admin, but there are some pretty good trade-offs. First of course, it is more secure, Option #1 gets the job done but relies heavily on one single component and end users could inadvertently get access to sensitive data if not managed properly. Also, a huge benefit is the data security you setup can be used in other awesome applications like PowerBI, Jet Reports, or even directly connecting Excel to your SQL Data – These open a whole new world of reporting options that could be a game changer for you.
Security Layer Breakdown:
- Layer A – Site Access: Always the same – either users have access or they don’t
- Layer B – Folder & Report Access: Flexible to be either tightly or loosely control folder and report access. In this pathway, the tightest layer of control is on what data users can actually get to. If that is done properly, users might attempt to run a report, but if they do not have access to the data behind it they will either get a security related error message or no data will be returned in the report.
- Layer C – Data Source Access: Use Integrated Security for users to access data which means SSRS will check what user is running the report, then only return data based on what they are allowed to see. In SQL each user is assigned read only access to certain tables, views, and stored procedures. These are usually defined in security roles for easier maintenance accross multiple users.
Option #2 Visual Breakdown
More security is going to be harder to maintain, but with the right up front work it really is not too bad. Each data source configuration will stay the same and most of the maintenance will be done in SQL Server Management Studio. Every user account (or group of users) will be given access to all those things in SQL that make the report possible (tables, views, and stored procedures).
For those using Dynamics GP, their are pre-defined “RPT roles” exist for every GP Security User Role. These roles contain groupings of GP Tables, Views, and Procedures needed for SSRS Reports. As a great starting point, you can just match what default GP roles people have to their necessary data security roles. See this post by Mariano Gomez for an awesome explanation and SQL script for doing this.
Limitations & Considerations:
There are not many limitations in this method, if you want to secure your reports and data very tightly – this is the way. You can even control which lines of data in a table or view are available. The maintenance of this method is considerably more than option #1 and does need some technical assistance.
If a user does not have access to any object that the report tries to use, you will get a similar error message to the one below – even if you intentionally did not want them to get to that data.
- Grant individual user access to only certain data
- Option to loosely control Folder & Report security (Layer B)
- Minimize Risk of users getting to sensitive data
- Share Data Security with other tools that can access SQL (Excel or PowerBI)
- Must be managed by IT team through SQL Security
- Must grant windows users access to SQL (typically read only roles)
- More difficult to tell who has access to what
- Higher Maintenance level when adding/removing users or reports.
Option #3 – Combination of 1 & 2
Option #3 is to use any combination of the above. You can secure most things with option #1, but then when it comes to your payroll reports for example you can point it to a Data Source (Level C) that uses integrated security. Every single report can have its own individual security. These are only a couple options but they make up a very large percentage of what people actually use.
There are endless combinations security you can setup in SSRS. There are also many more complex options I don’t plan on diving into right now. If you have a complex requirement you are trying to resolve, feel free to email me for an in depth discussion.
Now that you have a good idea of the high level, we can get into some of the specific layers and what your options are in each. One thing before we move on to that though, the language of SSRS!
I am a total geek when it comes to learning, well to be honest i’m a totally geek on quite a few things – nutrition, fitness, technology, and lucky for you… business intelligence solutions. I’d say the central thing of my career and something that drives me is learning new things. Once concept I picked up a long time ago, and one that I use when learning or teaching anything is The Trivium Method. It is my go to thought process for anything I don’t understand. There are three steps that build on each other in the trivium method, the first is understanding grammar. By grammar, it is referring to the common language and how items interact. If I refer to all these security concepts and your options, but you don’t know what the these words mean in relation to SSRS, you will probably get lost and this post won’t be much help to you. To help us get going on the same page, here are a list of frequently used terms in SSRS and their meaning/location.
Report Manager is website you log into to get to your SSRS reports most of the time. It is usually just available on your internal networks. The URL to access it is usually something like http://<<ReportServerName>>/Reports. If you have trouble when getting to the website, an admin probably needs to give you access under Site Settings (Layer A).
Report Object (Folders & Reports)
Report Objects are all the items you see in the grid when you open Report Manager. It can be a folder or a report. Users will only see the report objects they have access to.
Report Object Properties
Report Object Properties are the options you get when you hover over the object and look for the drop down on the right. If it is a folder, you can rename, move, or adjust who can access the contents (and how much they can do in there). If it is a report, you get those options plus the manage option includes things like data sources, parameters, subscriptions, and scheduling.
Data Source always refers to how the report is going to go and retrieve your data. There are 2 main places where you will see this. First is in the reports themselves. Every report will need to designate one or multiple data sources to use. These are key for security.
You will also see data sources as Report Objects. This is where the actual connection settings and security setup takes place. They are typically set and shared across many reports.
In regards to the basic security setup, we have listed the core components I want to help understand. If you are ever wondering about something or it just doesn’t make sense, please give me that feedback! More definitions will follow as we get deeper into the world of SSRS Reporting.
SSRS Security Layers
On to one of the main places beginning administrators of SSRS can get lost, the many different layers of security within the tool. I will highlight each one and where they are at here. I Identified each layer as A, B, C, and D so when discussing ore needing to dig a little deeper – you don’t have to break down terms like Report Manager Settings, Folder Security, Report Security, Data Security – as you can see they overlap quite a bit which is confusing to start out.
Layer A – Report Manager Access
Layer A is table stakes for anyone looking to do anything in SSRS. If they do not have access under Site Settings in Report Manager, they do not have access to anything. Think of this as your access to have even the option of seeing anything in your browser when navigating to Report Manager.
- System Administrator – These users are the ones who maintain security and other system functions. Usually only a few people would have this option.
- System User – This is the most common selection. This assignment is needed for anyone running or managing individual reports. These users are assigned more detailed security options in Level B below.
Layer B – Report Object Security
Layer B is where you can control access to all of the folders and reports that are deployed in Report Manager.
Importance of Hierarchy:
The default setting is that any access you give in the home folder (highest level) is going to roll down to any objects (folders or reports) below it. For any of those objects you can break the inherited security and have anything below that object use the adjusted security. Once you have broken the inherited security link, changes to the parent level no longer impact the object you change or anything below it.
If you get this message below, it is warning you that whatever level of the folder structure you are at, it will break off from the parent security. This is just a reminder so you can double check that you want to set the security differently for this report and everything below it.
You can always immediately see if this folder (or report) is inheriting from the parent or not. If it says “Revert to Parent Security” that means it was previously unlinked from the inherited model and does not receive parent security updates anymore. This box will not show up if it is already inheriting security from the parent folder.
To add a new user to the folder or report you selected, click on the “New Role Assignment” option on the top. This will give you the below options to choose from. Remember you have to put the full domain and username like this (domain\username).
Below is a table of what roles contain what tasks. These are all just pre-defined options from Microsoft and can be customized for individual requirements. Most people just go with checking “Browser” for read-only users, and marking everything for Admin users. Remember this is just for the object you are changing, and anything below it if it is a folder.
Layer C – Data Source Security
Layer C is where you control how the data is retrieved by the report. One thing that makes SSRS so powerful is that it can pull data from all over the place and combine it into a single report (similar to PowerBI – part of why i love them so much). This is the place where you point to those different locations and determine what credentials will be required to retrieve it.
There are two main setups I recommend and see people take with securing data sources. People will either use a powerful SQL account to connect to the source, which is shared by anyone running the report, or the data source will use integrated security which means it will check who is asking for the data (your domain account), then provide the data back only if you are allowed to see it.
There are a few other methods that could be used, but these two will cover the majority of use cases. If you have more detailed requirements, it would serve you better to discuss the exact scenario then for me to try to generalize.
I hope this post has given you a better understanding of how security is setup in SQL Reporting Services (SSRS). Please reach out if you have any feedback or want to get into specifics that I may have glossed over here.
I will make an effort to unleash a little more fun and personality into future articles. This one is gigantic and packed with content as it is so didn’t want to get too crazy!
Have Questions about GP or other Microsoft Products – Check out the great forum’s and posts over at Dynamics Communities