Securing Your Data in Analysis Services - Professional SQL Server Analysis Services 2005 with MDX - page 967

Securing Your Source Data
You need to ensure your source data is not compromised through Analysis
Services. Analysis Services 2005 provides you with several authentication
mechanisms to ensure your source data is retrieved securely by Analysis Ser-
vices. 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 op-
tions (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 authentica-
tion 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 star-
tup account for each data source used within databases of an Analysis Ser-
vices 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
then you were able to specify those in connection strings to the data source.
Analysis Services 2005 overcomes this deficiency by providing additional con-
trol and flexibility over Analysis Services instances connecting to relational
data sources as seen in Chapters 2 and 4 .
As with Analysis Services 2000, when you establish a connection to the data
source you can specify an authentication mechanism provided by the data
source. For example, if you choose Microsoft's SQL Server you have the
choice of either choosing Windows authentication or SQL Server authentica-
tion as shown in Figure 19-1 . Instead of connecting to the data source as the
service startup account as in Analysis Services 2000, Analysis Services 2005
provides four options to connect to data sources as shown in Figure 19-2 .
Once a data source has been created, you can then specify the credentials un-
der which you want the Analysis Services instance to retrieve data. The Imper-
sonation Information tab in the Data Source Designer page shown in Figure
19-2 provides you the flexibility to specify the impersonation option suited for
your database. Whenever Analysis Services 2005 instance connects to the
data source, Analysis Services uses the impersonation information specified in
the data source.