Loading Data from a Relational Database - Microsoft SQL Server 2008 Analysis Services - page 308

LISTING 17.1

DDL That Defines a Data Source Object

<DataSource>
<ID>FoodMart2008</ID>
<Name>FoodMart 2008</Name>
<ConnectionString>Provider= SQLNCLI.1; Data Source=localhost; Integrated
Security=SSPI; Initial Catalog=FoodMart2008</ConnectionString>
</DataSource>

Data Source Security

When Analysis Services establishes a connection to external data sources, it is important
to control the user account under which it establishes this connection. Establishing
connections in Analysis Services 2000 is always under Analysis Services account creden-
tials. There are pros and cons to this approach. It's easy for a database administrator to set
the security settings of a relational database—permission is required for only a single
account. However, this approach lacks flexibility. Establishing all connections under one
account means that a single account must have access to all data across all databases.

Starting from Analysis Services 2005, a database administrator can configure different
credentials under which connections to different relational databases are established. You
use the ImpersonationInfo object (which is part of the data source object) to define those
credentials. The properties of the ImpersonationInfo object define the credentials that
Analysis Services uses to establish a connection. Listing 17.2 is the DDL for an
ImpersonationInfo object in a data source.

LISTING 17.2

DDL That Defines an ImpersonationInfo Object

<ImpersonationInfo>
<ImpersonationMode>ImpersonateAccount</ImpersonationMode>
<Account>MyUser</Account>
<Password>AAAAAA</Password>
</ImpersonationInfo>

Table 17.2 describes the properties of the ImpersonationInfo object.