ANALYSIS SERVICES SERVICE SECURITY (Angela Harney)
( SSAS Quick Reference)
1. Add the necessary Analysis Services service account to
SQL Server Analysis Services in Management Studio
so that Analysis Services can Deploy objects to SQL
2. In SQL Management Studio, CONNECT to Analysis Services,
Instead of DATABASE ENGINE
3. Right-click the Analysis Services server node in SQL
Management Studio
4. Click Properties
5. Click Security, at the left
6. Click the ADD button in the lower right
7. Click the OBJECT TYPES button
8. Click GROUPS
9. Click OK
10. In the Enter Object Names to Select box, enter
NT ServiceMSSQLServerOLAPService
11. Click the CHECK NAMES button to make sure you
spelled it correctly
12. Click OK
13. This has added the OLAP Service Account to the Analysis
Service engine, but it also needs to be allowed to read
Data from the SQL databases.
14. In SQL Management Studio, CONNECT to Database
Engine
15. Expand SECURITY
16. Right-click LOGINS
17. Click NEW LOGIN
18. Click the SEARCH button at the top left
19. Follow Steps 7 through 12 to add the service account
as a login in SQL. Click ok. Then double-click to edit.
20. At the left, click USER MAPPING
21. Put a check mark next to each database the service
should have access to, with dbo as the ROLE below
22. In some cases you may want to click Server Roles at the
left, and check sysadmin, so that the service account
can also be ran under the content of SQL Agent jobs
========================
According to your description, you didn't create the SSAS service account when installing SQL Server. Right?
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 SIDNT SERVICEMSSQLServerOLAPService is granted membership in the local Windows group, and the local Windows group is granted the appropriate permissions in the ACL. If the account used to start the Analysis Services service is changed, SQL Server Configuration Manager must change some Windows permissions (such as the right to log on as a service), but the permissions assigned to the local Windows group will still be available without any updating, because the per-service SID has not changed.
In this scenario, the steps you have done supposed to work. Please try to restart the services to take effect. If the issue persists, please refer to the links below to reconfigure your services account:
Configure Service Accounts (Analysis Services)
Configure Windows Service Accounts and Permissions
https://msdn.microsoft.com/en-us/library/ms143504(v=sql.120).aspx#Serv_SID
-------------------------
- Make sure to add the NT ServiceMSSQLServerOLAPService account to your Analysis Services instance
SSMS database ---> Security ---> add a account
- Also add the account to the NorthwindDW, AdventureWorksDW and ProductSalesDW databases as dbowner.
- For class purposes, give the account a Server Role of Sysadmin.