The Trustworthy Computing Initiative from Microsoft has made each of its products more secure and robust to stand out and empower the data needs in an efficient manner. Business Intelligence means data is presented normally to the users that will take full advantage of the data tools and existing security infrastructure to secure the data. The security framework in the corporate network must be robust to secure the BI environment.
When we talk about the Microsoft Business Intelligence stack, the three giants are SQL Server, SharePoint, and Office 365. In the last few years, Microsoft development teams have made great progress in securing the data platform by making secure designs. The data platform is the key perspective in safeguarding your data. The major security features include the following:
Surface area reduction during the installation and disabled autostart of unwanted services
Native encryption on the database
User and schema separations
Endpoint-based authentication with Group Policies on Active Directory
Granular permissions in a least privileged environment
The major development since SharePoint 2007 is to prevent unauthorized users from viewing data, along with integrated AD authentication or forms authentication that provides a flexible security experience across the sites.
The new Office 365 platform provides end users with a flexible way of data presentation without having the need of other software and by simply using Microsoft Excel. The new features include analytics in analyzing data, ad hoc reporting with dashboards, and scorecards that fuel the powerful GUI frontend for business users.
SQL Server 2012 transports new changes on the Analysis Services foundational aspects that are divided into the modeling and server mode that gives an advantage of tools, tasks, and features that are available in the installed mode. The 2012 version provides a business intelligence semantic model that has three different approaches: tabular, multidimensional, and PowerPivot. These are mentioned in detail in the following list:
A tabular model uses a relational modeling construct, such as tables and relationships, for data modeling that uses the xVelocity in-memory analytics engine for better performance for calculations and data storage.
Multidimensional uses default OLAP modeling constructs used as cubes and dimensions, for data mining needs that use MOLAP, ROLAP, and HOLAP storage types.
PowerPivot is a self-service BI solution that uses an analytical data model in Excel workbooks using PowerPivot as an Excel add-in. This model also uses the xVelocity method for the in-memory analytics engine for better performance for Excel and SharePoint. PowerPivot is very effective for data modeling and data rendering that will help workbook deployment on a server for a centralized and controlled data access, which requires SharePoint and Excel Services for better presentation to the end user.
In this recipe we will go through the important aspects in building a secure BI platform on the instance level along with enhancements incorporated in SQL Server since the transition from 2008 R2 to 2012.
Note
The prerequisite for this recipe can be obtained from the Getting ready section of the Designing and creating security roles in an OLAP database recipe.
Perform the following steps:
Start SQL Server Management Studio and connect to the SQL Server 2012 Analysis Services (SSAS) instance.
Right-click on the SSAS instance. Choose Properties and click on the General option under the Select a page section.
By default the Analysis Server Properties window shows the default Name list for the chosen instance. Refer to the following screenshot:
Click on Show Advanced (All) Properties that will change the Name list with all advanced options that are available for Analysis Services. Refer to the following screenshot:
Note
The value yes within the Restart column indicates that any changes to these rows will require you to restart the specified SSAS instance.
Note
There are certain server properties that support tabular and multidimensional mode servers. Microsoft documentation suggests that, if SSAS is installed with PowerPivot for SharePoint, then you should make sure to use default values unless otherwise directed by a Microsoft product support engineer, on any occasion of a premier support case issue.
Specific to SQL Server 2008 R2, and in case of any specific troubleshooting on operational- or performance-based issues, refer to the SQL Server 2008 R2 Analysis Services Operations Guide, which can be downloaded from Microsoft Download center (or http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SSASOpsGuide2008R2.docx).
The server property configuration is divided into multiple references. They are divided into multiple sections, and the following table explains these relevant SSAS configuration properties. (Source: Microsoft SQL Server Documentation).
The following topics explain the various Analysis Services configuration properties:
|
Topic |
Description |
|---|---|
|
General Properties |
General properties are both basic and advanced properties, and include properties that define the data directory, backup directory, and other server behaviors. |
|
Data Mining Properties |
Data mining properties control which data mining algorithms are enabled and which are disabled. By default, all of the algorithms are enabled. |
|
Feature Properties |
Feature properties pertain to product features, most of them advanced, including properties that control links between server instances. |
|
Filestore Properties |
The filestore properties are for advanced use only. They include advanced memory management settings. |
|
Lock Manager Properties |
Lock manager properties define server behaviors pertaining to locking and timeouts. Most of these properties are for advanced use only. |
|
Log Properties |
Log properties controls whether, where, and how events are logged on the server. This includes error logging, exception logging, flight recorder, query logging, and traces. |
|
Memory Properties |
Memory properties control how the server uses memory. They are primarily for advanced use. |
|
Network Properties |
Network properties control server behavior pertaining to networking, including properties that control compression and binary XML. Most of these properties are for advanced use only. |
|
OLAP Properties |
OLAP properties control cube and dimension processing, lazy processing, data caching, and query behavior. These include both basic and advanced properties. |
|
Security Properties |
The security section contains both basic and advanced properties that define access permissions. This includes settings pertaining to administrators and users. |
|
Thread Pool Properties |
Thread pool properties control how many threads the server creates. These are primarily advanced properties. |