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 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 then you were able to specify those in connection strings to the data source. Analysis Services 2005 overcomes this deficiency by providing additional control 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 authentication 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 under which you want the Analysis Services instance to retrieve data. The Impersonation 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.
The default selection in the Impersonation Information page is "Use the service account" as in Analysis Services 2000. If this option is chosen, then Analysis Services 2005 impersonates as the Windows account used as the services startup account for Analysis Services instance to connect to the specified data source. When the option "Use specific username and password" is chosen then you need to specify a valid Windows credential account username and password. The Windows username is specified as <domainname>/<username>. This option overcomes the limitation of Analysis Services 2000. You can now have different Windows accounts having access to various data sources within a single database or across Analysis Services databases. If a specific account has access in the data source, then that account can just be specified in the Impersonation tab and you do not have to provide data source access to the service startup account of Analysis Services. It is recommended that the service startup account of Analysis Services be a low privileged account such as network service in order to reduce the attack surface on your system. In such circumstances the network service will typically not have access to your data sources. You can certainly provide data source access to Analysis Services by providing access to the network service account under which Analysis Services is running and choose the service account option for Impersonation Information. However, we recommend you use the "Use specific username and password" with Analysis Services running under a low privilege account to have a more secure environment. However you do need to be aware that whenever the password of the Windows account expires you would need to update the passwords in data sources, which can be done through a custom AMO program if needed. The third option in the Impersonation Information page is "Use the credentials of the current user." This specific option is selected primarily for issuing open rowset queries which are used during data mining querying and for processing objects that have out of line bindings (the object to be processed retrieves data through a query or a table dynamically at the time of processing through the process command). The last impersonation option is "Default." When the Default impersonation is selected, then the impersonation information is obtained from the impersonation information of the database object which also has the same four options. If the impersonation information is Default even for the database object, then the service startup account is used for impersonation while retrieving data for processing Analysis Services objects, server synchronization, and ROLAP queries, and uses Impersonate Current User option for data mining open rowset queries and out of line binding data sources.
You have learned the various impersonation modes that can be set on data source object in Analysis Services 2005 databases along with the recommended option to ensure source data exposed through Analysis Services is secure. You next learn to secure your dimension and cube data appropriately for your end users.