Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

22.1. Securing Your Source Data

You need to ensure your source data is not compromised through Analysis Services. Analysis Services 2008 provides you with several authentication mechanisms to ensure your source data is retrieved securely by Analysis Services. To retrieve data either at processing time or at query time, an Analysis Services instance needs to connect to data sources based on the storage options (MOLAP or ROLAP) specified for the dimensions and cubes within the database. In order to connect to the relational data source and retrieve the data, the Analysis Services instance needs appropriate credentials.

Analysis Services 2000 supported integrated security as the main authentication mechanism to the data source. The drawback with integrated security is that the Analysis Services 2000 instance used the credentials of the service startup account to connect to the data source. One of the main limitations of Analysis Services 2000 is that you need to provide access to the service startup account for each data source used within databases of an Analysis Services instance, and that can be a little tiresome. If the data source provided username and password options as with Microsoft's SQL Server or Oracle, you were able to specify those in connection strings to the data source. Analysis Services 2005 and Analysis Services 2008 overcome this deficiency by providing additional control and flexibility using Data Source Impersonation while connecting to the relational data sources, ...