Access SQL server analysis 2019 via Excel

chamila kariyawasam 1 Reputation point
2021-10-28T10:22:32.317+00:00

Hi,

I am trying to access Access SQL server analysis 2019 via Excel > Data > From Other Sources > From Analysis Services.
but once enter the SQL server IP address and click on next the Excel gets frozen and nothing happens.
SQL version is 2019 and installed on a Windows Server 2019.

If I add the particular user to Windows 2019 server's, local admin group, it works without any issue, but due to security reasons, we do not want to give admin rights to normal users.
How to achieve this with minimum rights?

Regards,
Chamila.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,245 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,736 Reputation points
    2021-10-29T05:51:46.423+00:00

    Hi @chamila kariyawasam ,

    In order for a specific role to have permissions over a cube data you have to specifically grant cube permissions to that role. That can be done using the Cube tab in the role properties page: in SSAS

    -> Databases -> Your db -> Roles -> create new role ->read definition->add the users to membership

    144931-untitled1.png

    You may take a reference of below links.

    grant-read-definition-permissions-on-object-metadata-analysis-services

    security-roles-analysis-services-multidimensional-data

    configuring-permissions-for-sql-server-analysis-services

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments
  2. chamila kariyawasam 1 Reputation point
    2021-10-29T08:39:33.937+00:00

    Hi Zoe,

    SQL permissions have been granted as you mentioned.
    If I add the particular user to Windows 2019 server's, local admin group, it works without any issue if not excel get frozen and after about 20-30 minutes it opens.

    Regards,
    Chamila.

    0 comments
  3. CarrinWu-MSFT 6,851 Reputation points
    2021-11-01T05:59:17.727+00:00

    Hi @chamila kariyawasam ,

    Thank you for your replying.

    SQL permissions have been granted as you mentioned.

    This permissions should be granted in SSAS as following:

    1.created an test user in Computer Management and then added to Groups:
    145403-1.png
    145335-2.png

    2.added a New Role into your SSAS and granted permissions to the user:
    145383-3.png
    145384-4.png
    145385-5.png
    145386-6.png

    3.connect to SSAS from excel successfully.

    it works without any issue if not excel get frozen and after about 20-30 minutes it opens.

    If you could connect to the cube, it means there is no error for your connection. But the connection takes a long time, I suspect that your cube maybe have some script need to calculate so that you will take a long time to connect if you connect to SSAS from user side. And you could try to use SQL Server Profiler to get some details. For more information, please refer to this thread.

    Best regards,
    Carrin

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments