Security and auditing are the primary concerns for a DBA, to secure business data that is stored in a cube, and the provision of end-to-end treatment of security configuration is beyond the scope of this book. However, the key points are referred to in this recipe to secure the cube data.
Perform the following steps:
Ensure that the SSAS instance is installed behind the firewall and the default port that the AS instance communicates with is 2383.
To change the default port, scroll down to the Port row under the Analysis Server? Properties page and modify the Current Value column to a desired numbered value.
The SSAS instance can be set up to use HTTP to communicate with clients. In such cases, ensure that you open port 80 or port 443.
Note
As a best practice ensure you open the assigned port number in the firewall for TCP/IP traffic. If the value is 0, the AS instance will use the 2382 port.
In order to connect to a named instance of SSAS, we can use the format of
[Server name{:[Port], in my case I have used [WIN2K8R2-DBIA \SQL2K12DE:1453].Bear in mind that you will need to open the port assigned here in your firewall for TCP/IP traffic. If you leave the default value at 0, Analysis Services will use port 2382.
Note
To encrypt the cube transactions on the network, we can use the Internet Protocol Security (IPSec) method to achieve the encryption. Refer to this link for a step-by-step guide (http://technet.microsoft.com/en-us/library/bb742429.aspx).
Further, to ensure that all administrative data is encrypted:
Under the Analysis Server Properties page, scroll down to Security \AdministrativeDataProtection\RequiredProtectionLevel and ensure the value is set to 1. Do not allow either 0 or 2, which is a lower level of security having no encryption for data transmission.
Under the Analysis Server Properties page, scroll down to Security \DataProtection RequiredProtectionLevel and ensure the value is set to 1. Do not allow either 0 or 2, which is a lower level of security having no encryption for data transmission.
Under the Analysis Server Properties page, scroll down to Network \ Request \EnableCompression and ensure the value is set to True. The EnableCompression value is shown under the Network \ Request and Network Responses row.
As we have followed the necessary steps to secure the BI platform, this section will explain the key points about why it is essential.
The best option to secure the cube data is to encrypt the filesystem used to store cubes. The SSAS service is executed using the msmdsrv.exe executable file that runs in the operating system. Every instance of SSAS is independent of each other on the same Windows server, having its own configuration settings stored under the msmdsrv.ini file. Any configuration changes for each instance, such as permissions, ports, file storage, server mode properties, and startup accounts, are different and can be configured separately.
The storage and memory architecture has been enhanced and redefined in the 2012 version.
Microsoft documentation states that:
A server that runs in multidimensional mode uses the resource management layer that was built for multidimensional cube databases and data mining models. In contrast, Tabular server mode uses the xVelocity in-memory analytics engine (VertiPaq) and data compression to aggregate data as it is requested.
Note
Based on the configuration settings, each instance of Analysis Services runs either tabular or multidimensional databases, but not both. And the server mode property is best to determine which type of database runs on the instance.
The server mode can be set up during the installation, and in order to run multiple modes on the same server we can install SSAS as multiple instances. As a general rule, most administrative tasks you must perform do not vary by mode. As an Analysis Services system administrator, you can use the same procedures and scripts to manage any Analysis Services instance on your network regardless of how it was installed.
If you have configured Analysis Services to communicate over HTTP, the communication can be secured using the SSL protocol. However, be aware that you may have to acquire a certificate to use SSL encryption over public networks. Also, note that SSL encryption normally uses port 443, and not port 80, to communicate. This difference may require changes in the firewall configuration.
Using the HTTP protocol also allows you to run secured lines to parties outside the corporate network—for example, in an extranet setup.
As we have seen, when it comes to managing SSAS instance configuration using the GUI options, the instance configuration can be managed using the .ini file from the specified instance. Let us work out the steps as follows:
On the server, where the SSAS Service instance is installed, open Windows Explorer to verify the location of Analysis Services program files or search for the
msmdsrv.inifile.Open the file using
notepad.exeor any other text editor.Ensure that you restart SQL Server Analysis Services (SSAS) once the file is modified and saved to bring about the new changes.