One of the most powerful abilities that SharePoint possesses is its ability to take data from multiple different data sources and display it in a single centralized area. This way, users do not have to log into separate systems or have permissions configured to those systems in order to access data that applies to them. So, instead of having to log into a payroll system to get paystubs, a vacation system to find out vacation status, an entitlement system to find out the status of entitlement claims, profile info to ensure your contact information is correct, you can view all of this data in one place, with convenient links to the request forms on that same dashboard page.
This has the added advantage of not having to manage user access to all of those seperate systems. No need to manage accounts and access to multiple different applications as they are all exposed through SharePoint.
However, there is a key risk here that prevents the development of such systems in some organizations. The data exposed by these systems is of a sensative nature, we have to ensure there is no way for any users to be able to access another users private information. This post walks through the steps necessary to expose that information in a secure mannner.
It does not dig deeply into any of the technical components of the system, it is an overview of the considerations and processes used.
|Employee Dashboard Vacation Page|
Step 1: Restrict the information exposed
Rather than directly exposing data tables through to SharePoint for access by employees, we instead expose just the data that employees will access. One way to accomplish this is through the usage of views and stored procedures built on the database of the hosting systems. These data objects can then be accessed by the secure store service within SharePoint to pull the data through to SharePoint.
If we are accessing the data from multiple systems however there is a better way. Create a sql server hosted database independent of the source systems, and design a small database with only the tables and data repositories necessary to host the data we are choosing to expose. This gives us the advantage of having an independent database accessed by SharePoint, rather than SharePoint directly accessing any systems core database. So no load on the host systems database, no access by SharePoint to any source systems database. The secure store service is instead given access to this stand alone independent database.
This independent database can be populated by a routine data transform built using Visual Studio BIDS. Depending on how current the data needs to be, it can be a nightly transform, or a more frequent data transform for those areas that require more current data.
This of course restricts our data retrieval actions to read only, we won't be leveraging the abilities of SharePoint to also directly update data within an external applications database. In real world terms, we don't usually want to be able to modify the data in a database associated with a specific application such as payroll anyway, the payroll application itself is used to update and modify data. This maintains the data integrity managed by the payroll system, but still allows us to expose key data to the end user through SharePoint.
Step 2: Map user data to windows login
One key element of the independent database is the ability to map the current users Windows Account login to that users data. SharePoint will pass through the current users domain and username, we need to be able to use this information to extract that employees records. Designing a sql server table in the independent database that maps the login to a universal identifier within your systems, such as employee number, may be the best approach. Of course, if your external systems already map the users login to their data, then you just need to extract that data as part of your transform.
The end result of this step is an independent sql server database that holds all of the information from disparate systems that we want to expose to the end user, as well as some way to map the users windows login to the appropriate data for that user.
This mapping is a critical part of ensuring only the data that should be viewed by the end user is viewed.
Step 3: Create stored procedures return filtered user data
We only want to expose the currently logged in users data through to SharePoint, regardless of that users SharePoint based permissions. This is done by having all data filtered by username. We will be binding the retreived data to a list in SharePoint that is viewable by the end user. So, when creating the stored procedures, we need to keep a number of things in mind:
- Each must accept the username as a parameter, and return only data that relates to that username.
- Each set of data retrieved needs both a read list and corresponding read item stored procedure. The read list stored procedure is filtered by username, and must not only return rows that match the current users username, but also a primary key or identity value. The read item stored procedure takes this primary key and must only return a single value from the data source. You can use two fields combined as a primary key if necessary.
- Name your stored procedures clearly so that when you expose them through designer, it is clear which are related read list and read item stored procedures, and what data they retrieve.
- If a large number of rows could potentially be returned for a single user, you may wish to restrict the data returned to the 100 most recent records, or another appropriate restriction (last year only). Remember, we are trying to expose only truly useful information to the end user, so, if they are attempting to access data from 8 years ago, the SharePoint dashboard may not be the best choice for this. We want current useful data.
- If you split dates into month and year, such as pay dates, entitlement dates, and return those values, you can use those values to group the results in a SharePoint list. This is preferable to filtering by date range which is not only tricky to set up but also not the most efficient way of retrieving data from the user end. See also this post, and this one.
Step 4: Add Auditing to your Stored Procedures
Since the data stored in this system is sensitive, you may want to audit all access to this data. Create a database table for each system that your database exposes, and add auditing sql to the beginning of each stored procedure that records:
- The stored procedure being called.
- Any parameters passed including username.
- The data being returned.
Step 5: Create database access account and configure secure store services
Once we have the independent database set up with all tables for our data, we will need to create an account that can access that database and retrieve the data. We will be using stored procedures to retrieve the data, so the account will need execute permissions on these stored procedures as well as the ability to connect to the database.
Once this account has been created and tested, set up a meeting with your SharePoint farm administrator to configure the secure store service. The farm administrator can pull up the configuration screen that creates a new target application in the secure store service. You will need to configure the target application to use a sql user name and password for access (mask the password), and add the specific group of users that will use this credential for accessing the data through SharePoint. If you are configuring this system for all of your employees, the "all users" group is used. Basically, this is the group that will use this credential to access the independent database. This is completely transparent to the end users, is encrypted for security, it is used instead by SharePoint for data retrieval.
Also add yourself, or the person who will be configuring external content types through designer as target application administrators. This is necessary to allow SharePoint designer to access the secure store service through designer when creating external content types.
Once you have created the target application, you encrypt the connection information using the key management components, and then set the credentials (username and password) of the account you set up previously to access the independent database.
Since security is key, the farm administrator for your SharePoint farm does not need to know the password to the external independent database. The dba or other technical expert that set up the external database can instead enter the masked password in the "set credentials" form in SharePoint Central Administration and save it (this is why a physical meeting is necessary, otherwise, other settings could be entirely handled by the farm admin with the dba giving connection information).
Just because we are exposing this data through SharePoint does not mean that we need to increase the number of people who can directly access the data as a whole, this ensures that even a farm admin cannot access the connected database directly.
Make note of the name of the secure store service target application as we will be using it within SharePoint Designer.
Step 6: Create external content types in SharePoint Designer
If you have configured your target application correctly, and you have set yourself up as a target application administrator, then you should be able to open SharePoint designer and add a connection to that target application using the external content type interface. Once you have added this connection, you can use the data source explorer to open that connection and see the tables, views and routines you have exposed in the independent databases. Since we used stored procedures, you will see them listed in the "routines" section.
If you have named your stored procedures well you should be able to set up your read list and read item operations for each content type you create. Remember to set up each read list operation using the user context filter (this is what passes in the current users account name) and identify the identity value within the Designer interface. The read item operation uses this identity value to retrieve individual records.
Step 7: Connect to the external content types in SharePoint Client
You will want to create a page or pages to host the dashboard elements of the various systems exposed through SharePoint. Add the external lists (Business Data Lists) to your pages, and configure the web part components to hide menus and other options. Use content editor web parts and other components to add instructions and data context information to your dashboards.
Once you have exposed this data to the end users, they will inevitably detect potential errors or issues with the data (wrong vacation dates, incorrect entitlement amounts). This can be supported by linking to request forms associated with those systems for updates, and/or by providing phone numbers or other contact information to allow confirmation of information. You can even create standard internal SharePoint lists with the key data needed to make an update, expose the add item form, and use a workflow to send this information to the appropriate group.
You can also use standard SharePoint document libraries to include employee manuals, system instructions, vacation and sick leave forms and other components all in the same site. Think of what a typical employee may need to do through the course of a year, and centralize all of the information, instructional material, and request forms in one place.
Step 8: Optional, Expose SSRS reports through to SharePoint for charts and other visualization elements
If you have an SSRS report server it is possible to deploy SSRS reports through to SharePoint directly through embedded report libraries. Any reports created against the independent database can be relatively easily exposed through SharePoint if the Reporting site features are enabled. Set up a report library and a data connection library on your dashboard site, ensure the reports also filter by username (report server side), and embed the reports using the report viewer web part in your dashboard pages.
The end result of this effort both provides a centralized area for end users to find the majority of their employment data in one place, no need for multiple logins and searching disparate systems, and, if you expose sql server reports, you can also create visual dashboard elements that simply describe the data in a quick to understand and intuitive manner.
And, by using these five security considerations:
- Expose only the data that is needed.
- Use an independent database separate from the source databases.
- Filter ALL results by username on the DATABASE side (including reports if you use them).
- Audit every call to the database.
- Do not provide the SharePoint farm admin the password to the external database used in the secure store service.
We can ensure the data exposed to the end user is only ever the data associated with currently logged in user account, even users with elevated permissions up to farm admin level cannot retrieve data other than their own using the client.
End Result? We have achieved a rich, secure centralized area for key employee data along with supporting information and links to request and other forms, and have reduced the amount of independent system management and training on those systems needed. A real increase
in efficiency from both the user and the support end.