There are four options to
get access to the data sources for an SSAS project as below:
· User a specific Windows
user
· User the service account
· Use the credential of the
current user
· Inherit
By default, Inherit is
selected. But you may encounter an error if you use this option such as Login Failed for user 'NT
Service\MSQLServerOLAPService', Why? Here is the reason:
At
the data source level, Inherit means that Analysis Services should use the
impersonation option of the parent object. In a multidimensional model, the
parent object is the Analysis Services database. Choosing the Inherit option
lets you centrally manage the impersonation settings for this and other data
sources that are part of the same database. For this option to be meaningful,
choose a specific Windows user name and password at the database level.
Otherwise, the combination of Inherit on the data source and Default on the
database are equivalent to using service account option.
The
error indicates that the service account for SSAS does not have permission on
the data source. What are the solutions?
Solution 1 – as recommended, use a specific Windows user name
and password at the database level.
As an example, I use
AdventureWorksDW2012 as the database source.
Step 1 - Create a user with the password
Step 2 - In SQL Server 2012, create a login from the windows
account. Choose AdventureWorksDW2012 as the default database for the login
(optional).
Step 3 - In AdventureWorksDW2012, create a user associated with the
login and assign it with a db_datareader role. At this point, you may see an
error message complaining no user or role not associated with the login, just
ignore it.
Solution 2: to give the default SSAS service account a read permission
on the data source.
The trick for this option
is to find the 'NT
Service\MSQLServerOLAPService'.
Here is the background information:
When installing SSAS, a
per-service SID for the Analysis Services service is created. A local Windows
group is created, named in the format SQLServerMSASUser$computer_name$instance_name. The per-service SID NT SERVICE\MSSQLServerOLAPService is
granted membership in that local Windows group, and the local Windows group is
granted the appropriate permissions in the ACL.
So you will not use the 'NT
Service\MSQLServerOLAPService' directly, instead, you use its group SQLServerMSASUser$computer_name$instance_name
Step 1:
SQL Server 2012 | Security | Login (right click) | New Login | Search | Object Type | Group (select it!) | OK | Advanced | Find Now.
You should be able to see
the SQLServerMSASUser$computer_name$instance_name
group at this point
Step 2:
Select the group
Step 3:
On user mapping, assign
the Login with the db_datareader role on the data source database- AdventureWorksDW2012.
You may see an error message complaining no user or role not associated with
the login, just ignore it.
